Lesson 8: Sorting and Grouping Data

This lesson explains how you can sort and group data in reports.

Data Sorting and Grouping: General Information

As you define a report by using the Acumatica Report Designer, you can divide the data into groups, each of which displays the sorted data in the order selected for the group. The sorting criteria are defined separately for every report group and for the report as a whole. To set up the data grouping and sorting rules, you should define the following:

The data groups to be included in the report and their sorting rules
The data sorting rules for the report

Learning Objectives

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

Create data groups in a report
Specify sorting criteria for the data in a report group and the data in a report

Applicable Scenarios

You may want to use data sorting and grouping in 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.
Your colleagues have asked you to group and sort the data in reports.

Data Grouping

Data groups are used to structure and logically group data in a report. You can add new data groups to the report and define the behavior properties for each group. The groups' data will be displayed on the pages of the generated report. For example, you may want to group sales orders by date and status to get the count of sales orders. By defining data groups in a report, you specify a GROUP BY clause for the SQL statement that is generated by the report. You define data groups in the Properties pane of the Report Designer, on the Properties tab. You use the Data > Groups property to open the Group Collection Editor (that is, the Group Collection Editor dialog box), where you can specify the names of groups and their properties.

          The Data property is available only if the report is selected but none of its elements, such as a section,
          is selected. An indicator of the selected report is the icon in the top le corner of the Design pane of
          the Report Designer.

When you create a data group, its header and footer sections are added to the report layout, which is displayed in the Design pane of the Report Designer: The header section of the group is placed between the header section of the page and the detail section; the footer section of the group is placed between the detail section and the footer section of the page. If your report already has data groups and you add a new group, the header section and the footer section of the new group are inserted inside the header section and the footer section of the last created Lesson 8: Sorting and Grouping Data | 90

group. The order of groups is defined in the Group Collection Editor, where you can change the order of the groups in the report by using the buttons with the arrow icons.

Data Sorting

You can specify how the data in a report is sorted. If you have defined data groups in the report, the data in the groups is initially sorted according to the sorting rules (if they are specified) of the defined group, and then the data of the detail section is sorted according to the sorting rules (if they are specified) of the report. By defining the sort order, you specify an ORDER BY clause for the SQL statement that is generated by the report. If you need to sort data, you should select the object for sorting—that is, a group or the report. By default, a sort order is not specified for the detail section. You define the sort order of the data in a group or in a report in the Properties pane of the Report Designer, on the Properties tab. Depending on the object for sorting, you use the following properties:

Data > Sorting: You use this property to sort the data in a detail section of the report. By clicking the More button to the right of the property, you open the SortExp Collection Editor and specify the necessary sort order.
Data > Groups: You use this property to sort the data in a group. By clicking the More button to the right of the property, you open the Group Collection Editor, in which you select the group whose data you want to sort. For the Behavior > Grouping property of the selected group, you open the GroupExp Collection Editor and specify the values for the DataField and SortOrder properties. In the Schema Builder, on the Sorting and Grouping tab, you can view the settings that are specified for grouping and sorting. You can also change and define the sort order for existing groups.

Optional Data Sorting

Acumatica ERP gives users the ability to add sorting rules to a generated report. Before a user runs the report, on the report form, the user can redefine the sort order that the report designer has specified in the Report Designer (by using the Data > Sorting property of the Property pane). The user can also add data fields whose data will be sorted and specify the sort order. The user redefines and adds the sort order on the Additional Sort and Filters tab of the report form. The user can define only the sort order for the data in the report but cannot redefine the sort order for the data in specified groups. In the Report Designer, you can manage the list of fields that a user can use to define the sorting and filtering conditions of the published report. When a report is selected, in the Behavior > ViewerFieldsMode property, you can select the following settings:

MergeWithUsed (default): With this setting, on the Additional Sort and Filters tab of the report form, the user can select any of the following as the properties of filtering and sorting conditions: any data fields used in the text boxes, any data fields used in the sorting and filtering conditions of the report, and any data fields listed on the Viewer Fields tab of the Schema Builder.

