Transforming measure names into values of a dimension
In this blog post I´ll show how one can take n measure names and turn them into values of a dimension.
The main reason behind this post was this thread , here at SCNhow to create stack chart in webi ? . In which was asked how to create a stacked bar chart when you have 1 dimension and 4 measures.
In order to create a stacked bar chart, as explained in the thread, you need 2 dimensions and 1 measure.
Let me give you a real sample.
Query e-fashion for State, Sales Revenue, Margin and Quantity Sold. Then let´s create a stacked bar chart showing all objects on it. You can´t do it because you got just one dimension and three measures.
But what if you could create a second dimension object, [type], with values : “Sales revenue”, “Quantity Sold” and “Margin” and a measure variable, [Value], which value would be :
— [Sales revenue] for [type] = “Sales revenue”,
— [Margin] for [type] = “Margin” and
— [Quantity Sold] for [type] = “Quantity Sold”
The challenge here is to create the [type] dimension.
For this case what I did was :
1 – Brought another dimension in my original query, one that has 3 values that exists for all values of the first dimension, for this, I used [Year];
2 – Create a dimension variable , [type] = If ([Year]=”2004″;”Margin”;If([Year]=”2005″;”Quantity Sold”;”Sales Revenue”));
3 – Create a measure variable [Value] = If ([type]=”Margin”;Sum([Margin] In ([State])) ForAll ([Year]);If ([type]=”Quantity Sold”;Sum([Quantity sold] In ([State])) ForAll ([Year]);Sum([Sales revenue]In ([State])) ForAll ([Year]))),
4 Create the stacked bar chart using [State], [type] and [Value].