Lesson 3

Working with Report Rows

Back to F350: Analytical Reports

Lesson 3: Working with Report Rows

Row Sets: General Information

A row set is a set of rows to be used in a report or in a group of reports that all use the same rows. You define row sets and their properties on the Row Sets (CS206010) form. Although a row set can be used in multiple reports, each report can be associated with only one row set. You specify what rows will be displayed in the analytical report by creating a new row set, adding rows to it, and specifying this row set for the report on the Report Definitions (CS206000) form.

Learning Objectives

In this lesson, you will learn about the properties of the rows in a row set and how to add, remove, and work with the rows in a row set.

Applicable Scenarios

You may find the information in this chapter useful if you are responsible for developing and supporting analytical reports in Acumatica ERP and need to add or modify rows of a report.

Properties of a Row Set

In the Summary area of the Row Sets (CS206010) form, you specify the code, description, and type of the data source for the row set. In the Type box, you select the GL type to display in your report accounting data from the general ledger, or the PM type to display project accounting data. The Code and Description properties of the row set help you to uniquely identify the row set in the system.

Row Sets: Row Attributes

In the table of the Row Sets (CS206010) form, you can add rows to the row set. Each row has its properties displayed in the lines of the form, which is shown in the following screenshot. Lesson 3: Working with Report Rows | 24

Figure: The Row Sets form

The Code and Description of a Row

The row code (Code column) is a unique value you specify when you add a new row to the row set. This code is used to identify the row within the row set, and the references to the rows in the formulas also include the row codes. The code assigned to the row can be changed later, but we do not recommend that you change the existing row codes, because they may be used in the formula references to this row. The rows in the row sets are sorted by row code in ascending order, and this is the order in which they appear in the generated report. The row description (Description column) is a descriptive definition of the row contents. The data from this column is not displayed in the analytical report. To include a row description in the report, you should add a column of the Desc type to the column set.

Row Types

By defining the Type attribute of a row, you specify what data will be displayed in a particular report row, and how the data in the row will be processed. You can select one of the following row types:

  • GL: A row of this type contains accounting data that satisfies the filtering conditions specified in the Data Source column of the row.
  • Caption: A row of this type is used to display in the report the text specified in the Description column of the row. If no description is specified, the row of this type is displayed as an empty line, which can be used to add space between groups of rows in the report.
  • Line: A row of this type is displayed in the report as a line. With these lines, you can improve the readability of the report and visually segregate groups of lines displayed in the report. You can specify the line style in the Line Style column of the row and the thickness of the row in the Height column.
  • Total: A row of this type is used when the Value column of the row contains a formula to calculate a sum or another calculated value.
  • Header: A row of this type contains the report header—that is, all lines of the report title and the column headers, which are specified in the column set, are displayed in this row.
  • Sort: A row of this type contains the sorting conditions of some report rows in the Value column. A row of this type is not displayed in the report; instead, it affects the order of the data displayed in the report. Lesson 3: Working with Report Rows | 25

The Value of a Row

By filling in the Value column for a row in the row set, you define a particular value to be displayed in the report line. The values in the rows can be predefined parameters or computed values that the system calculates by using formulas. Values are frequently used to insert sums and totals into the report lines. The formulas used to calculate the data in the report lines usually include references to other rows or particular cells in the report. The expressions used to insert the data in the report rows can also use parameter queries to select the necessary data from the data source.

          When you fill in the Value column to display total values in the row, you need to ensure that the
          appropriate Type is selected for this row to exclude it from the data that can be selected as a source
          for summarized value calculation.

Row Sets: To Add a Row with Summary Data

In this activity, you will add a report row with summary data retrieved from the GL accounts of an account class.

Story

Suppose that you are a technical specialist at SweetLife Fruits & Jams who is responsible for supporting analytical reports. You have developed most of the Profit & Loss report according to the company's needs, and now need to add a row displaying the aggregated amount of service and utility expenses. In your instance of Acumatica ERP, these expenses are recorded to the GL accounts of the EXSERVICE - Service and Utilities account class. So you need to add a report row that will total the amounts from all GL accounts of this class.

Configuration Overview

In the training dataset, the following tasks have been performed for the purposes of this activity:

  • On the Row Sets (CS206010) form, the F350RS1 row set has been created.
  • On the Report Definitions (CS206000) form, the F350RD1 report definition has been created with F350RS1 selected as the row set.

