Lesson 3: Creating a Report

This lesson explains how to create reports by using the Acumatica Report Designer.

Report Creation: General Information

By using the Acumatica Report Designer, you design a report to collect particular data from the Acumatica ERP database, using any report parameters that the user has specified. The report groups, sorts, filters, and displays the data based on the settings you have specified when designing the report, so that a user running the report does not have to perform all these steps manually. The Report Designer gives you the flexibility to gear the report design process and the resulting report to your users' needs for information.

Learning Objectives

In this chapter, you will learn how to do the following in the Report Designer:

Open and view an existing report
Copy an existing report
Create a report from scratch
Update the database schema for reports
Publish and view a report

Applicable Scenarios

You may want to create reports by using the Acumatica Report Designer in the following circumstances:

You are responsible for the customization of Acumatica ERP in your company, including developing and modifying reports to give users the information they need to do their jobs.
You need to deliver diﬀerent reports that your colleagues may need to perform their job duties.

Report Development Stages

The process of developing a report in the Report Designer consists of the following stages:

Preparation: You determine which data you need for the report, and you find the underlying data access classes (DACs) and the corresponding data fields. For more information about DACs, see DAC Discovery: General Information.
Data loading: You load the database schema, select the DACs that you have determined during the previous stage, and specify the relationships between DACs. For more information about relationships between DACs, see Data from Multiple Data Sources: General Information.
Layout definition: You add sections to the report layout (or delete unneeded sections from it), add any of the visual elements available in the Tools pane of the Report Designer main window, specify the visibility of the report sections and elements, and define the style and colors of the report. This stage can follow the Data modification stage instead of preceding it. For more information about adding elements to the report layout, see Report Content: General Information. For more information about the report style, see Report Style: General Information.
Data modification (optional): You define parameters, filters, and sorting and grouping for the data in the report. Lesson 3: Creating a Report | 21
```
   For more information about sorting and grouping, see Data Sorting and Grouping: General Information. For
   details about parameters and filters, see Parameters and Filters: General Information.
```
Saving: You save the report on the server or locally. For more information about saving of a report, see Report Creation: Modification of an Existing Report.
Publishing: You preview the report and make sure that it satisfies all the requirements. If it does not, you modify the report. When it is ready, you publish the report, which entails adding it to the site map to make it listed in a workspace, and assigning the Granted access rights to the roles whose users need to work with the report.

Running and viewing: You run the report in Acumatica ERP to be sure that you have published it correctly.

      Only users with the Report Designer role are allowed to preview, save, and publish reports.

Data for a Report

You can load the database schema of all available data access classes from the application server that you use to work with Acumatica ERP. To load the database schema, you use the Schema Builder, which you invoke by clicking File > Build Schema on the Report Designer menu bar. On the Tables tab, you specify the following settings:

The connection string (see Item 1 in the screenshot below) in the following format: https:////, where you replace with the actual URL of your Acumatica ERP server, and with the name of your Acumatica ERP instance.
Your username and password in the Acumatica ERP instance (Item 2). If your application contains more than one tenant, you type the appropriate tenant name with the username in the following format: @. The tenant name is the name you select when you sign in to Acumatica ERP.
When you click the Load Schema button (Item 3), the Report Designer connects to the application server and loads the schema. When the schema is retrieved, the list of all data access classes defined in Acumatica ERP is displayed (Item 4).
Figure: Loading of the schema of the data access classes Lesson 3: Creating a Report | 22
When you have loaded the database schema, you can select the data access classes you found for the report. If your report is to be based on multiple data access classes, you should specify the relationships between them. On the Relationships tab of the Schema Builder wizard, you specify the relationships between a pair of DACs in the upper table and the links between the DACs in the lower table.

Report Publication

When a report is ready, you can publish it so that users of the system can run it. Publishing a report entails adding the URL of the report to the site map in Acumatica ERP—that is, adding information about the report to the Site Map (SM200520) form and saving your changes. Also, you need to specify access rights to the report on the Access Rights by Screen (SM201020) form.

On the Site Map form, you add a new row with the following settings:

