Lesson 4

Working with Report Columns

Back to F350: Analytical Reports

Lesson 4: Working with Report Columns

Column Sets: General Information

Column sets are used to define the columns to be used in a specific report or in a variety of reports having a common layout. Every report can be associated with only one column set. When different reports use the same columns, you can create a single column set for them. You specify what columns will be displayed in the analytical report by creating a new column set, adding columns to it, and specifying this column set for the report on the Report Definitions (CS206000) form.

Learning Objectives

In this lesson, you will learn how to do the following:

  • Add a column with row descriptions
  • Add a column displaying values extracted from the general ledger
  • Add a column displaying calculated values

Applicable Scenarios

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

Column Set Properties

Each column in a column set has its properties displayed in the table of the Column Sets (CS206020) form. The properties of each column specify how the data is selected, calculated, formatted, and displayed in the particular column of a printable version of an analytical report.

Column Sets: Column Attributes

The Column Sets (CS206020) form, on which you define a column set, consists of two tables (shown in the following screenshot). Lesson 4: Working with Report Columns | 42

Figure: A column set on the Column Sets form

In the upper table of the form (Item 1 in the screenshot above), you define the report title, the column headers, and all the data that should be displayed on every report page. In the lower table (Item 2), you add the report columns and define column attributes. You click New in the toolbar of the upper table to add a line to the upper table—that is, to the report header. The added line is the bottom line of the upper table. You can select a line of the upper table and move the line up and down by clicking the appropriate arrow buttons on the toolbar of the upper table. You click New in the toolbar of the lower table to add a column to the far right of both the upper and the lower tables. In each table, you can select a column and move the column to the le and to the right by clicking the Lesson 4: Working with Report Columns | 43

appropriate arrow buttons on the table toolbar. The column is moved in one table and not in the other table of the form. You can also move a column in either table by dragging its header to the needed position.

The Code and Description of a Column

The system assigns a letter code (A, B, C, and so on) to every column that you add to the column set. The added columns are named as they would be named in an Excel file, and the letter assigned to the column cannot be changed later.

          The system assigns a letter code within the A - ZZZ range.

In the Description row, you can add a description of the contents of each column. The data from the Description row is not displayed in the analytical report; instead, you should add column headers in the upper table of the Column Sets (CS206020) form.

Column Types

The Type attribute of the columns shown in the lower table on the Column Sets (CS206020) form specifies what data will be displayed in a particular column. You can select one of the following types:

  • GL: A column of this type contains the data of the row set that also satisfies the filtering conditions specified in the Data Source dialog box for the column. The filtering conditions of rows of the GL type and the filtering conditions of the column are applied simultaneously.
  • Calc: A column of this type is used to calculate values with the formula that is specified in the Value attribute.
  • Descr: A column of this type displays the text of the Description attribute of the report row set.
                   You can also display the row description by using a formula with the @RowText parameter
               in a column of the Calc type as follows: =@RowText. For information about parameters, see
               Formulas: Parameters.

The Value of a Column

By specifying the Value attribute of a column in a column set, you define a certain value to be displayed in the report column. The values in the columns 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 columns. The formulas used to calculate the data in the report columns usually include references to other columns or particular cells in the report. The expressions defined for the report columns can also use parameter queries to select the values from the data source and insert them in the column cells.

          When you use the Value attribute to display predefined or calculated values in the column, you must
          ensure that the appropriate Type attribute is selected for this column; otherwise, the formula and
          value calculation rules will be ignored.

Cell Evaluation Order

Sometimes both the row and the column defining a cell may contain formulas as their values. In this case, you must define the Cell Evaluation Order attribute of the column, which determines the source of the formula that will be used to calculate the cell's value. You can select either of the following options:

  • Row: The formula of the row will be used to calculate the value. Lesson 4: Working with Report Columns | 44
  • Column: The formula from the column will be used to calculate the value.
                    You cannot set Cell Evaluation Order to Column for columns of the GL type.

Column Sets: To Add a Column with Row Descriptions

The following activity will walk you through the process of adding a column with row descriptions.

Story

