Transforming measure names into values of a dimension

Hi All,

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].

Regards,

Rogerio