Release June 2024

We are happy to deliver the brandnew June 2024 release with the new spreadsheet options and a new always encrypted mode and many more! 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 Spreadsheet Options (only for Power Users)

We have a new feature for Power Users! If you are either in the role of an “Tenant admin” or an “Application owner” then you’ll see the new Spreadsheet Options command in the context menue of the spreadsheet if you have pressed also “Ctrl”:

… which enables you to configure the new Spreadsheet Options:

  • Unhide all rows and columns
  • Page Layout Settings (Gridlines)
  • Formula calculation and Error Checking
  • Unprotect Sheet or Workbook
  • View Settings (Formula Bar, Named Ranges, Frozen Panes, Headers)

Using e.g. the Unhide function will shou you – as a power user – all hidden rows and columns:

(2) Web Client: Always Encrypted on Dimension Keys (Preview)

Until now, it was only possible to apply the Always Encrypted function only on numeric fields like “Measure01”.

Unfortunately this costs a lot of performance in the Web Client and makes any READ process much slower. Therefore we make it possible now, the encrypt the Dimension Keys and not the numeric fields. This encryption is applied to the fact table …

… as well as on the key fields in the dimension tables:

This brings also some restriction like reduced filter options on these fields and the sorting is not possible. But we are optimistic that this change can bring huge performance gains. If you are interested in (temporarely) change the encryption on your application and test the new possibilities, please contact us.

(3) Web Client: Calculated Columns for Dynamic Tables

With this release, it is also possible to use calculated columns on your dynamic tables – they are kept and calculated after the refresh of the dynamic table:

(4) Web Client: Simplified Definition for By Column Mapping

We have simplified the definition for the data1_CELLS01_ByColumnMapping parameter: until now you had to write “data1_CELLS01_MEASURE_0” and so on …

… from now on you can leave the “data1_CELLS01_” prefix and simply write “MEASURE01_0” (which is the target column and the relative record number):

Please note that the previous syntax is also continued, so you do not need to update your existing workbooks.

(5) Web Client: New Parameter “data1_IsWriting”

We have created a new parameter “data1_IsWriting” which has 2 parameters:

  • 0 = no Write process is executed
  • 1 = Write process is executed

You can use this parameter e.g. to set alternative dimension keys for the write process other than for the read process. This parameter is derived from all CELLS0x areas.

(6) Web Client: Data Ranges for Copy Data Processes (Add-On)

We have created the possibility to define data ranges as filters for Copy Data processes:

(7) Debugging and Improvements