Suppose that you are a technical specialist at SweetLife Fruits & Jams who is responsible for building and supporting analytical reports. You are starting to build a custom Profit & Loss report. You have already prepared the row set with all rows that need to be included in the report, and are now working on the report columns. First, you need to add a column with the row descriptions.

Configuration Overview

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

  • On the Column Sets (CS206020) form, the F350CS5 column set has been created.
  • On the Row Sets (CS206010) form, the F350RS5 row set has been created.
  • On the Report Definitions (CS206000) form, the F350RD5 report definition has been created with the F350CS5 column set and the F350RS5 row set specified.

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, review the rows that have been added to the row set.
  3. On the Column Sets (CS206020) form, modify the column set to display a column with the descriptions of the report 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 F350RD5 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 report is currently empty.
  2. Close the pop-up window. Lesson 4: Working with Report Columns | 45

Step 2: Reviewing the Row Set To review the row set, do the following:

  1. While you are still viewing the F350RD5 report definition 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 F350RS5 row set selected.
  2. Review the rows added to the row set. Notice the descriptions added to the Description column of the form. Also notice that the Suppress Empty check box is selected for some of the rows. (See the following screenshot.)
  3. Close the pop-up window.
    Figure: Row descriptions in the row set

Step 3: Modifying the Column Set To update the column set with a report column that will display row descriptions, do the following

  1. On the Report Definitions (CS206000) form, open the report definition with the F350RD5 code.
  2. In the Report Definition section, click the Edit button to the right of the Column Set box. The Column Sets (CS206020) form opens with the F350CS5 column set selected. Notice that one column, column A, has already been created in the report.
  3. In column A, set the Type attribute to Descr.
  4. 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 F350RD5 code. Lesson 4: Working with Report Columns | 46
  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 row descriptions are now displayed in the only column. The report does not display the descriptions of rows for which the Suppress Empty check box is selected in the row set.
    Figure: A column with row descriptions

Column Sets: To Add a Column with PTD Amounts

The following activity will walk you through the process of adding a column with period-to-date (PTD) amounts. The report will show this column in addition to an already-defined column with year-to-date (YTD) amounts.

Story

Suppose that you are a technical specialist at SweetLife Fruits & Jams who is responsible for building and supporting analytical reports. You are currently building a custom Profit & Loss report that should contain columns for both YTD amounts and PTD amounts. You have already defined the rows that you need to add to the report and added the column for YTD amounts. Now you need to add a new column for PTD values.

Configuration Overview

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

  • On the Column Sets (CS206020) form, the F350CS6 column set has been created.
  • On the Report Definitions (CS206000) form, the F350RD6 report definition has been created with the F350CS6 column set specified for it. Lesson 4: Working with Report Columns | 47

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 Column Sets (CS206020) form, modify the column set to add a column with the PTD values.
  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 F350RD6 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 form toolbar, click Run Report. The Profit & Loss report is opened in a pop-up window. Notice that the report displays only the column with row descriptions and the column with YTD values (as shown in the following screenshot). Lesson 4: Working with Report Columns | 48

Step 2: Adding a Column with PTD Values To update the column set with a report column that will display PTD values, do the following

  1. On the Report Definitions (CS206000) form, open the report definition with the F350RD6 code.
  2. In the Report Definition section, click the Edit button to the right of the Column Set box. The Column Sets (CS206020) form opens with the F350CS6 column set selected.
  3. On the table toolbar of the lower table, click New to add a column. The added column is the rightmost column of both the upper table and the lower table—that is, column C.
  4. In the lower table, do the following: a. Double-click the Data Source attribute in column C, and click the magnifier button to open the Data Source dialog box. b. In the dialog box, set Amount Type to Turnover. c. Click OK to close the dialog box.
  5. In the upper table, specify ='PTD' in the cell in the fourth row of column C.
  6. Copy the style of the column header of column B as follows: a. In the upper table, click the cell in the fourth row of column B. b. On the table toolbar, click Copy Style. Lesson 4: Working with Report Columns | 49
       c. Click the cell in the fourth row of column C.
   d. On the table toolbar, click Paste Style.
  7. On the form toolbar, click Save to save your changes to the column set.
    For the purposes of this activity, you do not need to make any other adjustments to the column set.

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 F350RD6 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 column with PTD values is now displayed in the report.
    Figure: The column with the PTD values Lesson 4: Working with Report Columns | 50

