Lesson 2: Filtering Data

Data Filtering: General Information

Data filtering criteria specify how the data will be selected from the database to be shown in a report. The data filtering criteria defined for the whole report or for the individual rows and columns, use common parameters that identify the subsets of data to be displayed in the report. These parameters can be specified in the Data Source Editor dialog box.

Learning Objectives

In this lesson, you will learn how to pull and filter data to be shown in a report by using the following criteria (further described in the following sections of this topic):

Group of accounts and subaccounts
Range of periods
Type of amount
Company or branch

Applicable Scenarios

You define filtering criteria for the whole report, for a unit, or for any specific row or column in the report. Usually, the criteria defined for the rows and columns reflect the report structure—that is, how the report data will be grouped in rows and columns.

Data Sources and Filtering Data

The data source defines and applies the data filtering criteria to select the data that will be used in the report. You can include the selected data in the report in the following ways:

For each row of a row set, by specifying data filtering criteria in the Data Source Editor dialog box. You invoke this dialog box by clicking the magnifier button in the Data Source column on the Row Sets (CS206010) form, which is shown in the following screenshot.
For each column of a column set, by specifying data filtering criteria for the report columns in the Data Source Editor dialog box. You invoke this dialog box by clicking the magnifier button in the Data Source cell for the column, in the lower table of the Column Sets (CS206020) form.
For each unit that includes rows and columns, by specifying data filtering criteria in the Data Source Editor dialog box. You invoke this dialog box by clicking the magnifier button in the Data Source column (in the table of the Units area) on the Unit Sets (CS206030) form.
For the data source defined for the entire report, by specifying additional data filtering criteria to be applied to the report rows and columns. You do this by specifying the data source parameters on the Report Definitions (CS206000) form. Lesson 2: Filtering Data | 21
Figure: The Data Source dialog box of the Row Sets form
The filtering rules defined for the unit have the highest priority. The rules defined on the row, column, and report levels have the next highest priorities (in that order). For example, if you want to define filtering criteria for a column and these criteria diﬀer from the criteria that has already been specified for the rows, you need to clear the parameters or specify the Amount Type as Not Set for the Data Source of the row that interacts with the corresponding column so that the data filtering criteria specified for the column will be applied. 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.

Filtering by a Group of Accounts or Subaccounts

To eliminate the data that is not related to a specific account and subaccount, or define the range of accounts and subaccounts to be used for data filtering criteria in the report, you should define the account and subaccount ranges. To specify a predefined group of accounts and subaccounts to be included in the report, you can select an Account Class. Filtering the data by using the account class is convenient, but you can filter the data in other ways. You can specify the range of the accounts by using the settings in the dialog box in one of the following ways:

Specify the range of accounts by the start account and the end account—that is, select 740000 as the Start Account and 758000 as the End Account. This range includes all the accounts between 740000 and 758000, and returns the following accounts: 740000, 745000, 755000, and 758000.
List all the accounts explicitly in the Start Account box separated by a comma as follows: 740000,745000,755000,758000.
Specify the range of accounts by using the colon in a mask in the Start Account box, such as 740000:758000. The range 740000:758000 includes all the accounts between 740000 and 758000. This range returns the following accounts: 740000, 745000, 755000, 758000.

Use the ? expanded wildcard character in a mask in the Start Account box as follows: 74?000,75?000. Lesson 2: Filtering Data | 22

    The ? wildcard character gives you the ability to match all possible options with any single character
    in the specific position where the ? character is. For example, the range 74?000,75?000 includes all the
    accounts starting with 74 and ending with 000 with a single character between them, and all the accounts
    starting with 75 and ending with 000 with a single character between them. This range returns the following
    accounts: 740000, 745000, 755000, and 758000.
    The range 7??000 includes all accounts starting with 7 and ending with 000 with two characters between
    them. This range returns the following accounts: 740000, 745000, 755000, 758000, 760000, 770000, and

You can combine the ,, :, and ? characters to create complicated masks. You can also use a mask for only subaccounts with the Unicode edit mask. The other edit masks do not allow using special characters.

You can specify values in the Account Class, Start Account, and End Account boxes at the same time. However, if the specified accounts have no common data, null values will be displayed in the report rows, columns, or both.

          You can use denominated accounts for retrieving amounts in a foreign currency.

Filtering by Period Range

To define specific periods for the data to be included in the report, you specify Start Period and End Period values. The data related to the periods not included in the selected range of periods will be filtered out and not included in the report.

Filtering by Amount Type

To define the type of amounts that should be used in the report lines (displayed or used in calculations), you should select the appropriate Amount Type option in the Data Source Editor dialog box or on the Default Data Source Settings section of the Report Definitions (CS206000) form. The following types of amounts can be selected: Turnover, Credit, Debit, Beg. Balance, Ending Balance. These types of amounts can be specified for use in the whole analytical report or for a separate row or column. You can also use the amounts in a foreign currency retrieved from the denominated accounts by specifying one of the following options: Curr. Turnover, Curr. Credit, Curr. Debit, Curr. Beg. Balance, or Curr. Ending Balance. Lesson 3: Working with Report Rows | 23