We have improved the CELLS02 feature, which was introduced with the May 2024 release. There was an issue with the WRITE process if the CELLS02 area didn`t have any write activated cells, another issue with Date-Ranges during the READ process and an issue with DATE01_Level definitions.

There was an issue with the record set structure in combination with CELLS02 and SysDelta definitions.

We have also improved the initial refresh for dynamic tables, which was also introduced with the May 2024 release. Before the initial refresh is executed the top element from the dimension tree is loaded into the sheet with the dynamic table since this is often used as filter criteria for the dynamic table.

There were two issues with the Iterate Sheet function which are solved now.

Furthermore we solved an issue with DIM001_Alternative Keys which resulted in a very poor READ performance. Another issue could be solved with poor performance for emptying cells during the READ process under certain conditions.

There was also an issue with the check for the activation/deactivation of the WRITE button which led to poor performance during data entry in the workbook and/or when scrolling the workbook.

In combination with the new Action Buttons the dialog for unsaved data was not shown, this is solved now.


We hope you like the new features – we are very curious about your feedback!

Please let us know what you think about data1.io and what kind of planning and data collection processes you evaluate to automate.

we #StandWithUkraine

Release May 2024

We are happy to deliver the brandnew May 2024 release with the new Multiple Layouts and many more! 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 Multiple Layouts with CELLS02

Until now, in data.io Workbooks only one data entry area per sheet could be defined (done with “CELLS01” named ranges). With this release, in the same sheet a variable number of data entry areas can be defined (CELLS02, CELLS03, etc.).

a. Fields of application

This new function makes many new usecases for planning, data collection and also reporting possible:

  • Collecting different types of information within the same sheet (e.g. blocks with different types of metrics, blocks for cost and invest, etc.)
  • Showing comparison data from the last forecast / budget / last year in the current period

b. Example – Data Entry

Let’s have a look on a practical example. We assume that the creator of this workbook has decided that there should be 2 data entry blocks behind each other – one for capacity metrics (left block) and another one for financial metrics (right block).

c. Example – Definitions

This type of layout would not have been possible until now since the named range for the metrics dimension could be done over 2 columns. With the new possibilities, a CELLS02 data entry area can be defined with its own independant DIM004 definition (which is used for the metrics in this application):

Following the data1.io Workbook Framework the full set of named ranges has to be defined for the new CELLS02 data entry area:

d. Example – Performance Analyzer

You can see the number of data entry areas – CELLS01, CELLS02, etc. – also in the Performance Analyzer:

As you can see, the second data entry area comes with a price on performance since the CELLS02 area generates its own SQL queries against the SQL database in the backend. So you should use this new functionality only, if there is an added value for the collector users.

e. Example – Fact record structure

The structure of the fact records is NOT affected by the number of data entry areas (CELLS01, CELLS02, etc.). You can see the two data entry areas in this fact table only because the creator of the workbook has decided to differentiate the “Dim03 KEY” and the “SYSTEM Delta” parameters:

Therefore the creator of the workbook has to guarentee that the data from the several data entry areas are not mashed up in the fact table and therefore also get mashed up during the READ process. Therefore CELLS02 definitions are an advanced feature. Please contact us, if you need some help with your application.

(2) Web Client: Initial Refresh for Dynamic Tables

We have introduced dynamic tables in July 2021 (it was called then “Filter Tables”) and improved this feature in several releases of 2021 and 2022. We are happy that with this release we can deliver an important new function that will make this feature much more relevant in practice!

Usually dynamic tables are placed in hidden sheets of a workbook and they serve as a source for dropdown selections and so on. Until now, those hidden dynamic tables could only be loaded / refreshed with a READ-Sync from another visible sheet. The problem is, that with every READ process in the visible sheet also the hidden dynamic table has to be refreshed although the data in the table does not change. This comes with high cost for the READ performance in the visible sheet.

With this release you can define the new parameter

  • data1_CELLS01_InsertTable_ReadOnLoad

whereby

  • 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
  • 0 = default behaviour … the dynamic table is refreshed only (a) if the sheet is activated or (b) if a READ-Sync is defined for this sheet

With this new parameter activated, the hidden dynamic table is refreshed already during the initial load process of the workbook. You can prove that with a metric using the COUNTA() function on the hidden table (which shows here “33” clients in the hidden table):

If there is a navigation button to the hidden sheet with the dynamic table, the current content of the table can be checked in every detail:

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 activating the sheet and it will also be refreshed with every element switch in the dimension tree.

(3) Web Client: Improved Loading Screen

We have improved the Loading Screen for Workbooks respectively the Web Client. The loading screen now shows the single steps that are executed in the background. This way, you and your collector users are informed about the loading processes and the time that is necessary to do this.

(4) Web Client: Action Buttons on Merged Cells (Preview)

We have introduced the new Action Buttons in March 2024 release as preview and improved them in April 2024 release. With this release we added the support for merged cells in the Excel spreadsheet. This way, it is easier to combine buttons of different size in the same column or rows.

Look at this button …

… it is defined in a merged cell area consisting of 4 cells:

Simply merge the cells in your Excel Workbook and then the Action Button will be generated in this cell area.

Please be aware that the new Action Buttons are still in preview.

(5) Web App: Improved Date Format

Finally we have managed to display dates simply in their original format – as date!

Until now dates were displayed as date/time (“30.06.2024 12:00 AM”) which could lead to confusion about the real content of date columns. The new date format uses the english date format.

(6) Debugging and Improvements

We have improved the refresh of the dynamic tables – until now it could happen that after the refresh of the table old records remained outside of the table object somewhere in the spreadsheet.

Furthermore we have solved an issue with the record structure of the new SYSTEM Delta definition introduced with the April 2024 release.


We hope you like the new features – we are very curious about your feedback!

Please let us know what you think about data1.io and what kind of planning and data collection processes you evaluate to automate.

we #StandWithUkraine

Release April 2024

We are happy to deliver the brandnew April 2024 release with a new Writeback Function and even more! 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 Writeback Function with SYSDELTA

Basically, writeback of new data always includes an update or replacement of existing / preliminary data. In data1.io this replacement of existing records is controlled with the so called SYSTEM Delta Parameter which is defined by the author of the workbook. Until now, only one parameter per sheet could be defined. With this release, we have removed this restriction and you can define several or even many such parameters whithin the same sheet.

a. Fields of application

This new function makes many new usecases for planning and data collection possible:

  • Collect FORECAST and BUDGET data within the same sheet in the first planning round (from the collector users) and then edit them seperately from each other in a second planning round (e.g. by controllers).
  • Collect ACTUALS and ESTIMATE data within the same sheet and then improve Estimate in a second planning round without changing Actuals anymore.
  • Collect COMMENTS together with FORECAST and later finalize Comments
  • Edit DATA and COMMENTS in workbooks with (list) queries from different tree elements (e.g. using Alternate Level definitions)

b. Example – Data Entry

Let’s demonstrate all this with a simple example. In our public showcase Cost & Invest Budgeting you can collect FORECAST data for the current year 2024 (“FC 1”) and BUDGET data for the years 2025 – 2027 (“BUD”):

c. Example – Definitions

Until now, the SYSTEM Delta parameter had to be the same for all records generated out this sheet when executing the writeback:

From now on, you can define the SYSTEM Delta as “horicontal” or “vertical” named range and therefore you can define different parameters for different columns of this sheet:

d. Example – Performance Analyzer

Until now, each writeback had only one SYSTEM Delta paraemeter and therefore triggered only one replacement of existing records (with the same parameter):

From now on, each writeback triggers as many replacements of existing records as different SYSTEM Delta parameters are found in the sheet at the time of executing the writeback:

e. Example – Fact record structure

Until now, all new records generated by a writeback process got the same SYSTEM Delta parameter (which will be the criteria for the replacement as soon as new records are written back):

From now on, the records of each column (or row) in the sheet can get their individual SYSTEM Delta parameter and this makes it possible to edit only a portion of the initial data amount in different data entry sheets:

f. Conclusions

This new definition of the SYSTEM Delta parameter enables a lot of new use cases!

As you see in the screenshots of the Performance Analyzer above, this new function comes with a price in performance. In our experience, WRITE performance usually is no issue while READ performance is nearly always a big issue. But nevertheless, you should keep that aspect in mind.

More importantly, variable SYSTEM Delta parameters makes your application more powerful but also rises complexity of your application. As probably many of our power users know, it is quite easy to generate undesired fact records by having small definition errors in the SYSTEM Delta parameter and this risk rises with variable parameters. So, we strongly recommend that you have to test even more if you use this great new feature!

(2) Web Client: Improvements for Action Buttons (Preview)

In our last March 2024 release we launched the preview of the new action buttons. With this release, we extend the functionality and make the feature more stable.

a. Auto show/hide sheets for Navigation Buttons

With this release, you can hide the sheets which are called by a navigation button:

The sheet is made visible automatically when the navigation button is pressed and it will be hidden again, as soon as you leave the sheet (e.g. by clicking the Back button):

This is the same functionality that we delivered with the prior hyperlink based navigation.

b. Demo for Dynamic Buttons

We have implemented a demo for dynamic buttons in our public showcase HR Contracts. Depending on the selection in the dropdown menu, the button changes title, target sheet and status:

You want to know, how this works? Here we go! 🙂

  • The most imporant thing is to use the “Cell Adress” parameter, i.e. the dynamic button must be generated in another cell than the formula cell (only this way the button can be changed dynamically)
  • The rest are IF() conditions and a lot of “” and “”” to get the correct syntax 🙂

c. Improved formula support for Dynamic Buttons

There was an issue with long formulas. Strictly speaking this is not a button specific issue but a spreadsheet issue in general. But it is very probable that you come across with this topic only if you use dynamic buttons.

Until now, the workbook could not be loaded if a formula was used which contained a text string that was longer than 255 characters. From now on you get a proper error message in order to be able to solve the issue:

You can solve the issue by “cutting” the too long text string in two ore even more pieces. Here in this demo this is done firstly with the IF() condition for the status and secondly with an extra ” & ” divider:

By doing this, the workbook can be loaded without any problems and the button also works 🙂

(4) Improvements and Debugging

We have solved some issues in the Web App (“Backend”). For instance, the refresh of table views now refreshes automatically after uploading a file. There was an issue with half-sized dropdown fields. In the Workbook and Workflow editors some definitions had to be entered twice to be saved correctly. Those little but annoying issues are all solved now.


We hope you like the new features – we are very curious about your feedback!

Please let us know what you think about data1.io and what kind of planning and data collection processes you evaluate to automate.

we #StandWithUkraine

Release March 2024

We are happy to deliver the brandnew March 2024 release with the Preview for the new Action Buttons! 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 Action Buttons (Preview)

Until now, we had only cell-based hyperlinks for navigation purposes. With the preview for the new Action Buttons we introduce

  • real buttons
  • with two states “enabled” and “disabled”
  • which are generated by a cell-based definition (statically or dynamically)
  • which support 7 different action types

The new buttons are in preview – that means that you can use this new functionality but there are some known (and maybe unknown) issues and changes are possible.

1a. Action Buttons – Frontend

The new action buttons are generated in the size (but not the format) of the target cell and look like “normal” buttons. You can use them e.g. for page navigation …

… and also as trigger for actions like Drillthrough documents or Iterate Sheet processes:

And here see them in “action” 🙂

1b. Action Buttons – Definition in Excel

The definition of an action button consists of two parts:

  • Named Range beginning with “data1_Button
  • JSON like definition of the button:
    { "Title": "Existing Contracts", "Action": "Navigation", "Action Parameter": "HR Contracts", "Status": "Automatic", "Rounded Corners": true, "Background Color Enabled": "#FFC000", "Text Color Enabled": "#000000", "Border Color Enabled": "#FFC000", "Background Color Disabled": "#595959", "Text Color Disabled": "#000000", "Border Color Disabled": "#000000" }

These are the parameters

  • Title = text on the button
  • Action = action type (see next topic)
  • Action Parameter = parameter like sheet name (see next topic)
  • Status = Automatic or Disabled
  • Rounded Corners = true or false
  • Background Color Enabled = color code (hex or text)
  • Text Color Enabled = color code (hex or text)
  • Border Color Enabled = color code (hex or text)
  • Background Color Disabled = color code (hex or text)
  • Text Color Disabled = color code (hex or text)
  • Border Color Disabled = color code (hex or text)

The order of the parameters does not matter for the functionality and can be set arbitrary.

1c. Action Buttons – Available Actions

At this stage the new Action Buttons support the following action types:

That means you can use the buttons also to trigger the Read and/or Write process.

1d. Action Buttons – Additional functions

There is one more optional parameter:

  • Cell Address = target cell for the button (i.e. the definition of the button does not have to be in the cell where the button will be generated):
    { "Cell Address": "B2", "Title": "Existing Contracts", "Action": "Navigation", "Action Parameter": "HR Contracts", "Status": "Automatic", "Rounded Corners": true, "Background Color Enabled": "#FFC000", "Text Color Enabled": "#000000", "Border Color Enabled": "#FFC000", "Background Color Disabled": "#595959", "Text Color Disabled": "#000000", "Border Color Disabled": "#000000" }

Furthermore, the JSON definition for the action button can be changed dynamically with formulas.

(2) Web App: Display of e-mail adresses

We have streamlined the display of user names respectively e-mail adresses in the Web App. From now on, user names are shown always as e-Mail adress (without registration name):

(3) Debugging

We have solved an important issue with the definition of the tenant admin(s) and application admin(s).

We have also solved an issue with the Copy Data trigger in the context menu of the dimension tree.

Furthermore we have solved an issue with the upload dialog for dimension and fact tables.


We hope you like the new features – we are very curious about your feedback!

Please let us know what you think about data1.io and what kind of planning and data collection processes you evaluate to automate.

we #StandWithUkraine

Release February 2024

We are happy to deliver the brandnew February 2024 release with the new Replace Mode for Fact Table Uploads and many more! 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 App: Replace Mode for Fact Table Uploads

We have introduced the Update Mode for Dimension Table Uploads in March 2023 Release. We are happy that we can deliver now the corresponding feature for the fact table – the brandnew Replace Mode.

This new Mode is based on the content of the column SYSTEM.Delta in the upload file for the fact table. Assume you have an upload file with records which all are flagged with the text Actuals 2020 in the SYSTEM.Delta column:

After starting the upload of this file into the Fact Table you can choose now between the already existing Append Mode or the new Replace Mode:

Now the system analyses the recordset structure of the upload file and determines how many already existing records in the fact table will be replaced by the upload:

If you continue, then the 81 existing records are going to be deleted and the 111 new records from the file are inserted into the fact table (therefore the number of records changes from 551 to 581):

We are optimistic that this feature saves you as a power user a lot of time and increases the operational safety in upload processes.

(2) Web App: Date Filters for the Copy Data feature

The new Copy Data feature was released in January 2024 release. Now, the definitions are extended to filters also for the date fields. This helps you to define exactly the correct amount of records for your scenario and forecasts processes:

At this stage you can define date filter but no variation for the target records (like a time shift, this is planned for a future release):

This is the way the new date filters are presented in the Web Client for the end users:

(3) Web Client: Iterate Sheet with Screen Wake Lock

We have introduced the Iterate Sheet function in the January 2023 release. There was an issue with the screensaver, which is mandatory in many companies – the screensaver prohibits the further execution of this client based process.

Therefore we have implemented …

… a so-called screen wake lock in order to prohibit the screensaver to activate before the Iterate Sheet process is finished:

At this stage the screen wake lock is supported by the Browsers Chrome and Edge but not by Mozilla Firefox:

(4) Web App: Delete Function for Workflows

From now on it is possible not only to add new workflows but also to delete existing workflows …

… by pressing the red Delete button in the bottom right corner of the dialog box:

It is not possible to delete all workflows since every data1.io application must have 1 workflow minimum. We recommend to carefully evaluate the deletion of a workflow since it is part of the documentation of the collected records in the fact table.

(5) Web App: Add/Delete Function for Tenant Admins

From now on it is also possible to maintain the Tenant Admins in your tenant by adding new and deleting existing tenant admins:

Similarly to the workflows, it is not possible to delete the last tenant admin since every data1.io application must have 1 tenant admin minimum.

(6) Web App: Improvements in the UI

We added two useful buttons to make your work as a Power User more effective.

First, we have added an Upload button directly in the workbook list which saves you having to open the workbook editor:

Second, we have added a refresh button in the Key Wizard which saves you to refresh the browser to get the current query:

(7) Debugging

We solved an issue with the “data1_SyncSheets_Write” parameter in combination with Level Dimensions.

We have improved the database structure in the backend which reduces the risk of capacity bottlenecks.

We have also solved an issue with the date range queries (introduced in the October 2023 release).

We have also solved an issue with the continous workflows and another issue with workflow e-mails which were sent too early.


We hope you like the new features – we are very curious about your feedback!

Please let us know what you think about data1.io and what kind of planning and data collection processes you evaluate to automate.

we #StandWithUkraine

Release January 2024

We are happy to deliver the brandnew January 2024 release with the new copy data feature and our new price plan! 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 Copy Data Function

The Copy Data function duplicates the existing records in the fact table of the selected element and alters e.g. the scenario. It can be used for the following tasks:

  • Freeze the current data status as „Forecast x“ or as a backup (Collector Users)
  • Create a new scenario from the current data status (Power Eser)
  • Create the default values for the upcoming Forecast out of the Budget or the last Forecast (Power User)

This function is basically designed to be used by the Collector Users (= End Users), but it can also be configured to be used only by Power Users. Please be aware that at this stage the Copy Data function does not support all thinkable functions.

1a. Copy Data – Frontend in the Web Client

The new Copy Data function is triggered in the context menu either on a bottom element or on a sum node level in the dimension tree:

This starts the Copy Data Dialog …

… and you can have a look on the predefined filter and target conditions (which cannot be edited in this scenario):

After clicking the Start button, the 3 existing records were duplicated:

1b. Copy Data – Records created

Here you can see the 3 source records that were filtered by the Copy Data function …

… and were duplicated and modified according to the “target” condition:

Furthermore the “SYSTEM.Delta” field was modified according to the “Dim02 Key” field and of course the “Created UTC” and “User Key” fields filled:

If the (Collector) User repeats the copy data process, then the existing “target records” are replaced by the new duplicates:

In the Copy Data Journal you`ll find a log of every execution of the Copy Data function:

