Release February 2023

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:

• data1_CELLS01_DimensionValues_T_DIM001_Filter
  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.
  
• data1_CELLS01_DimensionValues_T_DIM001_Sort
  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:

• data1_CELLS01_DimensionValues_T_DIM001_DIM001_KEY
  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.
  
• data1_CELLS01_DimensionValues_T_DIM001_DIM001_NAME
  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.

All together:

(3) Web Client: Read first element

The following new parameter replaces the previous parameters “data1_ReadFirstTopElement” and “data1_ReadFirstBottomElement”.

• data1_ReadFirstElement

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