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

Named Ranges in this section (overview):

• 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
  
  
• 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 with this sheet.
• Named ranges in data1.io Templates always must be defined with reference to the sheet (and not to workbook).

1. Queries on dimension attributes

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. Virtual attributes on date fields

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.