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 and use it e.g. for dropdown selections.

The most important

Named ranges in this section (overview):

  • data1_CELLS01_InsertTable_T_DIMxxx
  • data1_CELLS01_InsertTable_T_DIMxxx_Filter
  • data1_CELLS01_DIMxxx_Key_Path
  • data1_CELLS01_InsertTable_ForceRLS
  • data1_CELLS01_InsertTable_ReadOnLoad

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 into your application, start the Preview and click on the top level element in the tree:

mceclip4.png

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

mceclip5.png

How to configure

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_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_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_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.

 

data1_CELLS01_InsertTable_ReadOnLoad

This parameter can be set to

  • 0 = default ... the dynamic table is refreshed (a) if the sheet with the table is activated or (b) if a "READ Sync" is defined for the sheet with the table
  • 1 = execute an initial READ on the dynamic table when loading the workbook even if the sheet is not activated or even if the sheet is hidden; be aware that the default behaviour is also active with this parameter

Dynamic tables are usually placed in hidden sheets of a workbook since they serve as a source for dropdown selections and so on.

With the "1" parameter the dynamic table can be refreshed only once per session - this can save a lot of READ performance if the dynamic table does not change during the session of a collector user. Please be aware, that the initial load does not change the behaviour of the sheet itself. If the sheet with the dynamic table is visible, then additionally to the initial refresh the dynamic table will also be refreshed when the sheet is activated and it will also be refreshed with every element selection in the dimension tree.

Alternatively a "READ Sync" from a visible sheet could be defined - this is necessary if the data in the dynamic table changes during the session. This comes with a price on READ performance since the refresh of the dynamic table is executed with every READ process in the visible sheet.

Related articles