1c. Copy Data – Definitions in the Web App

The Copy Data Processes are defined in the new Copy Data definition dialog …

… where you define the filter conditions and the target values for the records modification:

Furthermore, the Copy Data function has to be activated on the workbook level:

Optionally, the Copy Data function can be allocated to certain users and certain dimension tree nodes with the RLS definition:

1d. Copy Data – License

The new Copy Data function is licensed with an Add-on License (pricing plan). Please contact us, if you want to activate a trial in your individual application:

(2) Web App: New Workbook Editor

The workbook editor became with more and more functions a bit confusing …

… so we streamlined and structured the functions in the new workbook editor:

(3) Web App: Workflow e-Mails and Finish Button only to WRITE-Users

Until now, all users of a workflow got the workflow e-Mails and had a Finish button. With this release, this is differentiated now by the right in the RLS definition – Read/Write or Read Only:

Users with Read/Write permission see of course furthermore the Finish button:

New is, that users with Read Only permissions do not get the Finish button anymore …

… and of course will not get any e-Mail notifications anymore:

(4) Public Showcases now with Workflows

Until now, the Public Showcases did not have an active workflow. We changed that to make the Public Showcases even more realistic:

(5) New Pricing for new Tenants

Until now, we had a very very low price for the Enterprise Plan (EUR 2,50 per month and app). This was a reward for the early adopters of data1.io who used the software in an early stage.

