Dynamic tables enable you to query a dimension table dynamically into your workbook. It is mainly used to query master data for bottom elements and to build dynamic element list reports.
The most important
Named ranges in this section (overview):
- data1_CELLS01_DIMxxx_Key_Path
- data1_CELLS01_InsertTable_T_DIMxxx_Filter
- data1_CELLS01_InsertTable_T_DIMxxx
- data1_CELLS01_InsertTable_ForceRLS
How it works
On a new sheet in your workbook is a named cell "data1_CELLS01_InsertTable_T_DIM001" and 3 other parameters (which will be explained further down):
Then upload this XLSX file as a workbook for your application, start the Preview and click on the top level element in the tree:
Now click on another node in tree, you will get a filtered table:
How to configure
data1_CELLS01_DIMxxx_Key_Path
This named range is filled with every click in the dimension with the full “SQL Where” path which can be used directly as filter criteria for the dynamic filter table.
We strongly recommend to use this parameter instead of combining "data1_CELLS01_DIM001_Attribute" with "data1_CELLS01_DIM001" since this combination would not be robust against same name nodes in the dimension tree.
data1_CELLS01_InsertTable_T_DIMxxx_Filter
This is the filter for the dynamic table. In many cases this parameter is equated with data1_CELLS01_DIMxxx_Key_Path, but it is also possible to build more advanced filter criterias for your dynamic table.
data1_CELLS01_InsertTable_T_DIMxxx
This named range cell is the position in the sheet where the dynamic table is generated. Alternatively, you can create and configure the table object manually (learn more).
data1_CELLS01_InsertTable_ForceRLS
This parameter can be set to
- 0 = no RLS is applied (default) or
- 1 = RLS is applied to the filter table
We strongly recommend to protect this cell so that your collector users will not able to change the parameter and maybe could access data they are not allowed to see.