Create an MDX calculated member to make a stacked bar chart using data with total and reductions

tags:

mdx
ssas

views:

156

answers:

+2 Q:

Create an MDX calculated member to make a stacked bar chart using data with total and reductions

+1 A:

There is no real great way in MDX to do this but when I had to create forcasts I used data mining to accomplish this task with this article as my main referencing point:

http://cwebbbi.spaces.live.com/Blog/cns!7B84B0F2C239489A!2795.entry?a665aac0&wa=wsignin1.0

I hope this helps and it may be quicker than attempting to solve it with straight MDX.

ajdams 2010-06-29 13:53:11

Very interesting, this actually gets to another question I've been interested in: how do you generate the forecast data (instead of loading it manually)? So thanks for the link. I'm still looking at MDX to do what is essentially a 'running sum'. Thanks for your answer!

Stin 2010-06-30 13:47:09

@Stin: Is loading it not an option? I think in the long run having it loaded into the cube would be more beneficial so that's why I had suggested looking at that article =).

ajdams 2010-06-30 15:11:38

@ajadams: Loading is definitely an option, in fact we're loading it now (In the example above, the forecast is 1000). The issue is we have a selection of projects to choose from (the 'deltas' above) and want to see their effect on the forecasts we've loaded in a bar chart (which means the forecast has to be reduced the proper amount before the deltas are loaded back on).

Stin 2010-06-30 15:32:08

+1 A:

In my last update to the question (Update 2) I asked how to do a SQL like union in MDX...which led me in the right direction but is the wrong way of thinking about it (I'm still new to MDX).

To add that Result 'Row' I needed to add a calculated dimension (which can be put ON ROWS), that returned the All level, which does the summation I was looking for, AND I needed to add a new calculated dimension that would do the case statement (flip the reductions in one case, subtract the reductions from the total in the other).

Stin 2010-07-12 12:37:25

Outstanding. I'd read this a couple times and wondered how to do this, glad you figured it out. I regret I have only 1 +1 to give..

Meff 2010-07-22 10:26:21

Great, glad to hear it helps. I don't know where you are in your MDX learning but the book MDX Step by Step was recommended as a good beginners book and was what helped me switch my mindset to answer this question.

Stin 2010-07-22 15:28:34

ansaurus

tags:

views:

answers:

Create an MDX calculated member to make a stacked bar chart using data with total and reductions

related questions