With the maturing of the data1.io software, we increase the price for the Enterprise Plan up to to a more realistic level of EUR 4,50 per month and app. This new price is valid for all new tenants, existing Enterprise Plan customers of course keep their early adopter conditions for lifetime as promised!

Furthermore we decreased the price for the “Copy Data” feature from EUR 30 to EUR 20 per month and app (which is valid of course for all new and also for all existing tenants).


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.

we #StandWithUkraine

Release November 2023 (before christmas break)

We are happy to deliver the brandnew November 2023 release with the new showcase “Integrated P&L Reporting” and a performance boost for aggregated queries! 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) Showcases: New “Integrated P&L Planning”

We are very happy and proud to deliver the brandnew “Integrated P&L Planning” showcase which is powered by our partner company 4C Group AG in Munich:

1a. Landingpage

On the Landingpage you can see three planning areas and the corresponding elements (and element types) in the (unfiltered) dimension tree.

b. Revenues Planning

The showcase company is a service oriented company which sells consulting services on a days basis. Therefore the revenues planning is a quantity x price planning process. The relevant customers within a profit center can be selected from a prefined list of customers and then the Forecast values for the number of days and the average rate is entered (in the yellow cells). From a technical point of view the customer list is realized with a dynamic list query:

This results in a total sum of revenues and a total sum of Consulting Days, which is important for the next planning area, the HR capacities planning:

