KB483976: Aggregate from base derived metric value is at the grand total level instead of the defined level

Starting with the release of Strategy ONE (March 2024), dossiers are also known as dashboards.

Description 

For arithmetic metric expressions, like (A+B)*C, evaluated from the base, you need to derive its output level based on the input level. When you aggregate the metric value and the aggregation level is not compatible with the metric level, it may result in the grand total level. 

Steps to reproduce

The following example uses Strategy Tutorial on Strategy 2020 with Data Engine version 10 or 11. 

• Create an OLAP cube from Developer with Category, Customer Age, Region, Cost, Profit and a report filter to make Customer Age elements the same as the number of rows in the grid. 
  

• Create a dossier based on the cube.
• Put Category and Region on the grid.
• Define a derived metric with th expression below and set it as Aggregation From Base.
  The level of the [Customer Age]@ID is {[Customer Age]}, Costs's level is {Category, Region}. The derived output level is {[Customer Age]}, due to the flaw. The level of Category@ID is {Category}. Hence, the level inside the sum function is {[Customer Age], {Category}}, and the output level is defined as Region. The input level and output level are not resolvable, which means the metric is calculated at the grand total level.
  

• Notice the metric values are at the grand total level, which means the same value for both regions.
  

Why is this happening

This is an identified flaw in the logic, which could set the output level as the first input's level, if all the inputs' row numbers are the same as the grid's. This issue will not be observed in Strategy 9.4, as there are no AFB derived metrics in that version.

Solution

Upgrade to Strategy 2020 or a new version, and set the Data Engine Version setting to 12.
The metric value will display at the region level.

Strategy Internal Reference Number for this technical note is KB483976 and DE150215