You can use several optional parameters in your Workbook to control the visibility of rows and columns. Please remember that all named ranges in data1.io must be defined on a sheet (and not workbook) level.
The most important
The following optional parameters can be defined to control the visibility of rows and columns:
The following optional parameters can be used to control the appearance of the spreadsheet:
How to use visibility parameters for rows and columns
With the visibility parameters rows and/or columns can be shown or hidden:
0 = always hide the column or row
1 = hide/unhide the column or row with the details button
2 = always show the column or row
any other (e.g. -1) = do nothing
The "1" rows and columns can be hidden or shown by pressing the "Details ..." button. Furthermore those parameters have a “change trigger“: whenever a value changes in the visibility named ranges, then the command is executed and the rows and columns are hidden or shown immediately (depending on their current state).
You can use this for many fields of applications, get inspired by the following two.
- Show/hide detail rows and/or columns by pressing the details button
- Show/hide detail rows and/or columns controlled by a dropdown selection
You can control now the visibility of rows and columns simply with dropdowns – and of course you can combine this also with the Details button. In the background this is simply done by connecting the visibility settings with the dropdowns via normal Excel formulas.
- Show/hide details rows and/or columns controlled by the tree element selected
You can show/hide now very easily rows and columns that are only relevant on the bottom level of the dimension tree. In the background this is simply done by connecting the visibility settings with the value of the data1_CELLS01_IsSumLevel parameter.
Please be aware that this feature causes a performance overhead – therefore test your workbook carefully with this great feature before you publish it to all your collector users.
How to use the appearance parameters
0 = hide
1 = show
no definition = default
"1" will show the formula bar and the named ranges dropdown:
"0" will hide both:
No definition will show the defaults - formula bar on and named ranges dropdown off: