Mark Mrachek - Business Intelligence Blog - Conditional Statements in the MDX WHERE Clause

Wednesday, January 25, 2006

Conditional Statements in the MDX WHERE Clause

I’ve written thousands of lines of MDX code over the years to support web reporting applications and today I discovered something new. Although it probably isn’t new to MDX, it was new to me because I never needed it until today. I was targeting the ascendants of a member and based on the parent of the member, I needed to alter the slice of the cube.

Typically, when I use conditional statements, I create calculated member and handle the logic in the WITH statement. Today’s challenge was different because I was not able to control what calculated members are passed from Panorama to Analysis Services. Instead, I needed to work through the ‘WHERE’ clause to slice my data.

Anyways, I don’t know if I’ll ever need to use it in the future, but it’s nice to know that this functionality exists. Below is a sample MDX statement that uses a conditional statement in the ‘WHERE’ clause. This sample provides little value except to illustrate that you can indeed use conditional statements in ‘WHERE’ clauses.

SELECT [Measures].[Internet Total Product Cost] ON 0,
[Sales Reason].[Sales Reasons].Members ON 1
FROM [Adventure Works]
WHERE (IIF([Sales Territory].[Sales Territory].[Sales Territory Country].[Australia].Parent.Name = “Pacific”, [Sales Territory].[Sales Territory], [Sales Territory].[Sales Territory].[Sales Territory Country].[Australia]))

1/25/2006 10:46:50 PM (Pacific Standard Time, UTC-08:00)

Comments [0] Analysis Services | MDX |

Name
E-mail
Home page

	Remember Me
Comment (HTML not allowed)
Enter the code shown (prevents robots):