c. HR Capacities Planning

The HR capacities planning is a also quantity x price planning. The data entry is done on a single capacities element within each company. Similarly to the revenues planning the number of roles is variable and can be selected from a dropdown. The user enter only the number of FTEs per role and the number of available consulting days as well as the HR cost is calculated automatically with pre-defined factors per role.

The special feature here is the comparison with the planned consulting days from the revenues planning, since the capacities must fit to the sold volumes. From a technical point of view the consulting days are retrieved from the sum level above the Capacites element, this is realized with an “alternative levels” query parameter which we introduced last month in the October 2023 Release.

The HR capacities planning results in a total sum for Capacities Days (compared with the Consulting Days) and a total sum for HR cost:

d. EBIT Planning

The final step is the cost planning on an account basis per cost center

… and joins the parts from the revenues and the HR capacities planning together …

… and delivers the final EBIT:

(2) Web Client: Performance boost for AGGREGATED and ALWAYS ENCRYPTED queries

We have invested a lot in boosting the performance of aggregated queries. Until now, using aggregated queries usually led to a poorer performance since the aggregation had to be retrieved in seperate (and there additionally) query on from the database which resulted often in a poorer performance than using a lot of hidden rows or columns to retrieve the data on a non-aggregated level.

With this release we have made major performance improvements in the following areas:

  1. Aggregated RANGE queries
    All queries are executed in one run on the server (and not in the database)
  2. Aggregated YEAR/QUARTER/MONTH queries
    Also here, all queries are executed in one run on the server (and not in the database)
  3. ALWAYS ENCRYPTED
    The sum up of the single records is done now with a new group by index on the server (since this is not possible in the database)
  4. New INDEX on the fact table
    At this stage this index is not created by default