Process Overview

In this activity, you will do the following:

  1. On the Report Definitions (CS206000) form, review the Profit & Loss report.
  2. On the Chart of Accounts (GL202500) form, review the structure of accounts of the EXSERVICE account class.
  3. On the Row Sets (CS206010) form, modify the row set to display the aggregated amount from the accounts of the EXSERVICE account class in a single row.
  4. On the Report Definitions form, review the updated Profit & Loss report.

Step 1: Reviewing the Existing Report To review the existing version of the Profit & Loss report, do the following:

  1. On the Report Definitions (CS206000) form, open the report definition with the F350RD1 code. Lesson 3: Working with Report Rows | 26
  2. On the form toolbar, click Preview. The report form opens in a pop-up window.
  3. On the Report Parameters tab of the report form, specify the following report parameters:
  • Company: SWEETLIFE
  • Ledger: ACTUAL
  • Financial Period: 12-2023
  1. On the report form toolbar, click Run Report. The Profit & Loss report is opened in a pop-up window. Notice that no data is displayed for service expenses in the expense section of the report.

Step 2: Reviewing the Accounts of the Account Class To review the accounts that belong to the EXSERVICE account class, do the following:

  1. Open the Chart of Accounts (GL202500) form.
  2. Click the header of the Account Class column.
  3. In the Sorting and Filtering Settings dialog box, which opens, do the following: a. Make sure Equals is selected in the list of filter conditions. b. In the Value box, specify EXSERVICE. c. Click OK. The system closes the dialog box, In the list of accounts, the accounts that belong to the EXSERVICE account class are displayed. (See the following screenshot.)
    Figure: The accounts of the EXSERVICE account class

Step 3: Updating the Row Set To modify the row in the row set, do the following:

  1. On the Report Definitions (CS206000) form, open the report definition with the F350RD1 code.
  2. In the Report Definition section, click the Edit button to the right of the Row Set box. The Row Sets (CS206010) form opens with the F350RS1 row set selected.
  3. Add a new row with the following settings:
  • Code: 0340
  • Description: Services and Utilities
  • Type: GL
  1. On the form toolbar, click Save.
  2. In the row with the 0340 code, double-click the Data Source cell, and then click the magnifier button.
  3. In the Data Source dialog box, which opens, do the following: a. Select EXSERVICE in the Account Class box. Lesson 3: Working with Report Rows | 27
       b. Make sure that Expand is set to Nothing.
      With this setting, data from all accounts of the selected class will be displayed in the same line of the
      report.
  4. Click OK to close the dialog box.
  5. In the Indent column of the 0340 row, type 10.
  6. In the Hide Zero column, select the check box. 10.Save your changes to the row set.

Step 4: Reviewing the Updated Report To review the updated version of the Profit & Loss report, do the following:

  1. On the Report Definitions (CS206000) form, open the report definition with the F350RD1 code.
  2. On the form toolbar, click Preview. The report form opens in a pop-up window.
  3. While the F350RD1 report definition is still open on the Report Definitions (CS206000) form, specify the following report parameters:
  • Company: SWEETLIFE
  • Ledger: ACTUAL
  • Financial Period: 12-2023
  1. On the report form toolbar, click Run Report. The Profit & Loss report is opened in a pop-up window. Notice that the Services and Utilities row is now displayed in the report. (See the following screenshot.) Lesson 3: Working with Report Rows | 28
    Figure: The added summary row for Services and Utilities

Row Sets: To Expand a Row to Show Detailed Data

In this activity, you will expand a row to show detailed data. Specifically, you will modify a row that displays summary data retrieved from the GL accounts of an account class. The updated report will show a separate row for each GL account included in the account class.

Story

Suppose that you are a technical specialist at SweetLife Fruits & Jams who is responsible for supporting analytical reports. You have developed the initial version of the Profit & Loss report according to the company's requirements. However, SweetLife's chief accountant has asked you to display the sales data in the report in more detail. You need to update the row that currently shows the sales data aggregated from all accounts of the sales account class so that it displays more information.

Configuration Overview

In the training dataset, the following tasks have been performed for the purposes of this activity:

  • On the Row Sets (CS206010) form, the F350RS2 row set has been created. Lesson 3: Working with Report Rows | 29
  • On the Report Definitions (CS206000) form, the F350RD2 report definition has been created with F350RS2 selected as its row set.