Column Sets: To Add a Column with a Calculated Value

In this activity, you will add a column with values that are calculated based on data from other columns.

Story

Suppose that you are a technical specialist at SweetLife Fruits & Jams who is responsible for building and supporting analytical reports. You are currently building a profit and loss report that compares the YTD and PTD values of the current year with the values from the previous year. You have already added the columns that show the necessary data for both years. Now you need to add two columns: one showing the change (in percentage) of the YTD values between the current year and the previous year, and the other showing the change (in percentage) of the PTD values between the current year and the previous year.

Configuration Overview

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

  • On the Column Sets (CS206020) form, the F350CS7 column set has been created.
  • On the Row Sets (CS206010) form, the F350RS7 row set has been created.
  • On the Report Definitions (CS206000) form, the F350RD7 report definition has been created with the F350CS7 column set and the F350RS7 row set specified in this report definition.

Process Overview

In this activity, you will do the following:

  1. On the Report Definitions (CS206000) form, review the Profit & Loss - Comparative report.
  2. On the Column Sets (CS206020) form, modify the column set to add two columns with the percentage values.
  3. On the Report Definitions form, review the updated Profit & Loss - Comparative report.

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

  1. On the Report Definitions (CS206000) form, open the report definition with the F350RD7 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:
  • Ledger: ACTUAL
  • Financial Period: 12-2023
  1. On the report form toolbar, click Run Report. The Profit & Loss - Comparative report opens in a pop-up window. Notice that the report displays four columns: YTD amounts for the current year (which is the year of the financial period selected in the report parameters), YTD amounts for the previous year, PTD amounts for the current year, and PTD amounts for the previous year. (See the following screenshot.)
  2. Close the pop-up window. Lesson 4: Working with Report Columns | 51
    Figure: The Profit and Loss - Comparative report before changes

Step 2: Adding a Column for the YTD Change Percentage To update the column set with a report column that will show the YTD change percentage over the previous year, do the following

  1. On the Report Definitions (CS206000) form, open the report definition with the F350RD7 code.
  2. In the Report Definition section, click the Edit button to the right of the Column Set box. The Column Sets (CS206020) form opens with the F350CS7 column set selected.
  3. On the table toolbar of the lower table, click New to add a column. The added column is the rightmost column of both the upper table and the lower table—that is, column F.
  4. In the upper table, click the cell in the first row of column F.
  5. On the table toolbar of the upper table, click Shi Le twice.
  6. In the lower table, click the cell in the first row of column F.
  7. On the table toolbar of the lower table, click Shi Le twice. The column that you added to the report is now column D in both the upper table and the lower table. Notice that you have shied the lower and upper parts of the column separately.
  8. Specify the following attributes for column D in the lower table: Lesson 4: Working with Report Columns | 52
  • Type: Calc
  • Cell Evaluation Order: Column
  • Value: =(B-C)/B
  • Format: #,##0%
  • Width: 60
  • Hide Zero: Selected With the format specifier you have entered in the Format attribute, the numerical value will be rounded to the nearest whole number and formatted as a percentage, with a comma used as the thousand separator.
                   You should use uppercase letters to specify columns in formulas, such as A, B, or C.
  1. Double-click the Style attribute of column D and then click the magnifier button to open the Style dialog box. 10.In the dialog box, in the Text Align box, select Right. 11.Click OK to close the dialog box. 12.On the form toolbar, click Save to save your changes to the column set.

Step 3: Adding a Column for the PTD Change Percentage To add a column for the YTD change percentage over the previous year, while you are still viewing the Column Sets (CS206020) form with the F350CS7 column set selected, do the following

  1. On the table toolbar of the lower table, click New to add a column. The added column is the rightmost column of both the upper table and the lower table—that is, column G.
  2. Update the attributes of column G in the lower table as follows:
  • Type: Calc
  • Cell Evaluation Order: Column
  • Value: =(E-F)/E
  • Format: #,##0%
  • Width: 60
  • Hide Zero: Selected
  1. Double-click the Style attribute of column G and then click the magnifier button to open the Style dialog box.
  2. In the dialog box, in the Text Align box, select Right.
  3. Click OK to close the dialog box.
  4. On the form toolbar, click Save to save your changes to the column set.

