Lesson 5: Using Overlapping Data Sources

Data Filtering: Data Sources That Overlap

By specifying the data filtering parameters on the row level, you define the data structure in the report. For example, if a group of lines in the report must display the calculated values for a specific period range, you should specify the start period and end period for each row included in this group of report lines. If some lines in the report must display the data related to a specific account, and the columns must display the values calculated for certain periods, you should specify the account on the row level, and specify the start period and end period of each period range on the column level. To retrieve the amounts posted to general ledger accounts and subaccounts to a report, you specify the conditions in the data source of the report definition, row, column, or unit. If these data sources overlap, the system retrieves the data by using the following rules:

Accounts, subaccounts, branches, and their ranges of all data sources are merged. The system retrieves the intersecting data that results from this merge. For example, if you have specified the 100 through 600 range of accounts for a row and the 400 through 900 range of accounts for a column, and you run a report that uses the row set and the column set with these settings, the system will retrieve the data for the 400 through 600 range of accounts. The image below illustrates this overlap.
```
    Figure: The overlap of accounts
```
All of the following are used from only one data source: the ledger, the account class, the financial periods, the oﬀsets of financial periods, and the amount type. If conditions overlap between the unit, row, column, and report definition, the system uses the condition specified for the unit (if any). If no condition is specified for the unit or there is no unit set in the report, the system uses the condition of the row. If no condition is specified for the row, the system uses the condition of the column. If no condition is specified in the unit, row, or column of the report, the condition from the report definition (if any) is used. Thus, the data sources of an analytical report have the following priority level, from the highest to the lowest: a. The unit b. The row c. The column d. The report definition The table below illustrates an example of how these rules work.
```
     Data Source Para-      The Unit               The Row               The Column             The Report Defin-
     meters                                                                                     ition

     Ledger                 ACTUAL                 BUDGET

     Account Class                                 EXOFFICE              EXBANK
```

Lesson 5: Using Overlapping Data Sources | 66

        Data Source Para-       The Unit            The Row               The Column            The Report Defin-
        meters                                                                                  ition

        Account Type                                                      Ending Balance        Turnover

        Start Period                                                      04-2023               01-2023

        End Period                                  09-2023                                     12-2023

       Based on the sample data provided above, the system uses the following consolidated conditions in the
       report:

Ledger: ACTUAL (from the unit)
Account Class: EXOFFICE (from the row)
Account Type: Ending Balance (from the column)
Start Period: 04-2023 (from the column)
End Period: 09-2023 (from the row)

Data Filtering: To Display Data from Diﬀerent Ledgers in the Same Column

The following activity will walk you through the creation of a column that displays data from diﬀerent ledgers.

Story

Suppose that you are a technical specialist at SweetLife Fruits & Jams who is responsible for supporting analytical reports. The company's financial manager has requested that you prepare a modified version of the Profit & Loss report that will display the net income per employee ratio. The employee headcount data is stored in a statistical ledger, HEADCOUNT, while the account amounts in other rows of the report are pulled from the ACTUAL ledger. You need to modify the existing Profit & Loss report to add a row with the employee headcount and a row with the net income per employee ratio.

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 F350RS15 row set has been created.
On the Column Sets (CS206020) form, the F350CS15 column set has been created.
On the Report Definitions (CS206000) form, the F350RD15 report definition has been created with the F350RS15 row set and the F350CS15 column set specified.
On the Journal Transactions (GL301000) form, the number of SweetLife employees at the end of December 2023 has been posted to the 69500 account of the HEADCOUNT ledger.

Process Overview

In this activity, you will do the following:

On the Report Definitions (CS206000) form, review the Profit & Loss report.
On the Row Sets (CS206010) form, update the row set to add two rows.
On the Report Definitions form, review the modified Profit & Loss report. Lesson 5: Using Overlapping Data Sources | 67

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

On the Report Definitions (CS206000) form, open the report definition with the F350RD15 code. In the Default Data Source Settings section, notice that the Request check box to the right of the Ledger box is selected.
On the form toolbar, click Preview. The report form opens in a pop-up window.
On the Report Parameters tab of the report form, specify the following report parameters:

Company: SWEETLIFE
Ledger: ACTUAL
Financial Period: 12-2023

On the report form toolbar, click Run Report. The Profit & Loss report opens in a pop-up window. Review the rows of the report.
Close the pop-up window.
Figure: The Profit & Loss report before changes Lesson 5: Using Overlapping Data Sources | 68

Step 2: Adding a Row with the Employee Headcount To add a row with the employee headcount to the row set, do the following:

On the Row Sets (CS206010) form, open the row set with the F350RS15 code.
On the table toolbar, click New to add a new row.
In the added row, specify the following settings:

Code: 0490
Description: Employee Headcount
Type: GL

On the form toolbar, click Save.
In the row with the 0490 code, click the Data Source cell, and then click the magnifier button.
In the Data Source dialog box, which opens, specify the following settings:

Ledger: HEADCOUNT
Start Account: 69500
Amount Type: Ending Balance You have specified 69500 as the start account because the number of employees has been posted to this account in the HEADCOUNT ledger.

Click OK to close the dialog box.
In the Format column of row 0490, specify d. This format specifier converts the value to a string of decimal digits.
On the form toolbar, click Save.

Step 3: Adding a Row with the Net Income per Employee Ratio To add a row that shows the net income per employee ratio, while you are still on the Row Sets (CS206010) form with the F350RS15 row set selected, do the following

On the table toolbar, click New to add a new row.
In the added row, specify the following settings:

Code: 0500
Description: Net Income per Employee
Type: Total

Value: =@0480/@0490

                  In formulas, you must precede a row code with the @ character. The letters of row codes (if
                  any) should always be uppercase.

Click OK to close the dialog box.
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:

On the Report Definitions (CS206000) form, open the report definition with the F350RD15 code. Lesson 5: Using Overlapping Data Sources | 69
On the form toolbar, click Preview. The report form opens in a pop-up window.
On the Report Parameters tab of the report form, specify the following report parameters:

Company: SWEETLIFE
Ledger: ACTUAL
Financial Period: 12-2023

On the report form toolbar, click Run Report. The Profit & Loss report is opened in a pop-up window. Notice that the report now displays two additional rows, Employee Headcount and Net Income per Employee. Although you have specified the ACTUAL ledger as the report parameter before running the report, the Employee Headcount row displays data from the HEADCOUNT ledger because the data source specified for a particular row has a higher priority than the data source specified for the columns or for the report definition.

Figure: The Profit & Loss report with the added rows Lesson 6: Working with Unit Sets | 70