SYMPTOM
After migrating to Strategy 9.3.x and above some MDX based reports with derived metrics return null as value. These derived metrics are derived from other metrics that use attribute forms containing numeric data.
STEPS TO REPRODUCE
Follow these steps to reproduce in a Strategy Tutorial project
1. Create a report with a derived metric based on Date Year attribute form:
Actual Year: Year@ID
2. Create a second metric based on previous metric:
LastYear: ((Actual Year)-1)
After upgrade to 9.3.x 'Last Year' metric always return blank but first metric always return the correct value.
CAUSE
In Strategy 9.3.x and later, arithmetic operations cannot be carried out on data of type other than numeric. The attribute form on which the derived metric is based is of string type, and the metric formula includes a minus function. This results in null values being returned.
WORKAROUND
Make sure that Strategy attribute form is cast as numeric in its definition. Then if source is MDX, mapping should correspond to numeric attribute (need to be cast to same type) as in following example:
Using MDX Catalog Editor --> Column definition
