Tuesday, March 20, 2012

Report Builder MDX on filter

Please interpret this MDX statement generated by Report Builder for me. I am not sure why it is returning all years in cube when I just want 2005:

SELECT

{ ( Extract( filter( { [Accounting Date].[MDY Date].Levels(1).Members * { [Accounting Date].[Date].[Calendar] } }, ( ( [Accounting Date].[MDY Date].CurrentMember.membervalue >= "09/01/2005" ) AND ( [Accounting Date].[MDY Date].CurrentMember.membervalue <= "09/30/2005" ) ) ), [Accounting Date].[MDY Date] ) , * ) } on 0

FROM

[Cube]

It is filtering the data on the month and day portion of the date range just fine, just not filtering on the year. Same report in Reporting Services generates different MDX and returns 2005 only, so this rules out cube problem.

Could you please add CDate() around the begin and end dates, so as comparisons are made using the proper time ordering?

For instance:

... >= CDate("09/01/2005") ...

No comments:

Post a Comment