Step 4: Adding the Column Headers To add the headers to the new columns, while you are still viewing the Column Sets (CS206020) form with the F350CS7 column set selected, do the following:

  1. In the upper table, click the cell in the fih row of column D.
  2. Click the magnifier button to open the Formula Editor.
  3. In the lower pane of the Formula Editor, enter ='%'.
  4. Click OK to close the Formula Editor.
  5. In the upper table, double-click the cell in the fih row of column G and type ='%'. Lesson 4: Working with Report Columns | 53
       You have defined the column headers to display the % symbol.
  6. On the form toolbar, click Save.

Step 5: Adjusting the Style of the Column Headers To copy the style of the existing columns and apply it to the new columns, while you are still viewing the Column Sets (CS206020) form with the F350CS7 column set selected, do the following:

  1. In the upper table, click the cell in the fih row of column C.
  2. On the table toolbar, click Copy Style.
  3. Click the cell in the fih row of column D.
  4. On the table toolbar, click Paste Style.
  5. Click the cell in the fih row of column G.
  6. On the table toolbar, click Paste Style.
  7. Copy the style of the sixth row of column C to columns D and G.
  8. On the form toolbar, click Save.

Step 6: Reviewing the Updated Report To review the updated Profit & Loss - Comparative report, do the following:

  1. On the Report Definitions (CS206000) form, open the report definition with the F350RD7 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:
  • Ledger: ACTUAL
  • Financial Period: 12-2023
  1. On the report form toolbar, click Run Report. The Profit & Loss - Comparative report opens in a pop-up window. Notice that the report now displays two percentage columns. Lesson 4: Working with Report Columns | 54
    Figure: The updated Profit and Loss - Comparative report

Column Sets: To Add Budget Performance Columns

In this activity, you will build a report that compares budgeted and actual amounts.

Story

Suppose that you are a technical specialist at SweetLife Fruits & Jams who is responsible for building and supporting analytical reports. The company's management has requested a report that will compare the budget for the 2023 financial year with the actual amounts for this financial year. You have already added a column with the actual values to the column set. Now you need to add a column with the budgeted figures and a column for the budget performance percentage.

Configuration Overview

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

  • On the Column Sets (CS206020) form, the F350CS8 column set has been created.
  • On the Report Definitions (CS206000) form, the F350RD8 report definition has been created with the F350CS8 column set specified. Lesson 4: Working with Report Columns | 55
  • On the Budgets (GL302010) form, a budget for the 2023 year has been added to the BUDGET budget ledger. On the Release Budgets (GL505510) form, the uploaded budget has been released.

Process Overview

In this activity, you will do the following:

  1. On the Report Definitions (CS206000) form, review the P&L Actual vs Budget report, which you will use as a basis of the new report.
  2. On the Column Sets (CS206020) form, modify the column set to add a column with the budgeted figures and a column with the budget performance percentage.
  3. On the Report Definitions form, review the new report.

Step 1: Reviewing the Existing Report To review the existing P&L Actual vs Budget report, do the following:

  1. On the Report Definitions (CS206000) form, open the report definition with the F350RD8 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, set Financial Period to 12-2023.
  4. On the report form toolbar, click Run Report. The P&L Actual vs Budget report opens in a pop-up window. Notice that the report now contains only two columns, one with the row descriptions and the other with the actual amounts for the year 2023.
  5. Close the pop-up window. Lesson 4: Working with Report Columns | 56
    Figure: The P&L Actual vs Budget report before changes

