SYMPTOM:
Subtotals for non-aggregatable metrics are not the values returned for the all element for MDX databases. An example would be a count distinct metric. The subtotal is a straight sum of the values of the metric at lower levels.
Example data demonstrating this below can be seen using two attributes call product and component which are in a many to many relationship.
Have the relationship defined by the following table.
ProductID ComponentID
1 1
1 2
2 2
2 3
A count distinct at the level of Product would return the following with Strategy Subtotaling.
Product Count
1 2
2 2
Total 4
The Appropriate count distinct value would be 3 however Strategy is unable to calculate that as the information about what component is being counted for each product is not brought back to Strategy for that report.
CAUSE:
This is working as designed. Strategy calculates all of its subtotals internally based on the lowest level information returned on the report. This is due to the subtotal behavior being originally designed before support for MDX database instances was introduced.
ACTION:
The solutions are to have reports at different levels and then combine the results on a Report Services Document or to move the calculation into the analytical engine.
