Richard Abrahams


I have an MDX dataset auto-created by SQL 2005, I want to restrict it to anything greater than year 2004. I could get the single year 2004 by replacing ALLMEMEBERS with &[2004] : (i.e. [Incident Date].[Year].&[2004]) but don't know how to do GREATER THAN in MDX.

WITH MEMBER [Measures].[ParameterCaption] AS '[Incident Date].[Year].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Incident Date].[Year].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Incident Date].[Year].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Incident Date].[Year].ALLMEMBERS ON ROWS FROM [Incidents]




Re: MDX : WHERE Greater than year 2004

Christopher Webb


Try the following:

WITH MEMBER [Measures].[ParameterCaption] AS '[Incident Date].[Year].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Incident Date].[Year].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Incident Date].[Year].CURRENTMEMBER.LEVEL.ORDINAL'
SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS ,
{[Incident Date].[Year].&[2004]:null}
ON ROWS FROM [Incidents]

This should return every member from 2004 onwards on [Incident Date].[Year].

HTH,

Chris