Articles in this section

7. Configure aggregated queries and "Level" parameters (optionally)

data1.io Team
  • Updated
Follow

The "Level"-Parameter optionally can be defined in order to realize reports with aggregated queries on dimension attributes instead of queries on the dimension keys.

The most important

The following optional parameters can be defined to execute aggregated queries:

  • Queries on the ALL-Level
    Keyword: use the % Asterisk

  • Queries on a dimension attribute using
    Level parameter: data1_CELLS01_DIMxxx_Level
    Keywords: element names of the defined level
    Please note: this function is currently limited to the first 5 level attributes of a dimension

  • Queries on a virtual date level
    Level parameter: data1_CELLS01_DATExx_Level
    Keywords: YEAR, QUARTER, MONTH, DATE

Please note

  • The existence of a Level-Parameter makes a sheet "read-only" which means that the Write-button will not be activated any more in this sheet.
  • Named ranges in data1.io Templates always must be defined with reference to the sheet (and not to workbook).

 

How to execute aggregated queries

Queries on aggregated levels can be achieved in 3 ways:

  1. Queries on the ALL-Level:
    use the "%" as asterisk keyword
  2. Queries on a dimension attribute (see chapter "level parameters" below for more details)
  3. Queries on a virtual level of date field (see chapter "level parameters" below for more details)

All together (with some german words)

mceclip0.png

 

How to use the "Level" parameters

1. Queries on a dimension attribute

In the data entry sheet of a data1.io Template File we always enter data on the bottom level of each dimension (the elements on this level we call the “keys”). Totals and Subtotals can simply be created with Excel formulas:

But in a report sheet it is often more effective to show aggregations instead of every single value on the key level. So a report for the “HR Contracts” could show only “Total Salaries” and “Total Contributions” as aggregations of the bottom level metrics:

But how this can be achived without querying the single metrics in the background? At first we need to define so-called attributes in the data model. We will use the definitions in the “Hierarchy Level 2” for the aggregation queries in the next step:

In the report sheet of the template file an additonal named range must be defined:

  • data1_CELLS01_DIMxxx_Level

This cell is filled with the technical column name of the attribute (“DIM004.A002”) and then it is possible to use the entries in this attribute column as query parameter for the row:

You`ll find the technical name of the attribute column in the “Field Properties”:

Please note that this feature can be used only with an Enterprise Plan since the definitions in the data model can only be set with the EP. Please note that this feature is at the moment restricted to the first 5 attributes of a dimension.

 

2. Queries on virtual levels of a date field

The same aggregation feature is now available for date fields:

It is very easy to use. Simply set the named range

  • data1_CELLS01_DATExx_Level

and set one of the 4 available default aggregation keywords:

  • YEAR
  • QUARTER
  • MONTH
  • DATE

Please note that in the DATExx panel there must be entries with date format (“31.12.2020”) and not only the year (“2020”). For a YEAR aggregation you can use any date in this year, for a QUARTER aggregation any date within this quarter and for a MONTH aggregation any date within this month:

This feature can be used also with the Free Plan since there are no definitions needed in the data model.

Related articles