Step 2: Adding a Column for the Budgeted Amounts To add to the column set a new column that will show the budgeted amounts for 2023, do the following:

  1. On the Report Definitions (CS206000) form, open the report definition with the F350RD8 code.
  2. In the Report Definition section, click the Edit button to the right of the Column Set box. The Column Sets (CS206020) form opens with the F350CS8 column set selected.
  3. On the table toolbar of the lower table, click New to add a column. The added column is the rightmost column of both the upper table and the lower table—that is, column C.
  4. In the upper table, double-click the cell in the fourth row of column C, and enter ='Budget'.
  5. In the lower table, specify the following attributes of column C:
  • Format: #,##0.00
  • Width: 120 With the format specifier you have entered in the Format attribute, the numerical value will be rounded to two decimal places and formatted with a comma used as the thousand separator and a period as the decimal separator. The format ensures that at least one digit is shown before the decimal point and exactly two digits are shown aer the decimal point.
  1. Double-click the cell of the Style attribute of column C, and then click the magnifier button.
  2. In the Style dialog box, which opens, set Text Align to Right. Lesson 4: Working with Report Columns | 57
  3. Click OK to close the Style dialog box.
  4. Double-click the cell of the Data Source attribute of column C, and then click the magnifier button. 10.In the Data Source dialog box, specify the following settings:
  • Company: SWEETLIFE
  • Ledger: BUDGET
  • Start Branch: HEADOFFICE
  • Amount Type: Ending Balance These settings are similar to those of the Actual column, except that a budget ledger is specified instead of the actual ledger. 11.Click OK to close the Data Source dialog box. 12.On the form toolbar, click Save.

Step 3: Adding a Column for the Budget Performance Percentage To calculate the percentage as a comparison of the values of the actual and budgeted YTD amounts for each report row, while you are still viewing the Column Sets (CS206020) form with the F350CS8 column set selected, do the following:

  1. On the table toolbar of the lower table, click New to add a column. The added column is column D.
  2. In the upper table, in the cell in the fourth row of column D, type ='% Budget'
  3. In column D of the lower table, specify the following attributes:
  • Type: Calc
  • Cell Evaluation Order: Column
  • Value: =B/C
  • Format: #,##0.00%
  • Width: 120
  1. On the form toolbar, click Save.

Step 4: Formatting the Column Headers Now you will improve the display of the headers of the new columns. While you are still viewing the Column Sets (CS206020) form with the F350CS8 column set selected, do the following in the upper table:

  1. Click the cell in the fourth row of column B, and click Copy Style on the table toolbar.
  2. Click the cell in the fourth row of column C, and click Paste Style on the table toolbar.
  3. Click the cell in the fourth row of column D, and click Paste Style on the table toolbar.
  4. Click the cell in the fih row of column B, and click Copy Style on the table toolbar.
  5. Click the cell in the fih row of column C, and click Paste Style on the table toolbar.
  6. Click the cell in the fih row of column D, and click Paste Style on the table toolbar.
  7. On the form toolbar, click Save to save your changes.

Step 5: Reviewing the Updated Report To review the updated P&L Actual vs Budget report, do the following:

  1. On the Report Definitions (CS206000) form, open the report definition with the F350RD8 code.
  2. On the form toolbar, click Preview. The report form opens in a pop-up window. Lesson 4: Working with Report Columns | 58
  3. On the Report Parameters tab of the report form, set Financial Period to 12-2023.
  4. On the report form toolbar, click Run Report. The P&L Actual vs Budget report is opened in a pop-up window. Notice that the report now shows additional columns with the budgeted data and the budget performance percentage.
    Figure: The updated P&L Actual vs Budget report

Column Sets: To Filter Data by Financial Periods

The following activity will walk you through the adding of columns with data pulled from specific financial periods.

Story

Suppose that you are a technical specialist at SweetLife Fruits & Jams who is responsible for building and supporting analytical reports. The management of the company requested a report that shows profits and losses from the first period of the selected financial year to the last period of the selected financial year, grouped by quarters. In SweetLife's system, financial periods have been configured to match the months of a year. You need to modify the column set of an existing Profit & Loss report to show the account balances at the end of the previous financial year. You will also add four columns to display the turnover of each quarter of the selected financial year, and add a column to show the account balances at the end of the selected financial year. Lesson 4: Working with Report Columns | 59

