We are happy to deliver the brandnew February 2023 release with the new ListQuery function, the extended RLS definition and many other new features! Try it now!
Remember, the Web Client is the user friendly spreadsheet for your Collector Users to deliver their monthly/weekly/daily data. The Web App is the tool for the Power User to setup and control your organization’s collection workflows.
(1) Web Client: New ListQuery feature – how it works
The new ListQuery feature can be used to generate dynamically necessary rows and/or columns! By selecting an element in the dimension tree, the list feature generates a list with the relevant elements depending on the selected element:
Please be aware that this new list feature replaces the previous solution using dynamic tables as a helper for dynamic reports. The new feature is better performing and does not need complicated formula constructions with the INDEX() function and others.
(2) Web Client: New ListQuery feature – how it is defined
The ListQuery feature only needs 2 defined named ranges to define the (SQL) query for the drilled list of elements:
The first parameter is the where-clause, which is applied on the referenced dimension table (here: DIM001). In the showcase below, we use the result of another parameter – namely data1_CELLS01_DIM001_Key_Path – in order to filter the dimension tree table on the elements below the selected tree node.
The second parameter is the sort-condition, which is applied on the resultset of the list query.
With these 2 parameters you can generate as many resultsets for the KEY, the NAME and any Attribute that is needed in the worksheet:
This is the list with the element KEYs from the DIM001 Table. This resultset is used to query the values in a dynamic list report.
This is the list with the element NAMEs from the DIM001 Table. This resultset is used to fill the row headers in a dynamic list report.
(3) Web Client: Read first element
The following new parameter replaces the previous parameters “data1_ReadFirstTopElement” and “data1_ReadFirstBottomElement”.
This parameter rules the behaviour when entering a sheet the first time after loading the workbook.
- 0 = do not read
- 1 = default = the first top element (from the tree) will be read on entering the sheet. Used to read directly the current aggregated sums in order to get an overview of the status of the data collection.
- 2 = the first bottom element (from the tree) will be read on entering the sheet. Used to jump directly to the first bottom element in order to start immediately with data entry.
- 3 = read the predefined element from the named range “data1_CELLS01_DIM001” (from the workbook) -> if this element is not found / not valid, then no READ is executed.
(4) Web Client: Elements with same name
Until now, elements with the same name within the same node have not been displayed properly. From now on, this is no issue any more, every element (based on its unique KEY from the dimension table) is shown in the dimension tree:
(5) Web App: Row-Level-Security (RLS) extended level definitions
We are really happy to present the new extended RLS definition. Until now, it was difficult to define RLS in dimension tree structures with same name nodes. E.g. authorizing a user only for “Sales Team 1” in our HR showcase shown above was quite difficult until now.
From now on, every RLS record can – but must not – be concretised with additional information in the higher levels of the dimension tree. This way it is quite easy now e.g. to authorise a user only for “Team 1” in the “Team Maintenance” branch and for “Team 2” only in the “Team Sales” branch of the tree:
This is the result in the Web Client:
Existing definitions in your application(s) based on the previous technique were transformed automatically to the new definition structure.
(6) Web App: Blank row filter for upload files
Until now it was a problem, if an Excel upload file contains e.g. a formatted cell below the actual data, since this leads to 3 additional imported blank rows which caused an error when uploading this file (due to a primary key violation):
From now on, the upload of such an Excel file is no problem any more:
(7) Technical Improvements and debugging
We have solved issues with the search function in the dimension tree and with the long-running opening of the edit dialog in the dimension tree. Furthermore we have solved an issue with the (undesireable) writeback of % aggregation elements and an issue with the +/* fact attribute definition. And there was an issue with the query of the data1_CELLS01_DIM001_Attribute parameter.
Hope you like the new features – we are very curious about your feedback!
Please let us know what you think and what kind of collection processes you would prefer to automate with data1.io.
Stay healthy and we #StandWithUkraine