MDX syntax for current year in a Pre-defined condition

[colin_terry]

Hi,

I'm looking for assistance in building a Pre-defined condition in a universe based on an MSAS 2005 OLAP cube, that only returns the current year.

My year is defined as [Time Accounting].[Accounting].[Accounting Year]

I can create a Pre-defined filter that accepts a constant value as follows:

<FILTER KEY="[Time Accounting].[Accounting].[Accounting Year]">

<CONDITION OPERATORCONDITION="Equal">

<CONSTANT CAPTION="2008"/>

</CONDITION>

</FILTER>

and it works fine, however, I would like to make it dynamic.

There is an attribute called current year that is defined as [Time Accounting].[Accounting Year].[Accounting Year].[Current Year].[Value] but when I try and put this into my pre-defined filter nothing is returned:

<FILTER KEY="[Time Accounting].[Accounting].[Accounting Year]">

<CONDITION OPERATORCONDITION="Equal">

<CONSTANT CAPTION="[Time Accounting].[Accounting Year].[Accounting Year].[Current Year].[Value]"/>

</CONDITION>

</FILTER>

Is this possible? What am I doing wrong?

What I'm ultimately aiming for is a rolling 12 month condition.

Thanks,

Colin

[Former Member]

I have exactly the same problem except I'm trying to get "yesterday" date.

My problem is two-fold:

1. I have a "named set" in the cube that contains yesterday's date that doesn't show up in the Universe when I refresh it.

2. I need to use that date as a filter on the report. It looks like the expression suggestion might work, but I'm not sure where to put it in relation to the filter. Do I put it somewhere in the filter or make an object with the expression? More detailed information would help me.

Also, will the expression be able to pull my "yesterday" date from the named set even though I can't see it in the universe?

Your help would be greatly appreciated.

[Former Member]

Hi,

We do not support named sets in OLAP universe.

What I am suggesting is that you create the calculated member that surfaces the yesterday's date in the universe and then define a filter thayt return a boolean as I described in my previous answer.

Normally it should work.

Didier

[colin_terry]

Thanks Didier - I'll give that a go.

Regards,

Colin

[Former Member]

Hi,

In OLAP universe, you cannot define a filter with CAPTION = another universe object.

What you can do is CAPTION = String or CAPTION = @Prompt.

By the way, if you want to achieve what you are requiring, I suggest you create a calculate expression such as:

<EXPRESSION>
IIF([Time Accounting].[Accounting Year.].CurrentMember.Properties("Name") = [Time Accounting].[Accounting Year.].CurrentMember.Properties("Current Year"), 1, 0)
</EXPRESSION>

Then you can define your filter on your new calculated expression and the operator to Equal and CAPTION = 1

Didier