Screen ID: The identifier of the form that is used to access the report in Acumatica ERP. You assigned this identifier when you saved your report in the Report Designer.
Title: The name of the report. This name is displayed in the Reports category of the workspace that you specify for the report.
URL: ~/Frames/ReportLauncher.aspx?ID=.rpx.
Workspaces: The name of the workspace or workspaces where you want to place the report; you can add the report to multiple workspaces by selecting the check box next to each needed workspace in the drop- down list.
Category: Reports. By default, the system sets the report's access rights to Revoked for all user roles. To make the report visible to users of any specific user role, you need to set the role’s access rights to it to Granted on the Access Rights by Screen (SM201020) form.

Report Viewing and Execution in Acumatica ERP

To preview a report that has not been published yet, you use the Preview tab in the Report Designer. If your report requires parameters, they are ignored, and the report is displayed with random data. To view a report in Acumatica ERP, you open the workspace where the report is placed, and under the Reports category, you click the link with the report name. You can also open the report form by searching for its identifier. If needed, on the Report Parameters tab, you specify the report parameters. Then you click Run Report on the report toolbar.

          To return to the report form—for example, to change report parameters—you can click the
          Parameters button ( ) on the report toolbar.

Report Creation: Modification of an Existing Report

In the Acumatica Report Designer, you can create a report by copying an existing report and modifying this copy. You can also create versions of an existing report and customize the versions.

Predefined, Custom, and Customized Reports in Acumatica ERP Acumatica ERP provides a number of predefined reports for each area of the system's functionality. By using the Acumatica Report Designer, you can expand the set of reports that meet your company's needs by creating custom (new) reports and by customizing copies of existing ones. Lesson 3: Creating a Report | 23

         We recommend that you leave predefined reports intact; if you want a report that is similar to an
         existing one, make a copy of the existing report, and customize (modify) this copy.

In the Acumatica ERP interface, you can view reports published in the system. In most workspaces, predefined reports are listed in the Reports category. You can add reports that you develop with the Report Designer to this category of any workspace.

Storing of Reports

Reports in the Acumatica Report Designer are XML files with the .rpx extension. Predefined reports are stored on the Acumatica ERP server. You can store reports that you develop by using the Report Designer either locally or on the Acumatica ERP server. We recommend that you save your reports in one of the following locations:

The server
The /Site/ReportsDefault/ folder
When you open a report in the Report Designer, the application searches for the report in these locations in the listed order.
```
      The system uses the /Site/ReportsCustomized/ folder for backward compatibility. You can
      use this folder to store modified reports whose names are the same as the names of predefined
      reports.
```
The Report Designer saves all reports in the UserReports table of the Acumatica ERP database.

Copy of a Report

To copy a report, you open this report in the Report Designer by using the sign-in credentials of a user who has the Report Designer role, and you save a copy of the report with the name diﬀerent from the name of the original report. To save a new report, you can use the following commands on the Report Designer menu bar:

File > Save to Server: You use this command if you want to save your report to the Acumatica ERP server. In the Save Report on Server dialog box, which opens, you specify the following:
The connection settings to the server—that is, the connection string and the sign-in credentials. You specify the connection string (see Item 1 in the screenshot below) in the following format: http:// , where is the URL of your Acumatica ERP server. You can use the http or https protocol, depending the settings of your website. When you enter your credentials, if your application contains more than one tenant, you type the tenant name with the username (Item 2 in the screenshot below) in the following format: @. The tenant name matches the name you use when you sign in to Acumatica ERP. If your application contains only one tenant, you specify only the username.

The name of the report (Item 3 in the screenshot below)—which is an identifier with the format, where A is a letter and N is a digit. For information on the identifier conventions of Acumatica ERP reports, see Form and Report Numbering.

                  To avoid conflicts with new reports that the Acumatica ERP engineering team could
                  produce in the future, we recommend that you use diﬀerent report identifiers than those
                  described in Form and Report Numbering.

Lesson 3: Creating a Report | 24

      Figure: The Save Report on Server dialog box

File > Save as: You use this command if you want to save your report locally.
```
               You can also save the report locally by clicking the Save Report As button on the Report
               Designer window toolbar.
```
Aer you have used any of these commands, the next time you want to save the report (to the same location), you can use the File > Save command. For example, if you have saved a report to the server by using the File > Save to Server command and then modified this report, you can use File > Save to save this report to the server again. Aer you have saved your report locally or to the server of an instance, you can use this report for any tenant of this instance.