Process Overview

In this activity, you will do the following:

  1. On the Report Definitions (CS206000) form, review the Profit & Loss report.
  2. On the Chart of Accounts (GL202500) form, review the structure of accounts of the SALES account class.
  3. On the Row Sets (CS206010) form, modify the row set to display the amounts from the accounts of the SALES account class in separate rows.
  4. On the Report Definitions form, review the updated Profit & Loss report.

Step 1: Reviewing the Existing Report To review the existing version of the Profit & Loss report, do the following:

  1. On the Report Definitions (CS206000) form, open the report definition with the F350RD2 code.
  2. On the form toolbar, click Preview. The report form opens in a pop-up window.
  3. On the Report Parameters tab of the report form, specify the following report parameters:
  • Company: SWEETLIFE
  • Ledger: ACTUAL
  • Financial Period: 12-2023
  1. On the report form toolbar, click Run Report. The Profit & Loss report opens in a pop-up window. Notice that the sales data is shown in one line, Sales (as shown in the following screenshot).
  2. Close the pop-up window. Lesson 3: Working with Report Rows | 30
    Figure: Aggregated sales data in the Profit & Loss report

Step 2: Reviewing the Accounts of the Account Class To review the accounts that belong to the SALES account class, do the following:

  1. Open the Chart of Accounts (GL202500) form.
  2. Click the header of the Account Class column.
  3. In the Sorting and Filtering Settings dialog box, which opens, do the following: a. Make sure Equals is selected in the list of filter conditions. b. In the Value box, specify SALES. c. Click OK. The system closes the dialog box, In the list of accounts, the accounts that belong to the SALES account class are displayed. (See the following screenshot.) Lesson 3: Working with Report Rows | 31
    Figure: The accounts of the SALES account class

Step 3: Updating the Row Set To modify the row in the row set, do the following:

  1. On the Report Definitions (CS206000) form, open the report definition with the F350RD2 code.
  2. In the Report Definition section, click the Edit button to the right of the Row Set box. The Row Sets (CS206010) form opens with the F350RS2 row set selected.
  3. In the row with the code 0100, click the Data Source cell, and then click the magnifier button.
  4. In the Data Source dialog box, which opens, set Expand to Account. With this option selected, the GL accounts that belong to the SALES account class (which is selected in the Account Class box) will be displayed in separate lines of the report.
  5. In the Row Description box, select Description. The descriptions of the accounts of the SALES account class will be displayed as the row descriptions.
  6. Click OK to close the dialog box.
  7. Save your changes to the row set.

Step 4: Reviewing the Updated Report To review the updated version of the Profit & Loss report, do the following:

  1. On the Report Definitions (CS206000) form, open the report definition with the F350RD2 code.
  2. On the form toolbar, click Preview. The report form opens in a pop-up window.
  3. On the Report Parameters tab of the report form, specify the following report parameters:
  • Company: SWEETLIFE
  • Ledger: ACTUAL
  • Financial Period: 12-2023
  1. On the report form toolbar, click Run Report. The Profit & Loss report is opened in a pop-up window. Notice that three lines are now displayed in the report (see the following screenshot). Lesson 3: Working with Report Rows | 32
    Figure: Sales data expanded by account

Row Sets: To Add a Row with a Total

The following activity will walk you through the process of adding a row that displays a total—in this case, a subtotal of a subset of other rows.

Story

Suppose that you are a technical specialist at SweetLife Fruits & Jams who is responsible for supporting analytical reports. SweetLife's manager has requested that you update the existing Profit & Loss report with detailed information about sales as well as the total sales amount. The rows with Total Sales and Net Income should be highlighted to be immediately visible to the users of the report. You have already added the rows with detailed sales data to the Profit & Loss report. Now you need to add a row that will display a subtotal of all sales amounts and highlight the needed rows.

Configuration Overview

In the training dataset, the following tasks have been performed for the purposes of this activity:

  • On the Row Sets (CS206010) form, the F350RS3 row set has been created. Lesson 3: Working with Report Rows | 33
  • On the Report Definitions (CS206000) form, the F350RD3 report definition has been created with F350RS3 selected as its row set.

Process Overview

In this activity, you will do the following:

  1. On the Report Definitions (CS206000) form, review the Profit & Loss report.
  2. On the Row Sets (CS206010) form, modify the row set to display a line with the subtotal of the sales amounts.
  3. On the Report Definitions form, review the updated Profit & Loss report.

