Lesson 6

Working with Unit Sets

Back to F350: Analytical Reports

Lesson 6: Working with Unit Sets

Unit Sets: General Information

A unit set defines the units that the user can use directly on the report to quickly filter or consolidate the report
data. You create a unit set on the Unit Sets (CS206030) form.

Learning Objectives

In this lesson, you will learn how to do the following:
  • Create a unit set
  • Define units within a unit set
  • Apply a unit set to a report

Applicable Scenarios

Unit sets might be useful if you need to develop reports for an organization with a hierarchical structure and would
like an ability to quickly filter or consolidate data in the report for a particular organizational unit (such as a branch,
a company, or a group of companies).

The Code and Description of a Unit Set

In the Summary area of the Unit Sets (CS206030) form, you specify the code, description, and type of the data
source for the unit 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 unit set help you to uniquely
identify the unit set in the system and to add it to the report definition.

Properties of a Unit

On the Unit Sets (CS206030) form and in each analytical report that uses the unit set, the units are organized in a
hierarchical structure and ordered by unit code, which is illustrated by the following screenshot.

Lesson 6: Working with Unit Sets | 71

Figure: A unit set configured on the Unit Sets form

This hierarchical structure is reflected in the le pane of the Unit Sets form (Item 1 in the screenshot above). You can select a unit in the le pane, and add child units to it in the right pane (Item 2). In the corresponding report, the descriptions of the units will be shown. The topmost unit is applied to the report by default when you run the report. You can select any unit to be applied to the report by default by selecting this unit in the Start Unit box on the Report Definition (CS206000) form.

          Although you can have an unlimited number of units in a unit set, keep in mind that having too many
          units can significantly slow the performance of the report, because the system generates the report
          corresponding to every single unit defined for the report when you run the report.

The Code and Description of a Unit

When you add a unit to the unit set on the Unit Sets (CS206030) form, you specify a code for it in the Code column. The unit code is used to identify the unit within the unit set. References in the formulas defined for the unit can also include the unit code. The units in the unit sets are sorted by the unit code in ascending order, and this is the order they appear in the hierarchical structure on the Unit Sets form. The value of the Code column can be modified manually.

          We do not recommend using successive values as the codes of new units added to the unit set so that
          it is easier to add new units between the existing ones.

The Value of a Unit

For every unit, you need to specify either the formula (in the Value column) or the Data Source to define the data processing rules for the report. By specifying the Value attribute of a unit, you can define a certain calculated value to be displayed in the report. The values in the units 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. The formulas used to calculate the data in the units usually include references to other units. The expressions defined for the units can also use parameter queries to select the values from the data source and insert them in the column cells. The values defined for the units in the analytical reports usually specify the report structure and follow up the user defined unit's hierarchical structure. The units from the top hierarchical level are usually based on the calculated Lesson 6: Working with Unit Sets | 72

values from the units on the lower hierarchical levels. The higher hierarchical levels can include the aggregated values based on the units from the lower hierarchical levels.

The Printing Group of a Unit

You can display particular columns and rows in some units and hide those columns and rows for other units. To do this, you specify a custom code in the Printing Group column for a unit. This code, consisting of only numbers and letters, and can be up to 10 characters in length. Then you can specify this code as the Unit Group of the rows on the Row Sets (CS206010) form and as the Unit Group of the columns on the Column Sets (CS206020) form. These rows and columns will be hidden for other units.

Unit Sets: To Filter Data with Unit Sets

In this activity, you will create a unit set and apply it to a report to be able to filter data in the executed report.

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 has requested a profit and loss report that shows data for the entire company and for each of these branches. You need to create a unit set to reflect the company's structure and apply it to the existing Profit & Loss report.

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 F350CS9 column set has been created.
  • On the Report Definitions (CS206000) form, the F350RD9 report definition has been created with F350CS9 selected as the column set.

Process Overview

In this activity, you will do the following:

  1. On the Unit Sets (CS206030) form, you will create a unit set with four units, one for the SweetLife company, and one for each of the company's branches.
  2. On the Report Definitions (CS206000) form, you will add the unit set to the report definition. You will also hide the Ledger parameter on the report form, because the ledgers used will be determined by the settings of the units.

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 F350RD9 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. Lesson 6: Working with Unit Sets | 73
        The Profit & Loss report is opened in a pop-up window (as shown in the following screenshot).
  2. Close the pop-up window.
    Figure: The existing Profit & Loss report