For instance, the report HR Totals in the HR Contracts showcase uses RANGE queries. This report is now very fast …

… since all the data is queried from the database with only one single request:

(3) Web Client: new function “Analyze write process”

We have invested in another analyzer tool – we are happy to presend the brandnew “Analyze write process“! You will find it in the context menu of the dimension tree when you additionally press the “Ctrl” button on your keyboard:

On the first tab you can see a preview of the fact records that will be generated out of the cell values within the workbook sheet when you later will press the Write button:

On the second tab you see something very interesting and usefull: the cell values that will NOT be transformed in fact records and also the reason why:

Last but not least you can also fill all the writeable cells

… with random values:

We hope that this new tool will help you to better understand what consequences your definitions will have on the fact records that will be generated during the write process.

(4) Web App: Performance Analyzer improvement

We have done another improvement for the Performance Analyzer (started with the shortcut “Ctrl + Alt + P”). When you hover the pie chart you can see not only the relative share but also the absolut duration in seconds:

(5) Web App: E-mail sender in journal

In the October 2023 Release we have introduced the new e-mail sender settings. This month we have added the E-Mail Sender column to the Sent E-Mails Journal:

This is important when you change the E-Mail Settings from default sender to company specific mail adresses since then the mail adress(es) from the workflow organizer(s) are used.

(6) Showcases: Removed “S&OP Demand Planning”

We have removed the showcase “S&OP Demand Planning” since it is replaced by the new “Integrated P&L Planning” showcase.

(7) Debugging and Improvements

(will be delivered)


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.

We`ll be back after christmas break with the January Release!

we #StandWithUkraine

Release October 2023

We are happy to deliver the brandnew October 2023 release with the new drillthrough workbooks 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 drillthrough workbooks – how it is used

There is a new way to open a workbook: the drillthrough!

It can be used for many workflows, here are some ideas:

  • Workbooks with comparison data from the last forecast / last budget
  • Additional reports which should not be part of the major workbook

Simply click in the context menue (= right mouse click) of a dimension tree element (or node) and trigger the predefined drillthrough document:

The drillthrough document is loaded in a new browser tab

… and is automatically switched to the same dimension tree element (or node) where the drillthrough was started:

(2) Web Client: New drillthrough workbooks – how it is defined

At first, the drillthrough document is defined as (normal) workbook in your application:

Secondly, open your major workbook (which is linked to your workflow) and add a new drillthrough workbook:

In this dialog you select

  1. The workbook which should be used as drilltrough target
  2. The title of the context menue entry
  3. An optional filter on which elements in the dimension tree the drillthrough entry should be shown
    e.g. “[DIM001.DIMTYPE.UID] = 23” means that the drillthrough document is only available on elements from type 23; the drilltrough is also shown on sum nodes if only valid elements are below of the selected node …

Usually, the drillthrough workbook is not be allocated to a workflow, therefore no “widget” on the landingpage will be generated. This way, the drillthrough workbook is only accessable via the drillthrough action in the dimension tree.

That`s it – just try it!

(3) Web App: New e-mail sender settings

Until now, workflow e-mails in data1.io were always sent with the default e-mail adress no-reply@data1.io. Unfortunately in some cases those workflow e-mails are classified as spam and never reached the recipients.

We have a solution now for this issue! Now you can setup your own company account at www.sendgrid.com and authorize the cloud service to send e-mails on your behalf. We have documented the creation process in our Help Center, finally you get an API key from SendGrid which is placed here in this new E-Mail Settings dialog:

After activating the new sender e-mail option, all workflow e-mails from your application are sent with the organizer`s e-mail adress on behalf:

Please be aware, that the so called sender authentication in the SendGrid cloud service must fit to all workflow organizer`s e-mail adresses in your application.

(4) Web Client: New “Alternative levels” query parameter

We are happy to present a new query parameter which will be very useful for many applications: the alternative level query. That means in your workbook you want so show values from other elements (or nodes) than the selected element in the dimension tree.

Fields of application:

  • Coordination of sub-plans on different levels (see example below)
  • Integrated balance sheet planning (which needs the sums from profit center and cost center planning)
  • Ratios on benchmark values from a top level
  • and many more!

Look at this example:

The metric “Consulting Days” was planned on various profit center elements below the company “Alpha Treasury Services”. In the “HR Capacity Planning” the sum of the planned Consulting Days has to be shown in the workbook, although the element “Capacities” is selected. Therefore the Consulting Days have to be retrieved from the alternative element “Alpha Treasury Services”:

This query on the alternative level is realized with 2 new filter parameters:

  • data1_CELLS01_DIM001_Alternative
    In this field put the alternative element name or node name (here: “Alpha Treasury Service”)
  • data1_CELLS01_DIM001_Alternative_Read_Attribute
    In this field put the attribute information for the query of the alternative element name or node (here: “DIM001.A002”)

Please be aware of the following properties / restrictions:

  • In the same sheet queries on the selected element/node can be mixed with queries on alternative elements/nodes
  • Rows or columns which are queried from alternative elements/nodes are read-only (no writeback due to the SysDelta algorithm)

(5) Web Client: New “Date Range” query parameters

Until now, you needed 12 hidden columns in order to query the Actual and Plan months in the correct way and then sum up the Actuals and the Plan values. With the new “Date Range” parameters you can directly query the sum of the correct months with Actuals and the sum of the correct months with Plan values:

Just to be clear: we have an application with 3 months Actuals and 9 months Plan values:

This is implemented with 2 new query parameters:

  • data1_CELLS01_DATE01_RANGE_FROM
    This date defines the beginning of queried period.
  • data1_CELLS01_DATE01_RANGE_UNTIL
    This date defines the end of queried period.

Please be aware of the following properties / restrictions:

  • You can mix normal date queries with date range queries in the same sheet (the content in the named range data1_CELLS01_DATE01 is not relevant in rows or columns with date ranges, but we recommend to write the text “RANGE” into those fields just to make it clearer)
  • Rows or columns which are queried with a date range are read-only (no writeback)
  • At the time of release the date ranges cannot be combined with date level queries (YEAR, QUARTER, MONTH, DATE) within the same sheet

Cross-reference:

In special cases you can query the correct annual forecast value with a level query like this. But this only works as long you have only 1 plan scenario per year and as long you do not load new actual months after the planning workflow was finished.

(6) Web Client: New “Treefilter” query parameter

With the new Treefilter query parameter you can filter your read data not only by dimension 1to 10 but also on certain elements in the dimension tree.

Take a look at this report: the first block (“Contracts”) queries metrics from the “green” elements and the second block (“New Hires”) queries the same metrics from the “blue” elements.

Those queries can deliver wrong totals if (1) in the fact table there are also dumped records and (2) this option cannot be activated on the workbook level:

To guarantee correct totals calculated only from visible (= valid) elements with the wright element type you can define now an additional treefilter query parameter within the sheet:

  • data1_CELLS01_TreeFilter
    You can define any filter on the dimension tree elements (use the syntax of an SQL where clause)

This new parameter should not be confused with an existing – similar – parameter

  • data1_TreeFilter
    This parameter filters only the elements in the dimension tree and has only impact on the queried values if the workbook option “Aggregate only visible elements” is activated.

(7) Infrastructure: Automated Tests

We started to invest in automated tests in our DevOps Pipelines since we learned (the hard tour) that we have reached a level of complexity that cannot handled with manual tests anymore.

The automated tests focus on the correct function of the READ and WRITE process within the workbook. The first level of tests is already in production, our goal is to cover all existing parameters (and their combinations) until the end of this year.


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.

we #StandWithUkraine

Release September 2023

We are happy to deliver the brandnew September 2023 release with the new welcome pages, new sheet navigation 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 Welcome Pages

We are happy that you can create now Welcome Pages in your data1.io Workbooks! These new Welcome Pages can be used to structure the content of your workbook and are mainly enabled by the new sheet navigation feature (see next item):

Furthermore we have solved an issue with dark backgrounds on the sheet – there was an issue with the gridlines which became visible under certain circumstances (depending on the screen resolution).

(2) Web Client: New Sheet Navigation

With the new sheet navigation it is possible to use Excel Hyperlinks (“Bookmarks”) to create a sheet navigation from the welcome page to the detailled pages …

… and back to the welcome page. Other than in Excel all sheets can be hidden and the hyperlinks still work since data1.io makes the target sheet visible when clicking the hyperlink navigation.

Hyperlinks for the sheet navigation are created in the Excel version of your data1.io workbook. Simply create a link on a cell …

… and create a bookmark with reference to the target sheet:

Tipp: you can use the font “Wingdings” to create symbols for the “navigation buttons”:

(3) Web App: Configuration of the “Finish” button

With this release you can customize the “Finish” button in the “workflow box” in the Web Client. Until now, the button was green coloured and named “Finish” …

… with this release you can change the title as well as the color of the button:

This customizing can be done very simple in settings of your workflow:

(4) Web App: Streamlined workflow e-Mails

With the last releases we have made many extensions to the configuration of your workflows. With this release we have streamlined the submission of the workflow e-Mails. For instance, until now, reminder e-mails were only sent to your collector users if the received an invitation e-Mail within this workflow. With this release we removed such “hidden” conditions …

… so that only the visible settings control the submission of the workflow e-Mails:

(5) Web App: Finish e-Mails as cc to organizer