Report Versions

You can save your report to an Acumatica ERP server as a new version by selecting the Save as New Version check box in the Save Report on Server dialog box; the version you are saving is activated automatically. When a user runs this report, the active version of the report is executed. You can manage the active version of a report only in the Acumatica ERP interface. When a report form is opened, you activate the necessary version on the Report Versions tab by selecting the Active check box in the row with this version. Alternatively, you can activate the selected version by clicking Activate on the table toolbar. If you want to modify a specific report version, you need to open the report form in Acumatica ERP and select the row with the necessary version on the Report Versions tab. Then you start the Report Designer by clicking the Edit Report button on the report form toolbar.

Report Creation: Report Sections

By default, a newly created report includes the main sections, which are described in this topic. You can add new sections, as well as delete, hide, and duplicate sections.

Sections in the Report Layout

The layout of any newly created report always consists of the following main sections:

The page header section (see Item 2 in the screenshot below)
The page footer section (Item 6)
The detail section (Item 4) Within the report, you can add group sections, which can be nested. Each group includes the group header (Item 3) and group footer (Item 5) sections, with the same detail section always nested between the group header and group footer. You can also add the report header (Item 1) and report footer (Item 7) sections. Lesson 3: Creating a Report | 25

All these sections are briefly described below:

The detail section is the central part of the report that contains its main data. (If the detail section has been duplicated, both detail sections are in the middle of the report.) For example, if your report outlines sales orders, you would put a list of orders in the detail section. Unlike other sections, it cannot be deleted.
The group header and group footer sections are shown at the beginning and end, respectively, of each group of the report data. These sections are a good place for information that applies to all data in the detail section of the report. For example, if you are grouping sales orders by customer, you can place detailed information about the customer in the group header section and use the group footer section to calculate subtotals for the customer.
The page header and page footer sections, located at the top and bottom of every page of the report, are the best place for information that the reader may need when reading every page. For example, you can use the page header to display the column headers of a table that is continued from the previous page. You can use the page footer to display the number of the current page.
The report header and report footer sections are the only types of report sections that are printed once per report. The report header is the second section of the report on the first page; when the report is printed, the report header is placed right aer the page header. The report header is the best place to display the report name, the company logo, the date range of the report, and similar information. The report footer, which finalizes the informative part of the report, is placed before the page footer on the last page of the report. It is a good place for totals or conclusions.

Figure: Sections of a report layout

You can do the following with a report section:

Add it: You can add the report header, the report footer, the page header, and the page footer sections to the report layout. To add these sections, you right-click any empty space of the report layout (on the le side or the right side), and select the appropriate command (Item 8 in the screenshot above). When you add a new group, its group header and group footer sections are added automatically. To do this, you right-click any empty space in the report layout, and then select Add New Group (Item 9 in the screenshot above). You can also add a new group in the Group Collection Editor. For more information, see Data Sorting and Grouping: General Information.
Delete it: You can delete the report header, the report footer, the page header, and the page footer sections from the report layout; you cannot delete the detail section. You may need to delete a section if it does not Lesson 3: Creating a Report | 26
```
    contain any elements or the information in this section is not relevant to this report. To delete a section, you
    right-click the section header and then select Delete, as shown in the screenshot below.
```
Duplicate it: You may need to duplicate a section if, for example, you want to display content in the section conditionally. To duplicate any section, you right-click the section header and then select Duplicate Section.
Hide it: You may need to hide a section if data from the section is used in calculations but you do not need to display the content of the section. To hide any section, you set the Visible property of the section to False. You also can hide a section if a condition is met; in this case, you specify an expression for the VisibleExpr property.
Figure: Deletion of a section

Report Creation: Update of the Database Schema for Reports

Any report of Acumatica ERP contains a database schema. You load the database schema for a report in the Schema Builder when you create the report in the Acumatica Report Designer. You may need to update the database schema to make new fields and database columns available in the report in the following cases:

You have installed a new version of Acumatica ERP.
Acumatica ERP has been customized—for example, with the addition of a custom bound field. You can update the database schema for each report manually, or you can use the ReportUpdater utility to update the database schema for multiple reports at once.