Configuration Overview

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

  • On the Column Sets (CS206020) form, the F350CS10 column set has been created.
  • On the Report Definitions (CS206000) form, the F350RD10 report definition has been created with the F350CS10 column set.

Process Overview

In this activity, you will do the following:

  1. On the Report Definitions (CS206000) form, review the existing version of the report.
  2. On the Column Sets (CS206020) form, change the YTD column to show the account balances at the end of the previous financial year. Then you will change the PTD column, and add three more columns to show the turnover of each quarter of the selected financial year. Finally, you will add a column to show the account balances at the end of the selected financial year.
  3. On the Report Definitions form, review the updated version of the report.

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

  1. On the Report Definitions (CS206000) form, open the report definition with the F350RD10 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 (shown in the following screenshot). Notice that, apart from the column with row descriptions, the report displays only the YTD and PTD columns.
  2. Close the pop-up window. Lesson 4: Working with Report Columns | 60
    Figure: The existing Profit & Loss report

Step 2: Changing the YTD Column First, you will change the values of the YTD column to the ending balance of the last financial period of the previous financial year. Do the following:

  1. On the Column Sets (CS206020) form, open the column set with the F350CS10 code.
  2. In the lower table, double-click the cell in the row with the Data Source attribute and column B, and click the magnifier button to open the Data Source dialog box.
  3. In the dialog box, specify the following settings (see the following screenshot):
  • Start Period: 12
  • Offset (Year, Period): -1 (year offset)
  • End Period: 12
  • Offset (Year, Period): -1 (year offset)
  • Amount type: Ending Balance The following screenshot shows the data filtering criteria specified in the dialog box. Lesson 4: Working with Report Columns | 61
        Figure: Data Source dialog box with the data filtering criteria

    The identifier of each financial period is a combination of the period number and the financial year. For
    example, the identifier of the first period of 2024 is 01-2024. You have used the mask 12 to get the last period
    of the selected financial year. You have also specified -1 as the offset of the financial periods by a year to
    subtract this number from the year of the period.
    Thus, the data that meets these filtering criteria is the account balances of the last financial period of the
    previous financial year relative to the selected financial period.
  1. Click OK to save your changes and close the dialog box.
  2. On the form toolbar, click Save.

Step 3: Showing Turnover for the First Quarter To show the turnover amounts of the first quarter of the current financial year in the PTD column, while remaining on the Column Sets (CS206020) form with the F350CS10 column set selected, do the following:

  1. In the lower table, double-click the Data Source cell of column C, and click the magnifier button to open the Data Source dialog box.
  2. In the dialog box, specify the following settings:
  • Start Period: 01
  • End Period: 03
  • Amount Type: Turnover
  1. Click OK to save your changes and close the dialog box.
  2. On the form toolbar, click Save.

Step 4: Showing Turnover for the Second Quarter To add turnover for the second quarter, while you are still on the Column Sets (CS206020) form with the F350CS10 column set selected, do the following:

  1. In the lower table, click New to add a column.
  2. In the added column, click the magnifier button in the row with the Data Source attribute.
  3. In the Data Source dialog box, specify the following settings:
  • Start Period: 04
  • End Period: 06
  • Amount Type: Turnover
  1. Click OK to save your changes and close the dialog box.
  2. On the form toolbar, click Save. Lesson 4: Working with Report Columns | 62

Step 5: Showing Turnover for the Third Quarter To add turnover for the third quarter, while you are still on the Column Sets (CS206020) form with the F350CS10 column set selected, do the following:

  1. In the lower table, click New to add a column.
  2. In the added column, click the magnifier button in the row with the Data Source attribute.
  3. In the Data Source dialog box, specify the following settings:
  • Start Period: 07
  • End Period: 09
  • Amount Type: Turnover
  1. Click OK to save your changes and close the dialog box.
  2. On the form toolbar, click Save.