Step 1: Reviewing the Existing Report To review the existing version of the Profit & Loss report, do the following:

  1. On the Report Definitions (CS206000) form, open the report definition with the F350RD3 code.
  2. On the form toolbar, click Preview. The report form opens in a pop-up window.
  3. On the Report Parameters tab of the report form, specify the following report parameters:
  • Company: SWEETLIFE
  • Ledger: ACTUAL
  • Financial Period: 12-2023
  1. On the report form toolbar, click Run Report. The Profit & Loss report is opened in a pop-up window. Notice that three rows with sales data are displayed in the report.
  2. Close the pop-up window. Lesson 3: Working with Report Rows | 34
    Figure: Sales data without the total sales amount

Step 2: Adding a Row with the Subtotal To add a row with the subtotal to the row set, do the following:

  1. While the F350RD3 report definition is still open on the Report Definitions (CS206000) form, in the Report Definition section, click the Edit button to the right of the Row Set box. The Row Sets (CS206010) form opens with the F350RS3 row set selected.
  2. Add a new row with the following settings:
  • Code: 0102
  • Description: Total Sales
  • Type: Total
  • Value: =@0100 The 0100 row has been expanded to display individual amounts of the accounts of the SALES account class. With these settings, the 0102 row will display the total of these amounts.
  1. On the form toolbar, click Save.

Step 3: Formatting the Rows To format the rows with Total Sales and Net Income, while you are still on the Row Sets (CS206010) form with the F350RS3 row set selected, do the following: Lesson 3: Working with Report Rows | 35

  1. In the row with the 0102 code, double-click the cell of the Style attribute, and click the magnifier button.
  2. In the Style dialog box, which opens, specify the following settings:
  • Backgr. Color: Yellow
  • Bold: Selected
  1. Click OK to close the dialog box.
  2. On the form toolbar, click Save.
  3. Click any cell of the row with the 0102 code, and click Copy Style on the table toolbar.
  4. Click any cell of the row with the 0480 code, and click Paste Style on the table toolbar. The style that you defined for row 0102 will be applied to row 0480.
  5. On the form toolbar, click Save.

Step 4: Reviewing the Updated Report To review the updated version of the Profit & Loss report, do the following:

  1. On the Report Definitions (CS206000) form, open the report definition with the F350RD3 code.
  2. On the form toolbar, click Preview. The report form opens in a pop-up window
  3. On the Report Parameters tab of the report form, specify the following report parameters:
  • Company: SWEETLIFE
  • Ledger: ACTUAL
  • Financial Period: 12-2023
  1. On the report form toolbar, click Run Report. The Profit & Loss report is opened in a pop-up window. Notice that the sales subtotal row is now displayed and that the two rows are highlighted in yellow (as shown in the following screenshot). Lesson 3: Working with Report Rows | 36
    Figure: The new Total Sales line

Row Sets: Sorting Rows

The Order of Report Rows

The order of the rows in a row set depends on the values you specify in the Code column of the table on the Row Sets (CS206010) form. The lower the code of a row, the higher the row will be listed in the row set. If you do not specify any sorting conditions for the row set, the rows will be displayed in the report in the order in which they are listed in the row set.

Sorting Report Rows

You can sort a group of rows of the report in ascending or descending order by adding a row of the Sort type. The row of this type contains the sorting conditions for some report rows in the Value column. This row is not displayed in the report; instead, it affects the order of the data displayed in the report. The location of the rows of the Sort type in the row set does not affect the sorting conditions. You can sort orders in ascending order by using the SORT() function. The first two attributes of the function define the range of rows to be sorted, and the third attribute determines the column of the column set by which the values should be sorted. For example, the SORT('300','315','B') expression added as the Value attribute of a row of the Sort type will sort rows with codes from 300 to 315 in ascending order based on the value these rows have in column B. Lesson 3: Working with Report Rows | 37

To sort orders in descending order, you use the SORTD() function. The attributes of this function are the same as the attributes of the SORT() function.

          You should keep all rows with sorting conditions together in a particular row set. The location of rows
          with sorting conditions in the row set does not affect sorting conditions. For easier maintenance of
          row sets, we recommend that you add the rows with sorting conditions to the end of each row set and
          keep all conditions listed together.

For information on using formulas, see Formulas: General Information.

Reserving Codes for Future Use

