MSAS OLAP Universe Pre Defined Filter

[Former Member]

Hi:

We have a Universe based on a MSAS cube. The cube has only three time u201Cdimensionu201D objects u2013 Year, Quarter and Month. All come across as Character data types. The Month object displays the year and the month concatenated together (2009.JUL, for example). What we need to do is to create a pre defined filter in the Universe that will always give us the data for rolling 16 months. So, for example, if we run the report in July, the data would be for April 2009 through July 2010 u2013 of course, if the report is run next month in August, the data would be for May 2009 through August 2010. If anyone can offer any help in what the pre defined filter in the Universe would look like, that would be much appreciated.

Thanks!

[Former Member]

Thanks, Didier. Using the Property instead of member value did the trick.

-Nishant.

[Former Member]

Hi, Gurus.

Could you help this person? I would like to help but I don't know how to handle Olap Universes. Her is his question:

[Former Member]

Hi Didier:

Just wondered if you had any further input into resolving this problem. Thanks!

-Nishant

[Former Member]

Hi,

In order to have the expression working correctly the values need to be correctly sorted thus implying to compare numeric values rather than am^habetic values.

In my sample I used numeric values to satisfy the expression.

In your sample the months are alphabetical thus explaining the incorrect results.

Can you try to use the key or any other attribute to to the comparison rather than the caption?

Didier

[Former Member]

Hi Didier:

Thanks again for your response u2013 the revised filter works without any syntax errors.

There are two problems. Firstly, we need it to dynamically calculate the 16 month period based on the month when the query is run, instead of prompting the user to enter the ending period as it does now.

Secondly, the dates that it brings back donu2019t appear to be correct. Here is what it brings back, if July 2010 is entered for the prompt:

2009.MAR

2009.MAY

2009.NOV

2009.OCT

2009.SEP

2010.APR

2010.AUG

2010.DEC

2010.FEB

2010.JAN

2010.JUL

What I would expect it to bring is the following:

2009.APR

2009.AUG

2009.DEC

2009.JUL

2009.JUN

2009.MAY

2009.NOV

2009.OCT

2009.SEP

2010.APR

2010.FEB

2010.JAN

2010.JUL

2010.JUN

2010.MAR

2010.MAY

Here is the exact Filter that I am using:

<FILTER EXPRESSION="IIF([Time].[H1].currentmember.member_value &lt;= STRTOMEMBER(&quot;@Prompt('Select period','A','Time\Month',Mono,primary_key,Persistent)&quot;).member_value AND [Time].[H1].currentmember.member_value &gt;= STRTOMEMBER(&quot;@Prompt('Select period','A','Time\Month',Mono,primary_key,Persistent)&quot;).lag(16).member_value ,1,0)">

<CONDITION OPERATORCONDITION="Equal"><CONSTANT CAPTION="1"></CONSTANT></CONDITION></FILTER>

Also, it does not seem to change the number of months returned even if I change the lag(16) to lag(15).

BTW, I installed the Adventure Works cube and tested the query there u2013 it works as expected when I use it there, as you had suggested in thefirst example you gave me.

Thanks again for your help with this.

Regards,

Nishant.

[Former Member]

Hi Didier:

First of all, thanks very much for your response! I tried your solution and the filer expression parsed just fine in Designer. However, I get the following error when I run the query:

A database error occured. The database error text is: Failed to execute MDX query. Reason: The component Microsoft OLE DB Provider for Analysis Services 2005 returned server error (Query (1, 76) The CURRENTMEMBER function expects a hierarchy expression for the 0 argument. A member expression was used.). (WIS 10901)

The filter expression I am using is:

<FILTER EXPRESSION="IIF([Time].[H1].[Month].currentmember.member_value &lt;= STRTOMEMBER(&quot;@Prompt('Select period','A','Time\Month',Mono,primary_key,Persistent)&quot;).member_value AND [Time].[H1].[Month].currentmember.member_value &gt;= STRTOMEMBER(&quot;@Prompt('Select period','A','Time\Month',Mono,primary_key,Persistent)&quot;).lag(16).member_value ,1,0)"><CONDITION OPERATORCONDITION="Equal"><CONSTANT CAPTION="1"></CONSTANT></CONDITION></FILTER>

Lastly, what we need to do is to have the filter detect the current month based on the date the query is run, and then calculate the 16 months back from there, instead of prompting the user.

Again, thanks so much for your help!

Regards,

Nishant.

[Former Member]

Hi,

The error message tell you taht you have to use a hierarchy with CURRENTMEMBER.

In your expression you used a level instead of a hierarchy.

So your expression must be:

<FILTER EXPRESSION="IIF([Time].[H1].currentmember.member_value &lt;= STRTOMEMBER(&quot;@Prompt('Select period','A','Time\Month',Mono,primary_key,Persistent)&quot;).member_value AND [Time].[H1].currentmember.member_value &gt;= STRTOMEMBER(&quot;@Prompt('Select period','A','Time\Month',Mono,primary_key,Persistent)&quot;).lag(16).member_value ,1,0)">
<CONDITION OPERATORCONDITION="Equal"><CONSTANT CAPTION="1"></CONSTANT></CONDITION></FILTER>

You can also replace the expression lag(16) by lag(@prompt expression) to let users choose the number rolling periods if neede.

Regards,

Didier

[Former Member]

Hi,

I found a solution that works with AdventureWorks.

So you can try this example and replace the dimension definition by the one you are using:

<FILTER EXPRESSION="IIF([Date].[Calendar].currentmember.member_value &lt;= STRTOMEMBER(&quot;@Prompt('Select period','A','Date.Calendar\Month',Mono,primary_key,Persistent)&quot;).member_value AND [Date].[Calendar].currentmember.member_value &gt;= STRTOMEMBER(&quot;@Prompt('Select period','A','Date.Calendar\Month',Mono,primary_key,Persistent)&quot;).lag(16).member_value ,1,0)">
<CONDITION OPERATORCONDITION="Equal"><CONSTANT CAPTION="1"></CONSTANT></CONDITION></FILTER>

Don't forget to escape characters in the XML expression as I did in my sample.

Use also the "primary_key" paramter in the prompt to transform it in a member.

Last I used "member_value" to be sure that the values are correctly sorted.

Regards,

Didier