OnlySpecified: With this setting, on the Additional Sort and Filters tab of the report form, the user can select only the data fields specified on the Viewer Fields tab of the Schema Builder.

                On the Viewer Fields tab of the Schema Builder, you can add all the data fields used in the
                report to the list of available properties. To do that, you click the Load Used Fields button.

Data Sorting and Grouping: To Specify a Data Group in a Report

In the following activity, you will specify a data group in a report. Lesson 8: Sorting and Grouping Data | 91

          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 customizations. A sales manager of your company has requested a report that displays data about vendors. You have oﬀered the predefined Vendor Summary (AP655000) report, but the sales manager wants the data to be grouped by vendor class. The sales manager has also asked you to display the total balance for each group. You will create the requested report by using a copy of the Vendor Summary report.

Process Overview

In the Report Designer, you will open the AP6550C1.RPX report, which is a copy of the Vendor Summary (AP655000) report, and add a new group. You will specify a grouping condition and other properties for the new group. You will then modify the report layout and place necessary elements in the new group. Then in the Schema Builder, on the Sorting and Grouping tab, you will review the changes that you have made on the Properties tab of the Properties pane.

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. Also, to prepare for use the file that is intended for this activity, do the following:

Download the AP6550C1.rpx file.
Open the downloaded file in the Report Designer.
On the Report Designer menu bar, select File > Save To Server, which opens the Save Report on Server dialog box.
In the dialog box, specify the connection string and sign-in credentials of your Acumatica ERP instance, type AP6550C1 as the report name, and click OK. The report is saved on the server.

Step 1: Creating a Group in the Report To create the group in the AP6550C1 report, perform the following steps:

In the Report Designer, make sure that the AP6550C1 report (which you have saved to the server) is open.
In the top le corner of the Design pane, click the icon to select the whole report as an object.
On the Properties tab of the Properties pane, in the Data > Groups property, click the More button.
In the Group Collection Editor, which opens, in the Members pane on the le, click Add. A new group with the default group2 name is added and displayed in the Members pane.
Click OK to save the new group and close the Group Collection Editor.
On the report layout, notice that the new group is placed inside the existing group1. Lesson 8: Sorting and Grouping Data | 92

Step 2: Specifying the Properties of the Group To specify the group properties, while you are still working with the AP6550C1 report in the Report Designer, do the following:

In the top le corner of the Report Designer screen, click the icon to select the whole report as an object.
On the Properties tab of the Properties pane, in the Data > Groups property, click the More button.
In the Group Collection Editor, which opens, in the Members pane, select group2.
To the right of the Members pane, click the button with the arrow pointing upward to place group2 at the top of the list. The order of the groups in the Group Collection Editor defines the order of groups in the report.
In the group2 properties pane on the right side of the Group Collection Editor, in the Design > (Name) property, type NewGroup.
Select the Behavior > Grouping property, and click the More button.
In the GroupExp Collection Editor, which opens, in the Members pane, click Add.

In the PX.Reports.GroupExp properties pane, in the Misc > DataField property, type Vendor.VendorClassID to specify the grouping condition based on the class of a vendor.

              By clicking the More button to the right of the Misc > DataField collection, you can invoke the
              Expression Editor. In the le pane, you can select the necessary field from the list.

   Notice that the Misc > SortOrder property has the Ascending value, which means that by default, the data in
   the group is sorted in ascending order.

Click OK to save your changes and close the GroupExp Collection Editor. 10.Click OK to close the Group Collection Editor.

Step 3: Placing Elements in the New Group To place elements in the new group, while you are still working with the AP6550C1 report in the Report Designer, do the following:

In the Design pane of Report Designer, in the pageHeaderSection2 section, select the text box with the Class value and move it to the groupHeaderSection2 (Header of NewGroup) section (to the le).
In the groupHeaderSection1 (Header of group1) section, select the text box with the =Vendor.VendorClassID value, and move it to the groupHeaderSection2 (Header of NewGroup) section, to the right of the text box with the Class value.
```
              If values are not displayed in the text boxes, you can enlarge the height of the text boxes.
              Alternatively, you can view values in the Appearance > Value property of the text boxes.
```