Update of the Database Schema for One Report

You use the Schema Builder of the Acumatica Report Designer to update the database schema for a report that is opened. On the Tables tab, you click one of the following buttons, which are shown in the screenshot below:

Refactor: Updates the changed field names for the tables used in the database schema for the report. When you click the button, the Report Designer displays the Field Refactoring window with a two-column table. The Existing Name column displays the field names that were in use in the previous version of Acumatica ERP. For each field name in the Existing Name column, in the New Name column, you select the field name from the list of table field names corresponding to the new version of Acumatica ERP. Lesson 3: Creating a Report | 27
Refresh: Updates the table structure you have selected (by clicking it in the list of tables) in the database schema for the report.
Refresh All: Updates the structure of all the tables used in the database schema for the report.
Figure: The buttons to update the database schema for a report

Update of the Database Schema for Multiple Reports

You can update the database schema for multiple reports at once by running the ReportUpdater utility, which is located in the same folder that holds the Report Designer. You launch this utility in the command prompt by typing the following.

>ReportUpdater src=<srcFolder> dest=<dstFolder> url=<url>
   log=<logName> login=<login> password=<password>

The parameters of the ReportUpdater utility are described in the following table.

Parameter           Description

src                 The absolute path to the folder from which the utility loads the source reports.

dest                The absolute path to the destination folder to which the utility saves the updated reports.

url                 The URL of the site used to load the database schema.

log                 The optional log file name. If you specify a name, the entire log is written to the file. Other-
                    wise, the system displays the log information in the console window.

login               The username for the site used to load the database schema.

password            The password for the site used to load the database schema.

When you launch the ReportUpdater, it performs the following actions:

On the specified site, loads the database schema

For each report from the specified source folder, does the following: Lesson 3: Creating a Report | 28

   a. Reads the report
   b. Refreshes all the tables used in the report, based on the database schema
   c. Writes the updated report file to the specified destination folder
   d. Writes the result of the report update to either the system console or the specified log file

When working with the ReportUpdater, use the following tips:

If a parameter contains spaces, you must use quotation marks around it.
If you need to override existing reports, you use the same path for the source and destination folders.
You can get the help information for the ReportUpdater by launching the utility in the command prompt without parameters, as the following screenshot shows.
Figure: The ReportUpdater help information
The following example shows the use of the ReportUpdater utility for the Test application, as illustrated in the screenshot below.
ReportUpdater.exe src=C:\aaa dest="C:\bbb dest" url=http://localhost/Test login=gibbs password=123

Figure: The ReportUpdater utility in use
In the example, the command line does not contain the log parameter; therefore, the ReportUpdater utility displays the log information in the command prompt window.

Report Creation: To Copy an Existing Report

In the following activity, you will learn how to copy an existing report, which can be a predefined report or a report designed through the Acumatica Report Designer (for example, a report created by a colleague who works on customizations). Lesson 3: Creating a Report | 29

Story

Suppose that you are a technical specialist in your company, and you are working on customizations. An accountant of the company has requested a report that displays the AR register. You have oﬀered the accountant to use the AR Register (AR621500) report, which is a predefined report in Acumatica ERP, but the accountant has asked for modifications to the report. You have decided to make a copy of the report and change the copy in the Report Designer rather than directly modifying the predefined report. As a first step, you will create and save the copy of the report.

Process Overview

In this activity, you will first open and examine the AR Register (AR621500) report in Acumatica ERP. Then you will open the report in the Acumatica Report Designer and save a copy of it with a diﬀerent name.

System Preparation

Before you begin performing the steps of this activity, launch the Acumatica ERP website with the U100 dataset preloaded, and sign in as a system administrator Kimberly Gibbs by using the gibbs username and the 123 password.

          The gibbs user is assigned the Administrator role and the Report Designer role. Thus, this user has
          suﬀicient access rights to manage system configuration and to preview, save, and publish reports.

Step 1: Opening and Viewing the Existing Report in Acumatica ERP To open and view the AR Register (AR621500) report, which is an existing report in Acumatica ERP, do the following:

On the main menu, click the Receivables menu item.
In the Reports category of the Receivables workspace, click the AR Register link to open the report form (that is, the form you use to specify parameters and run the report). Notice that in the address bar of the browser, the ScreenId parameter is AR621500. This is the form identifier of the report.
```
               Alternatively, to open a form, you can search for it by its name or by its form ID without
               periods.
```
In the From Period box of the Report Parameters tab, select 01-2024, in the To Period box, leave the default value.
On the form toolbar, click Run Report. The resulting report is displayed.

Step 2: Opening the Existing Report in the Acumatica Report Designer To open the existing report in the Acumatica Report Designer, do the following:

On the Windows Start menu, select All Programs > Acumatica > Report Designer.
On the Report Designer menu bar, click File > Open From Server.
In the Open Report from Server dialog box, which opens, do the following: Lesson 3: Creating a Report | 30

In the Site URL to Load Reports box, type the URL of your Acumatica ERP website in the following format: http://, where is the actual URL of your instance. The URL can be your local website or an external URL of Acumatica ERP.
In the Login box, type your username as follows: gibbs. If your instance contains more than one tenant, in the Login box, type your tenant name aer your username and the @ symbol as follows: gibbs@.
In the Password box, type your password: 123.
Click the Load Reports button to load the list of reports from the website.

In the Report to Load box, select AR621500.rpx, and click OK. The dialog box is closed, and the opened report file is displayed in the Design pane of the Report Designer.

        While you are viewing the report form of any existing report in Acumatica ERP, you can start the
        Report Designer and open the report as follows:

On the report form toolbar, click Edit Report. An .rps file is saved on your local machine.
On the Report Designer menu bar, click File > Open, locate the previously saved .rps file and open it. The Open Report from Server dialog box is displayed with all the boxes filled in except Password.

Type your password in the Password box and click OK.

       Note that this capability is available only if you are signed in to a user account with the Report
       Designer role.

Step 3: Saving a Copy of the Opened Report on the Acumatica ERP Server To save the opened report in the Acumatica Report Designer to the Acumatica ERP server, do the following:

On the Report Designer menu bar, click File > Save To Server. The Save Report on Server dialog box opens.
In the Name of Report to Save box, type AR6215C1.rpx.
Type your username and your password, and click OK.
The copy of the report is saved to the server.

Report Creation: To Create a Report Based on One DAC

In the following activity, you will create a report based on one DAC.

          This activity is based on the U100 dataset. If you are using another dataset, or if any system settings
          have been changed in U100, these changes can aﬀect the workflow of the activity and the results of
          the processing. To avoid any issues, restore the U100 dataset to its initial state.

Story

Suppose that you are a technical specialist in your company who is working on simple customizations. An accountant of your company has requested a report that collects data about invoices and memos. The accountant Lesson 3: Creating a Report | 31

wants a simple report that displays columns with the document type, the invoice reference number, and the balance of the invoice with that number.

Process Overview

You will first inspect the Invoices and Memos (AR301000) form to explore which data access classes you can use to access the needed data: the document type, reference number, and balance. With the knowledge you have obtained, you will create the report and add the columns to be displayed. When the report is created and all the necessary settings are specified, you will preview and then publish the report.

System Preparation

Step 1: Discovering DACs and Data Fields To inspect the user interface elements whose data you will use for your report, do the following:

In Acumatica ERP, open the Invoices and Memos (AR301000) form.
Point to the Reference Nbr. element, press Ctrl+Alt, and then click. The Element Properties dialog box opens. You are interested in the Data Class and Data Field values, which correspond to the class and field you need. Notice that these values are ARInvoice and RefNbr, respectively. Close the dialog box.
On the form, point at the Balance element, press Ctrl+Alt, and click to again open the Element Properties dialog box. Notice that the Data Class value is again ARInvoice, while the Data Field value is CuryDocBal. Close the dialog box.
On the form, inspect the Type element as you inspected elements in the previous instructions to find its data class and data field. Notice that its Data Class value is again ARInvoice and its Data Field value is DocType.
You have discovered that the data access class you need is ARInvoice and the data fields are DocType, RefNbr, and CuryDocBal.

Step 2: Loading the Database Schema To load the database schema, do the following:

On the Report Designer menu bar, click File > New to create a report. An empty report with the default sections opens.
On the Report Designer menu bar, click File > Build Schema. The Schema Builder opens with the Tables tab selected.

