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