Skip to Content

MDX syntax for current year in a Pre-defined condition

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

Add a comment
10|10000 characters needed characters exceeded

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Dec 18, 2008 at 03:07 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Dec 18, 2008 at 05:05 PM

    Thanks Didier - I'll give that a go.

    Regards,

    Colin

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Dec 19, 2008 at 12:19 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

    • 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

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.