OLAP Universe Filter for Constant

[Former Member]

Hello

I am trying to create a universe filter. We have a BO OLAP Universe on top of MSAS 2008. We are at BOXI 3.1 SP2/FP2.2

<FILTER KEY="[Date].[Calendar Date].[Date]"><CONDITION
OPERATORCONDITION="InList"><CONSTANT
CAPTION="[Today].item(0).MEMBER_CAPTION"/></CONDITION></FILTER>

Where [Today] is a calculated member.

This one parses fine, however when it comes to MDX which i capture via

profiler

WITH SET NS28B9BE741FD446A5A0414EA718F8FC_0 AS Filter([Date].[Calendar
Date].levels(4).allmembers,((([Date].[Calendar
Date].currentmember.properties("CAPTION")="[Today].item
(0).MEMBER_CAPTION"))))


SELECT { [Measures].DefaultMember } ON COLUMNS ,
NS28B9BE741FD446A5A0414EA718F8FC_0 DIMENSION PROPERTIES
MEMBER_UNIQUE_NAME, MEMBER_CAPTION ON ROWS FROM [My Cube]

This returns empty. However it works fine and returns value if i remove "" wrapping

[Today].item(0).MEMBER_CAPTION and run this query in SQL Server Management Studio ( MDX query)

However i cannot introduce the same in the filter - if i try it gives

me a parse error.

i.e the following doesnt help

<FILTER KEY="[Date].[Calendar Date].[Date]"><CONDITION
OPERATORCONDITION="InList"><CONSTANT
CAPTION=[Today].item(0).MEMBER_CAPTION/></CONDITION></FILTER>

Can you kindly advise if you have seen similar errors and what i can do possibly in this case to workaround this?

Edited by: ramaks on Oct 26, 2010 2:35 AM

Edited by: ramaks on Oct 26, 2010 2:36 AM

Edited by: ramaks on Oct 26, 2010 2:37 AM

[Former Member]

Hi,

In an OLAP filter, CAPTION parameter can only use constants or hierarchy values not calculated members.

By the way you can solve your problem in a different way like this:

<FILTER EXPRESSION="IIF([Date].[Calendar Date].CurrentMember.Name = [Today].item(0).MEMBER_CAPTION, 1, 0">
  <CONDITION OPERATORCONDITION="Equal">
    <CONSTANT CAPTION="1"/>
  </CONDITION>
</FILTER>

To use this filter you need to add the date dimension in your query:

[Date].[Calendar Date].[Date]

Regards

Didier

[Former Member]

Hi Didier,

Thank you for your response. That was exactly my original design - but it has performance implications for us. Please see an earlier thread of mine.

In this Case the MDX will become as below.

Which means every additional dimension i add creates multiple cross joins, and the filter will go through all possible cross-join combination to filter out that one record for that particular date. My original thread explains this more clearly.

Is there no other way to implement such a filter?

WITH

MEMBER [Measures].[2FF3DF31-5D02-4A9B-A8,BC,E7,25,C2,30,1B,61] AS

IIF

(

[Date].[Calendar Date].CurrentMember.Properties("CAPTION")

=

[Today].Item(0).Member_Caption

,1

,0

)

SELECT { [Measures].DefaultMember } ON COLUMNS ,

,NON EMPTY

Filter

(

CrossJoin

(

AddCalculatedMembers

(

[Date].[Calendar Date].[Date].MEMBERS

)

)

,

[Measures].[2FF3DF31-5D02-4A9B-A8,BC,E7,25,C2,30,1B,61] = 1

)

DIMENSION PROPERTIES

MEMBER_UNIQUE_NAME, MEMBER_CAPTION ON ROWS FROM [My Cube]

Edited by: ramaks on Oct 26, 2010 4:26 PM