In the Site URL to Load Database Tables box, enter the URL of your Acumatica ERP website, which can be your local website or an external URL of Acumatica ERP.

              The history of successful connections is automatically saved in the drop-down list of the URL
              box. For example, if you have already opened a report from the server, you can select the URL
              from the drop-down list.

Type gibbs as the username and 123 as the password.
Click Load Schema. Lesson 3: Creating a Report | 32
In the Search box, type ARInvoice.
In the Report Tables list, click ARInvoice (PX.Objects.AR.ARInvoice), and click >. The ARInvoice data access class is placed in the right pane of the Schema Builder.
In the Schema Builder, click OK. The Schema Builder is closed.

Step 3: Adding Data to the Report To add data to the report, while you are still working with the report in the Report Designer, do the following:

In the Properties pane, open the Fields tab.
In the list of the fields of the ARInvoice data access class, drag the DocType field to the detailSection1 section. Notice that in detailSection1, you have a box with the following content: =ARInvoice.DocType.
Repeat the actions of the previous instruction with the RefNbr and CuryDocBal fields. Place RefNbr to the right of DocType, and CuryDocBal next to RefNbr.
In the Tools pane, drag the TextBox element to the pageHeaderSection1 section. This element will be a column header. Place the element above the element with the =ARInvoice.DocType content.

While the text box is selected, go to the Properties tab of the Properties pane. For the Appearance > Value property, enter Document Type.

              Alternatively, you could double-click the added text box, enter Document Type, and press
              the Enter key.

Add two more TextBox elements to the pageHeaderSection1 section with the following values: Reference Number and Balance. These elements will be the headers of two other columns. Place these elements above the element with the =ARInvoice.RefNbr and =ARInvoice.CuryDocBal content.
In the Tools pane, drag the Line element to pageHeaderSection1. Extend the line. Place the line below the added text boxes. This line will divide the heading of the table in the report.
Add two more TextBox elements to the pageHeaderSection1 section with the following values: Page and =PageOf. These elements will display the current page number and the total number of pages in the report.
Add one more text box to the pageHeaderSection1 section with the Invoices and Memos value to display the report name. 10.Adjust the height of the pageHeaderSection1 and detailSection1 sections by dragging the section borders to be the same as the height of the added elements in the sections. Set the height of pageFooterSection1 to the minimum.
The following screenshot shows the report you have designed. Lesson 3: Creating a Report | 33
Figure: The report designed to display data about invoices and memos
Before saving the report, preview it. In the Design pane of the Report Designer, go to the Preview tab. If you are not satisfied with the design of the report, move the text boxes to align them.

Step 4: Saving the Report To save the report on your Acumatica ERP website, do the following:

On the Report Designer menu bar, click File > Save to Server. The Save Report on Server dialog box opens.
In the dialog box, in the Site URL to Load Database Tables box, type the URL of your Acumatica ERP website.
In the Login box, type gibbs, and in the Password box, type 123.
In the Name of Report to Save box, enter the report name: AR308000.
Click OK.

Step 5: Publishing the Report To publish the report, do the following:

In Acumatica ERP, open the Site Map (SM200520) form.
Add a new row with the following settings:

Screen ID: AR.30.80.00
Title: All Invoices and Memos
URL: ~/Frames/ReportLauncher.aspx?ID=AR308000.rpx
Workspaces: Receivables
Category: Reports

Save your changes.
On the Access Rights by Screen (SM201020) form, specify the access rights for the added report as follows: a. In the Receivables node, click All Invoices and Memos. b. In the right pane, select Granted in the Access Rights column for the ReportDesigner, AR Admin, and Accountant roles. c. On the form toolbar, click Save.

Step 6: Viewing the Report To view and run the report you have created, do the following: Lesson 3: Creating a Report | 34

In the main menu of Acumatica ERP, click the Receivables menu item.
In the Reports category of the Receivables workspace, click All Invoices and Memos.
On the report form toolbar, click Run Report.

The created report with the information about invoices and memos is displayed. The following screenshot shows the resulting report.

Figure: The resulting report displaying information about invoices and memos Lesson 4: Getting Data from Multiple DACs | 35