INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Case statement in MDX

Case statement in MDX

(OP)
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

RE: Case statement in MDX

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!

RE: Case statement in MDX

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close