Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations derfloh on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Case statement in MDX

  • Thread starter Thread starter eo
  • Start date Start date
Status
Not open for further replies.

eo

MIS
Joined
Apr 3, 2003
Messages
809
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:
Code:
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...

Code:
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
 
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!
 
I tried using scopes, but I think I am missing a trick. As explained, the TRIANGULATION dimension hierarchy looks like this:
Code:
> 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...
Code:
CREATE MEMBER CURRENTCUBE.[MEASURES].[Test123]
  AS NULL, FORMAT_STRING = "#,#", VISIBLE = 1;

I then created the two scopes...
Code:
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...
[nosmiley]

EO
Hertfordshire, England
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top