Adjust the height of the groupHeaderSection2 (Header of NewGroup) section to be the same as the height of the text boxes in the section.

              You can also adjust the height of the groupHeaderSection2 (Header of NewGroup)
              section on the Properties tab of the Properties pane. Select groupHeaderSection2 (Header of
              NewGroup), and for the Appearance > Height property, type the value as follows: 20px.

In the groupHeaderSection1 (Header of group1) section, copy the text box with the following value: =sum(APHistory.FinYtdBalance) (this is the second element from the right). Paste the Lesson 8: Sorting and Grouping Data | 93
```
   text box in the groupFooterSection2 (Footer of NewGroup) section, in the column with the
   Current Balance title (see pageHeaderSection2). This element will display the total balance for
   the members of the NewGroup group.
```
In the groupHeaderSection1 (Header of group1) section, copy the text box with the following value: =BAccount.BaseCuryID (this is the rightmost element). Paste the text box in the groupFooterSection2 (Footer of NewGroup) section, in the column with the Balance Currency title (see pageHeaderSection2). This element will display the currency name of the total balance for the members of the NewGroup group.
Adjust the height of the groupFooterSection2 (Footer of NewGroup) section to be the same as the height of the text boxes in the section.
On the Report Designer window toolbar, click Save.
Click the Preview tab to preview the report. Make sure that the data of one group is displayed.

Step 4: Reviewing the Sorting and Grouping Settings in the Schema Builder While you are still working with the AP6550C1 report in the Report Designer, in the Report Designer, click File

Build Schema. In the Schema Builder, which opens, go to the Sorting and Grouping tab. Make sure that the settings on this tab are the same as those that you have specified in the Properties pane of the Report Designer. The settings are shown in the following screenshot.

Figure: Grouping and sorting settings for the AP6550C1 report

Step 5: Viewing the Report In Acumatica ERP, open the S150 Vendor Summary (AP6550C1) report form by searching for its identifier.

          This report, which you have modified in this activity, has been published in the U100 dataset. That is,
          it has been added to the Site Map (SM200520) form, and you can access it in Acumatica ERP.

On the report form toolbar, click Run Report. Notice that the data in the report is grouped by vendor class and that a total balance is displayed for each class. The following screenshot shows the report. Lesson 8: Sorting and Grouping Data | 94

Figure: The S150 Vendor Summary (AP6550C1) report, with data grouped by vendor class

Data Sorting and Grouping: To Specify Sorting Conditions

In the following activity, you will specify the sort order for the data in a report.

          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 customizations. An accountant of your company has requested a report that displays the AP batch register. You have oﬀered the predefined AP Batch Register Detailed (AP621000) report, but the sales manager has requested that the data be sorted by batch number in descending order and by debit amount in ascending order.

Process Overview

In the Report Designer, you will open the AP6210C1.RPX report, which is a copy of the AR Batch Register Detailed (AP621000) report. Then you will find the sections of the data for sorting. You will add the sorting condition for the data in the detailSection1 section and change the sort order in the existing group1 group. In the Schema Builder, on the Sorting and Grouping tab, you will review the changes that you have made on the Properties tab of the Properties pane. Lesson 8: Sorting and Grouping Data | 95

System Preparation

Download the AP6210C1.rpx file.
Open the downloaded file in the Report Designer.
On the Report Designer menu bar, select File > Save To Server, which opens the Save Report on Server dialog box.
In the dialog box, specify the connection string and sign-in credentials of your Acumatica ERP instance, type AP6210C1 as the report name, and click OK. The report is saved on the server.

Step 1 (Optional): Looking for the Report Sections of the Data for Sorting This step is not required if you understand in which sections the data for sorting is located. To find the sections in which the needed data—that is, the batch number and stock item description—is placed, do the following:

In the Report Designer, make sure that the AP6210C1 report (which you have saved to the server) is open.
Select groupHeaderSection1 (Header of group1), and in the Appearance > Style > BackColor property, select the light green color.

Repeat the action described in the previous instruction and select the light yellow color for detailSection1.

               For each section, you can use any color that you prefer. The only requirement is that the color
               for each section must be diﬀerent.

In the Report Designer window toolbar, select Save. The report is saved on the server.

In Acumatica ERP, open the S150 AP Batch Register Detailed (AP6210C1) report form by searching for its identifier.

               This report, which you have modified in this activity, has been published in the U100 dataset.
               That is, it has been added to the Site Map (SM200520) form, and you can access it in Acumatica
               ERP.

On the Report Parameters tab, in the From Period box, select 01-2024, and in the To Period box, select 12-2024. In other boxes, leave the default values.
On the report form toolbar, click Run Report. Notice that diﬀerent sections of the report have diﬀerent colors. The stock item description has a yellow background because it is located in detailSection1. The batch number has a green background because it is located in group1. Based on this information, you can make a conclusion about the sections in which you need to sort data. You will need to sort data in the group1 group by batch number (the Batch.BatchNbr field) and in the detailSection1 section by debit amount (GLTran.DebitAmt field). Lesson 8: Sorting and Grouping Data | 96

Step 2: Specifying the Sort Order for the Data Group To specify the sort order for the group1 data group, do the following:

While you are viewing the AP6210C1 report in the Report Designer, in the top le corner of the Design pane, click the icon.
In the Properties pane, to the right of the Data > Groups property, click the More button.
In the Group Collection Editor, which opens with the only group1 group selected in the Members pane, in the right pane (group1 properties), to the right of the Behavior > Grouping property, click the More button.
In the GroupExp Collection Editor, which opens, in the Members pane, select the second member (its Misc > DataField property is Batch.BatchNbr). In the Misc > SortOrder property, select Descending.
Click OK to save your changes and close the GroupExp Collection Editor.
Click OK to close the Group Collection Editor.

Step 3: Specifying the Sort Order for the Report Data To specify the sort order for the data in the report, while you are still working with the AP6210C1 report in the Report Designer, do the following:

In the top le corner of the Design pane, click the icon to select the report.
In the Properties pane, to the right of the Data > Sorting property, click the More button.
In the SortExp Collection Editor, which opens, click the Add button to add the new criteria for sorting.
In the right pane of the SortExp Collection Editor, in the Misc > DataField property, select GLTran.DebitAmt. This field will be used to sort the data in the report.
In the Misc > SortOrder property, make sure that the Ascending option is selected (this is the default value).
Click OK to save your changes and close the SortExp Collection Editor.
In the Report Designer window toolbar, click Save.

Step 4: Reviewing the Sorting and Grouping Settings in the Schema Builder While you are still working with the AP6210C1 report in the Report Designer, click File > Build Schema. In the Schema Builder, which opens, open the Sorting And Grouping tab. Make sure that the settings on this tab are the same as those that you have specified in the Properties pane of Report Designer. The settings in the Schema Builder are shown in the following screenshot.

Figure: Sorting and grouping settings for the AP6210C1 report in the Schema Builder Lesson 8: Sorting and Grouping Data | 97

Step 5: Viewing the Report To view the report, do the following:

In Acumatica ERP, open the S150 AP Batch Register Detailed (AP6210C1) report form by searching for its identifier.

              The report that you have modified in this activity has been published in the U100 dataset. That
              is, it has been added to the Site Map (SM200520) form, and you can access it in Acumatica ERP.

On the Report Parameters tab, in the From Period box, select 01-2025, and in the To Period box, select 12-2025. In other boxes, leave the default values.
On the report form toolbar, click Run Report.
Make sure that the report is sorted by batch number in descending order (see Item 1 in the following screenshot), and by debit amount in each batch in ascending order (Item 2). The following screenshot displays one of the report pages.
Figure: Sample page of the S150 AP Batch Register Detailed (AP6210C1) report Lesson 9: Modifying the Report Style | 98