CASE statements

One of the first things I noticed when I got hold of AS2005 for the first time, well over a year ago now, was that we now had a CASE statement in MDX. The joy! And yet it’s one of those small improvements which so often gets ignored, so I thought I’d mention it. Here’s an example:

WITH
MEMBER MEASURES.MYTEST AS
CASE
WHEN [Measures].[Internet Sales Amount]> 3780000 THEN "HIGH"
WHEN [Measures].[Internet Sales Amount]> 3700000 THEN "MEDIUM"
ELSE "LOW"
END

SELECT {[Measures].[Internet Sales Amount],MEASURES.MYTEST} ON 0,
[Date].[Day of Week].[Day of Week].MEMBERS ON 1
FROM [Adventure Works]

No more nested IIFs! One of the biggest contributions to improved productivity in AS2005 in my opinion…

One thought on “CASE statements

  1. Oh thank you!! It is about time for that one.Half of the measures I end up creating have so many nested iif statements the code is unreadable.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s