With this release you as a workflow organizer can define to get every individual Finished / Not Finished e-Mails of your collector users as copy (cc):

(6) Web App: Performance Analyzer improvements

We have improved the Performance Analyzer (started with the shortcut “Ctrl + Alt + P”). Until now, the share for the SQL queries was too low for applications with the “Always Encrypted” security feature activated.

(7) Web App: Key Wizard improvements

We removed the Upload button and the Delete button since they were actually not active here. Instead, we have added the useful download button and now you can see the number of records also in the Key Wizard:

(8) Web App: Read Process Dialog improvements

We have also improved the Read Process Dialog which shows now also the origin of the values. This is especially useful for the reconstruction of the result of aggregated queries:

Furthermore we have added download buttons for the lists in order to improve analysis of the data in Excel.

(9) Web Client: Warning unsaved data

If your collector users press the back button of their browser with unsaved data, they get now a warning which helps them to prevent the loss of the unsaved data:

(10) Web Client: Improved copy & paste capabilities

We solved the following error when pasting values from special formatted Excel sheets and improved the copy & paste capabilities between different workbooks in different browser tabs.

Furthermore we improved the copy & paste behaviour when pasting date values which are formatted as string.

(11) Debugging and further improvements

We have solved an issue with so-called ExceptionType definitions within the workbook. They were applied also to other sheets within the same workbook.

We have solved an issue with poor workbook performance when validating if the Write button should be active or not after a change in the sheet. And we have solved issues with poor write performance under special circumstances.

We have solved an issue with the upload of the workflow users list (in general there was an issue with all uploads into tables with active validation rules).

Another issue was an inconsistency of the “Workbook wizard” with the Upload button for workbooks.

Furthermore we have improved the READ performance when the browser tab is not active.

There was also an issue in the dimension tree which reloaded under special circumstances on the first selection of an element.

Last but not least we solved an issue with heavy RAM usage when using the Iterate function.


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.

we #StandWithUkraine

Release July 2023 (before the summer break)

We are happy to deliver the brandnew July 2023 release with the new calculation wizard 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 calculation wizard (Add-On)

With the new calculation wizard your users can enter values with the calculation operators +, -, * or /:

How does this work? The definition is very simple:

  • Definition of the named range “data1_CELLS01_Calculation
  • Simply type the column letter of the source column for the calculation in this range (only into the desired columns)

Please note that this new feature is licensed as Add-On since it was financed by a single data1.io customer. Please find the current pricing here.

(2) Web Client: New parameter “Application Key”

The ListQuery feature was introduced in the February 2023 release and was improved in the March 2023 release, in the April 2023 release and the May 2023 release.

If you want to connect your ListQuery filter with the Fact table – e.g. to show only elements with current values – then the application key is needed for this SQL filter criteria. Until now this application key had to be hard-coded within the workbook.

With this release you can use a new parameter to retrieve dynamically the application key:

  • Named range “data1_ApplicationKey

Simply connect your application key with the ListQuery Filter using a formula to generate an SQL filter statement like this:

  • [DIM005.KEY] IN (SELECT [DIM005.Key] FROM appG5K9KA5DVD.T_FACT01 WHERE [SYSTEM.IsDeleted] = 0 AND MEASURE01 IS NOT NULL AND [Dim003.Key] = ‘MARKET’)

(3) Web Client: New parameter “Read after Write”

From now on you can defined a refresh of your sheet after the WRITE process – e.g. in order to refresh the ListQuery with the customers which have already data:

Simply define

  • Named Range “data1_ReadAfterWrite
  • 1 = execute a Read process immediately after the Write process
  • 0 = do nothing after the Write process (= default)

(4) Web Client: Debugging for % queries

There were several issues with aggregated “%” queries:

  1. Combination of “%” query in 1 dimension and “%” query in another dimension
  2. Combination of “%” query in 1 dimension and “-1” query in another dimension
  3. Combination of “%” query with the definition of a “DIM001_Alternative” parameter

Those issues are solved now.

(5) Web App: New behaviour of table filters

We have changed the behaviour of table filters in 2 manners:

  1. Deactivating the filter button removes now all current filters from the table
  2. Deleting records does not remove current filters from the table any more

Think of the fact table with 2 current filters:

First, deactivating the filter button removes now not only the filter symbols from the table header but removes now also all current filters from the table:

Second, deleting records …

… does not change the current filters anymore:

This will help you a lot, if you have to delete records in portions with the variation of only 1 filter criteria and keeping the other filters unchanged. And if you want to remove all the current filters simply press the filter button to do this. 🙂

(6) Technical Improvements and Debugging

We have solved an issue with the error message “Cannot read properties of null (reading ‘findIndex’)” with Sheets that have no READ activated cells.

We have solved another issue with the error message “Sequence contains no elements” with sheets that have no valid date in the named range “data1_CELLS01_DATExx”.

Furthermore there was an issue with the title of workflows using the new type “recurring”.


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. 

We`ll be back after the summer break with the September Release!

we #StandWithUkraine