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

Question

We use a cube built in SSAS and we would like to show the effect of deltas against a forecast.

So, we have data like this:

Year    Type        Amount
----    ---------   ---------
2008    Forecast    +1000
2008    Delta 1     - 100
2008    Delta 2     - 200
2009    ...

We would like to make a stacked bar chart, so we need data like this:

Year    Type        Amount
----    ---------   ---------
2008    Result      +700
2008    Delta 1     +100
2008    Delta 2     +200

You can see that 'Result' equals the sum of forecast and the deltas, at a particular granularity (in this this example case it is just time, we will also have place as an added dimension).

The catch is we'd like to be able to filter out a delta and have the bar chart update, so then 'Result' would need to then to change so that the sum is still the forecast for that granularity:

Type          Amount
---------   ---------
Result         +900
Delta 1        +100

We could do this in a view, but then we'd have to generate a result for every possibility of delta selects and deselects, which doesn't seem optimal.

Is there a way to do this in the cube? Our best guess is some kind of MDX calculated member but we are open to other solutions as well.

Thanks!

UPDATE 1: The problem I see with a calculated field is that it adds a member (i.e. a column) to the cube, where what we really need to do is add rows with a UNION...but in a cube. Here's some psuedo SQL as an example:

SELECT 
  Year,
  'Result' AS Type,
  SUM(Amount) AS Amount
FROM Table1
WHERE 1=1
  --AND Type 'Delta 2' 
GROUP BY Year

UNION ALL

SELECT 
  Year,
  Type,
  -1*Amount AS Amount
FROM Table1
WHERE Type LIKE 'Delta%'
  --AND Type 'Delta 2'

--Note the commented out WHERE clauses are where you would de-select a delta if desired.

Can that be translated to MDX for an on the fly calculation?

UPDATE 2:

This is the closest I've gotten so far, but it's not perfect:

CREATE MEMBER CURRENTCUBE.[Measures].[Adjusted]
 AS CASE [Measure Type DEV].[Measure Type] /* This is the 'Type' column above */
    WHEN [Measure Type DEV].[Measure Type].&[2] /* Forecast is key 2 */
    THEN ([Measure Type DEV].[Measure Type].&[2],
        [Time].[Year].CURRENTMEMBER,[Measures].[Amount]) 
      + ([Measure Type DEV].[Measure Type].&[3], /* Delta is key 3 */
         [Time].[Year].CURRENTMEMBER,[Measures].[Amount]) END

ajdams ajdams · Accepted Answer · 2010-06-29T13:53:11

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.

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

2 Answers