Monday, July 20, 2009

"in between" filters for MDX sources

Fiddling around with some more functional options we've all come to know, like and use frequently, I found that "in between" filters in answers requests going against MDX sources (Essbase, MS Analysis server etc) don't work as expected.

To showcase this, I have created a simple request. Planning and Current numbers by Fiscal Month within Europe. And I'm interested in all months between Q1 2009 and Q4 2009:



Checking the results tab we see that it's not necessarily what we'd expect:



Doing a full data scroll reveals that basically all Fiscal Month members are being pulled up.

Here's the MDX from the log:

With
set [Geo3] as 'Filter([Geo].Generations(3).members, (([Geo].CurrentMember.MEMBER_ALIAS = "Europe" OR [Geo].CurrentMember.MEMBER_Name = "Europe")))'
set [Time Period3] as 'Filter([Time Period].Generations(3).members, ( NOT (([Time Period].CurrentMember.MEMBER_ALIAS < "Q1-09" OR [Time Period].CurrentMember.MEMBER_Name < "Q1-09"))) AND ( NOT (("Q4-09" < [Time Period].CurrentMember.MEMBER_ALIAS OR "Q4-09" < [Time Period].CurrentMember.MEMBER_Name))))'
set [Time Period4] as 'Generate({[Time Period3]}, Descendants([Time Period].currentmember, [Time Period].Generations(4),SELF), ALL)'
member [Scenario].[MS1] as 'AGGREGATE(crossjoin {[Geo3]},Scenario.[Current])', SOLVE_ORDER = 100
member [Scenario].[MS2] as 'AGGREGATE(crossjoin {[Geo3]},Scenario.[Planned])', SOLVE_ORDER = 100
select
{ [Scenario].[MS1],
[Scenario].[MS2],
[Scenario].[MS3]
} on columns,
NON EMPTY {{[Time Period4]}} properties ANCESTOR_NAMES, GEN_NUMBER on rows
from [Sample.Sample]


And here's the important bit:

set [Time Period3] as 'Filter([Time Period].Generations(3).members, ( NOT (([Time Period].CurrentMember.MEMBER_ALIAS < "Q1-09" OR [Time Period].CurrentMember.MEMBER_Name < "Q1-09"))) AND ( NOT (("Q4-09" < [Time Period].CurrentMember.MEMBER_ALIAS OR "Q4-09" < [Time Period].CurrentMember.MEMBER_Name))))'

I've opened an SR for this since there's no mentioning on metalink, the release notes or the new features guides.

Cheers,
Christi@n

No comments:

Post a Comment