Articles in this section

Integrate a dynamic table into your workbook

data1.io Team
  • Updated
Follow

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):

mceclip1.png

Then upload this XLSX file as a workbook for your application, start the Preview and click on the top level element in the tree:

mceclip4.png

Now click on another node in tree, you will get a filtered table:

mceclip5.png

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.

Related articles