When you are creating a report, you should reserve row codes—that is, omit a particular number or a range of numbers aer each row code. For example, if the code of the first row of your row set is 0010, omit the numbers 0011 through 0019, and add the next row with the 0020 code. Similarly, you should then omit the numbers 0021 through 0029 and use the 0030 code for the next row; you then continue using multiples of 10 as the codes. The order of rows in a row set depends on their codes. If you do not specify any sorting conditions for the row set, the rows are displayed in the report in the order in which they are listed in the row set. Reserving row codes gives you the ability to add new rows between existing ones. For example, if you decide to expand a row of an existing report, you will be able to add a subtotal aer this row. You can renumber all rows of a row set if the code of the topmost row is an integer value. To renumber all rows, click the Renumber button on the table toolbar. In the dialog box that opens, specify the number to be omitted aer each row code in the Numbering Step box, and specify the length of codes in the Length box. When you confirm renumbering by clicking the Renumber button of the dialog box, the system takes the code of the topmost row, corrects its length according to the specified Length, and sequentially renumbers each next row by adding the specified Numbering Step to the previous row. The codes that are used in the Linked Row, the Base Row, and the formulas of the row set will be changed accordingly.

Row Sets: To Change the Order of Rows

The following activity will walk you through the process of reordering rows in a report.

Story

Suppose that you are a technical specialist at SweetLife Fruits & Jams who is responsible for building and supporting analytical reports. The chief accountant has asked you to update the lines displayed in the existing Profit & Loss report so that the lines with year-to-date sales amounts are displayed in descending order.

Configuration Overview

In the training dataset, the following tasks have been performed for the purposes of this activity:

  • On the Row Sets (CS206010) form, the F350RS4 row set has been created.
  • On the Report Definitions (CS206000) form, the F350RD4 report definition has been created with F350RS4 selected as its row set.

Process Overview

In this activity, you will do the following:

  1. On the Report Definitions (CS206000) form, review the Profit & Loss report. Lesson 3: Working with Report Rows | 38
  2. On the Row Sets (CS206010) form, modify the row set to display the lines with sales amounts in descending order.
  3. On the Report Definitions form, review the updated Profit & Loss report.

Step 1: Reviewing the Existing Report To review the existing version of the Profit & Loss report, do the following:

  1. On the Report Definitions (CS206000) form, open the report definition with the F350RD4 code.
  2. On the form toolbar, click Preview. The report form opens in a pop-up window.
  3. On the Report Parameters tab of the report form, specify the following report parameters:
  • Company: SWEETLIFE
  • Ledger: ACTUAL
  • Financial Period: 12-2023
  1. On the report form toolbar, click Run Report. The Profit & Loss report opens in a pop-up window. Notice that three rows with sales data are displayed in the report. These rows are not ordered by the amounts in the YTD column.
  2. Close the pop-up window.
    Figure: Sales data with unsorted sales amounts Lesson 3: Working with Report Rows | 39

Step 2: Sorting the Rows with Sales Amounts To sort the sales rows, while the F350RD4 report definition is still open on the Report Definitions (CS206000) form, do the following:

  1. In the Report Definition section, click the Edit button to the right of the Row Set box. The Row Sets (CS206010) form opens with the F350RS4 row set selected.
  2. Add a new row with the following attributes:
  • Code: 0900
  • Description: Sorting Sales
  • Type: Sort
  • Value: =SORTD('0100','0100','B') For the SORTD function, you specify the start and the end of the row range to which you need to apply sorting (in this case, you apply sorting to only one row, 0100), and the column by whose values you need to sort the rows (column B).
  1. On the form toolbar, click Save.

Step 3: Reviewing the Updated Report To review the updated version of the Profit & Loss report, do the following:

  1. On the Report Definitions (CS206000) form, open the report definition with the F350RD4 code.
  2. On the form toolbar, click Preview. The report form opens in a pop-up window.
  3. On the Report Parameters tab of the report form, specify the following report parameters:
  • Company: SWEETLIFE
  • Ledger: ACTUAL
  • Financial Period: 12-2023
  1. On the report form toolbar, click Run Report. The Profit & Loss report opens in a pop-up window. Notice that the sales rows are now sorted in descending order (as shown in the following screenshot). Lesson 3: Working with Report Rows | 40

Figure: Lines with sales amounts sorted in descending order Lesson 4: Working with Report Columns | 41