Case statement in MDX

[eo]

I have been battling with this for over a week now, and am now ready to admit defeat and ask for help!!

I have an MDX expression which creates a measure (either 1 or null) if a condition is met. I got this working, but forgot that it should apply to more than one dimension attribure hierarchy. Now I am struggling to put it all together and it should work no matter which one of three dimension attribure hierarchies are selected.

The dimension looks like this:

Dimension - TRIANGULATION
  Attribute - DevYear
  Attribute - DevQuarter
  Attribute - DevMonth

Or Hierarchy - TRIANGULATE
               > DevYear
                 > DevQuarter
                   > DevMonth

I am thinking I need to use a CASE statement to code the MDX that will ultimately return a 1 or NULL irrespective of whether DevYear, DevQuarter or DevMonth is selected.

How do I define this? The MDX needs to know to do A if [TRIANGULATION].[DevYear] is selected, do B if [TRIANGULATION].[DevMonth] is selected, and do C is [TRIANGULATION].[DevQuarter] is selected. I tried the following, but this does not work...

CASE
    WHEN [TRIANGULATION].[DevYear] IS TRUE THEN IIF(expr, true, false)
    WHEN [TRIANGULATION].[DevQuarter] IS TRUE THEN IIF(expr, true, false)
    WHEN [TRIANGULATION].[DevMonth] IS TRUE THEN IIF(expr, true, false)

Any ideas if this is indeed even possible

EO
Hertfordshire, England

 

[MDXer]

you utilize MDX scripts which will allow you to apply a scope to your calculations. You would write an MDX script for each Case.

MDX Scripts are used in conjunction with your Calculations but have a slightly different format. I would recommend you get Spoffords MDX book, there is probably lots of MDX scripting stuff to be found on google.


Good Luck!

 

[eo]

I tried using scopes, but I think I am missing a trick. As explained, the TRIANGULATION dimension hierarchy looks like this:

> ProcessingYear
 > ProcessingQuarter
  > ProcessingMonth

To test scopes I employed a very simple test. I first created a calculated measure which will be used within the scopes...

CREATE MEMBER CURRENTCUBE.[MEASURES].[Test123]
  AS NULL, FORMAT_STRING = "#,#", VISIBLE = 1;

I then created the two scopes...

SCOPE([Triangulation].[ProcessingYear].[All]);
[MEASURES].[Test123] = 
1000;
END SCOPE;

SCOPE([Triangulation].[ProcessingMonth].[All]);
[MEASURES].[Test123] = 
1;
END SCOPE;

...when I view the test measure Test123, I expect 1000 to apply when I slice by ProcessingYear, and 1 to apply when I slice the cube by ProcessingMonth

BUT this does not work. 1 seems to apply when I slice by ProcessingYear, and I only see nulls when I slice by ProcessingMonth.

Can multiple scope statements be applied to various levels in a single hierarchy as I am trying to do here, I.e. to [Triangulation].[ProcessingYear].[All]) and to [Triangulation].[ProcessingMonth].[All]?

Does the problem lie with using [All] as opposed to a specific member?

Please help...


EO
Hertfordshire, England