When you create variables in web intelligence, they must be assigned a qualification. This qualification assigns a “strong” type of either a measure, or a dimension (or a detail) kind of object to your variable. This strong tying has a profound impact on how webi handles aggregation of values in the variable you create, and can be the cause of many headaches for report developers trying to handle complex reporting requirements. The following example uses the e-Fashion universe for illustration purposes:
Imagine you need to produce a report that contains a reporting year in column one. Column 2 should display the revenue for the year in prior year, and a revenue state for the current year, and the revenue by state in the third column, for the current year only. Seems fairly simple, right. You can create a fairly simple variable in webi to satisfy the condition for column 2: If([Year]=”2006″;[State];[Sales revenue]) (If the year is 2006, the current year, display the State, else, display the revenue). Your variable will have to have a qualification, and will default to a dimension most likely. When you place your variable in the report, you will see a result far from what you were looking for:
What is happening is that webi is treating the variable value as a dimension and is not aggregating it across the states for the prior years. So even though the states are not displayed, we still get multiple rows for the years, as if the states were included in the calculation context.
Try to change the variable qualification to a measure, and you get the flip side of the problem: now you get the infamous #MULTIVALUE error because webi is treating the list of States like a measure and is unable to aggregate multiple States per Year.
What can you do? Using the ForEach webi function, you can enforce the proper calculation context for each part of your variable and “loosen” the tight typing of your variable due to its qualification:
If([Year]=”2006″;[State];[Sales revenue] ForEach ([Year]))
Make sure you set the variable qualification to a dimension, and now webi will be able to properly calculate the proper aggregation for each year, as expected.
Add a similar formula as your third column variable (If([Year]=”2006″;[Sales revenue] ForEach([State]);”")), apply some formatting (with NumberFormat()), and there you go, requirement met.