Step 2: Creating the Unit Set To create the unit set, do the following:

  1. On the Unit Sets (CS206030) form, add a new record.
  2. In the Summary area, specify the following settings:
  • Code: MYPL
  • Description: SweetLife Branches
  1. In the le pane, click the ROOT node of the unit set, which is the topmost system node and is not shown in the report.
  2. In the right pane, click Add Row on the table toolbar and specify the following settings in the added row:
  • Code: U1
  • Description: SweetLife - All Branches
  1. Save your changes to the unit set. A node for the U1 unit appears in the le pane.
  2. In the le pane, click the U1 node. Lesson 6: Working with Unit Sets | 74
  3. In the table in the right pane, add three rows with the settings shown in the following table.
                   The Data Source dialog box opens when you click the cell in the Data Source column and then
               click the magnifier button.


      Code          Description                        The Ledger box in the Data         The Start Branch box in
                                                       Source dialog box                  the Data Source dialog
                                                                                          box

      U2            Head Office and                    ACTUAL                             HEADOFFICE
                    Wholesale Center

      U3            Retail Store                       ACTUAL                             RETAIL

      U4            Service and Equipment              ACTUAL                             SWEETEQUIP
                    Center
  4. On the form toolbar, click Save. The created unit set should look like the one shown in the following screenshot.
    Figure: The created unit set

Step 3: Defining the First Unit of the Unit Set To define the first unit, while you are still viewing the MYPL unit set on the Unit Sets (CS206030) form, do the following:

  1. In the le pane, click the ROOT node.
  2. In the table of the right pane, specify the following expression in the Value column in the row for unit U1: =@U2+@U3+@U4 The expression =@U2+@U3+@U4 consolidates the data for all branches.
                   In formulas, you must precede a unit code with the @ character, as you have done in the
               =@U2+@U3+@4 expression. The letters of codes are always uppercase, so you must use the
               appropriate letter case in formulas to avoid the system returning an exception.
               Alternatively, you can specify the Ledger and Company in the Data Source attribute of this
               unit. This way, you will be able to use the drill-down functionality when displaying the report
               for this unit.

Lesson 6: Working with Unit Sets | 75 3. On the form toolbar, click Save.

Step 4: Applying the Unit Set and Changing the Report Form To apply the unit set to the report and modify the report parameters, do the following:

  1. On the Report Definitions (CS206000) form, select the definition with the F350RD9 code.
  2. In the Unit Set box, select MYPL to apply the unit set.
  3. Modify the default report parameters as follows: a. Clear the Company box and the Request check box to the right of this box. b. Clear the Ledger box and the Request check box to the right of this box.
  4. On the form toolbar, click Save.

Step 5: Modifying the Report Header To modify the report name to display the name of the unit for which data is displayed, do the following:

  1. On the Column Sets (CS206020) form, open the column set with the F350CS9 code.
  2. In the second row of the upper table, change the value in column A to ='P&L: '+@UnitText. The @UnitText parameter returns the unit description specified on the Unit Sets (CS206030) form. Based on the expression you have specified, the report header will be changed when you select a unit.
  3. On the form toolbar, click Save.

Step 6: Reviewing the Updated Report To run the updated Profit & Loss report and review your changes, do the following:

  1. On the Report Definitions (CS206000), open the report definition with the F350RD9 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 Profit & Loss report opens in a pop-up window.
  5. On the report toolbar, click the Groups button to open the le pane with the report units (see the following screenshot). Notice that the SweetLife - All Branches unit is selected by default. Because you have specified a formula that combines amounts from all branches as the value of this unit, you cannot drill down to the account details. Lesson 6: Working with Unit Sets | 76
    Figure: The Profit & Loss report showing data for the whole company
  6. In the le pane, click Head Office and Wholesale Center, which corresponds to the U2 unit, as shown in the following screenshot, and review the report.
    Figure: The Profit & Loss report showing data for a single branch Lesson 7: Using Formulas in a Report | 77