Step 6: Showing Turnover for the Fourth Quarter To show the turnover amounts of the fourth quarter of the current financial year, while you are still on the Column Sets (CS206020) form with the F350CS10 column set selected, do the following:

  1. In the lower table, click New to add a column.
  2. In the added column, click the magnifier button in the row with the Data Source attribute.
  3. In the Data Source dialog box, specify the following settings:
  • Start Period: 10
  • End Period: 12
  • Amount Type: Turnover
  1. Click OK to save your changes and close the dialog box.
  2. On the form toolbar, click Save.
           When you specify an explicit range of periods in the data source and specify the end period number,
       you should consider the structure of your financial year and make sure that you have specified the
       greatest possible end number of a period. For example, if you had an adjustment period that was
       period 13 in the financial year, you would have to specify 13 in the End Period box of the Data Source
       dialog box to make the report cover all financial periods.

Step 7: Adding Ending Balances at the End of the Selected Year To display the account balances at the end of the selected financial year, while you are still on the Column Sets (CS206020) form with the F350CS10 column set selected, do the following:

  1. In the lower table, click New to add a column.
  2. In the added column, click the magnifier button in the row with the Data Source attribute.
  3. In the Data Source dialog box, specify the following settings:
  • Start Period: 12
  • End Period: 12
  • Amount Type: Ending Balance
  1. Click OK to save your changes and close the dialog box. Lesson 4: Working with Report Columns | 63
  2. On the form toolbar, click Save.

Step 8: Modifying the Report and Column Headers To modify the report header and add the missing column headers, while you are still viewing the Column Sets (CS206020) form with the F350CS10 code selected, do the following:

  1. In the second row of the upper table, change the value in column A to ='Profit & Loss Quarterly'.
  2. In the cell in the fourth row of column B, enter ='FY-'+Report.FormatYear(@StartPeriod, -1). The FormatYear(period, shift) function retrieves the year of the specified period and shis the obtained year to the value of the shift attribute of the function. For example, if a user selects any financial period of the year 2024 as the start period, the expression you have specified returns 2023.
  3. In the fourth row, enter the following values in the listed columns:
  • C: ='Q1-'+Report.FormatYear(@StartPeriod)
  • D: ='Q2-'+Report.FormatYear(@StartPeriod)
  • E: ='Q3-'+Report.FormatYear(@StartPeriod)
  • F: ='Q4-'+Report.FormatYear(@StartPeriod)
  • G: ='FY-'+Report.FormatYear(@StartPeriod)
  1. On the form toolbar, click Save.

Step 9: Adjusting the Formatting of the Column Headers To adjust the formatting of the column headers, while you are still viewing the Column Sets (CS206020) form with the F350CS10 code selected, do the following in the upper table:

  1. In the fourth row, click the cell in column C. Click Copy Style on the table toolbar.
  2. Paste the style in columns D, E, F, and G of the row as follows: a. Click the cell in the fourth row of the column. b. On the table toolbar, click Paste Style.
  3. In the fih row, click the cell in column C. Click Copy Style on the table toolbar.
  4. Paste the style in columns D, E, F, and G of the row as follows: a. Click the cell in the fih row of each column. b. On the table toolbar, click Paste Style.
  5. On the form toolbar, click Save.

Step 10: Adjusting the Formatting of Columns In this step, you will specify the attributes to adjust the formatting of the columns. While you are still viewing the Column Sets (CS206020) form with the F350CS10 code selected, do the following in the lower table:

  1. Set the Width attribute of column A to 350.
  2. Set the Width attributes of columns B, C, D, E, F, and G to 100.
  3. Set the Format attributes of columns D, E, F, and G to #,##0.00.
  4. Select any cell of column C, and then click Copy Style on the table toolbar.
  5. Paste the style to columns D, E, F, and G as follows: a. Select any cell of the column. Lesson 4: Working with Report Columns | 64
       b. On the table toolbar, click Paste Style.
  6. On the form toolbar, click Save.

Step 11: Reviewing the Updated Report To review the updated Profit & Loss Quarterly report, do the following:

  1. On the Report Definitions (CS206000) form, open the report definition with the F350RD10 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 Quarterly report is opened in a pop-up window (shown in the following screenshot).
    Figure: The new Profit & Loss Quarterly report Lesson 5: Using Overlapping Data Sources | 65