Skip to Content
author's profile photo Former Member
Former Member

IIF in dimension formula

Dear All

I'm looking for a good explanation regarding the proper dimension formula in hierarchy.

For formula for H1

IIF(Account.H2.CurrentMember is Account.H2.(All Account.H2) And

Account.H3.CurrentMember is Account.H3.(All Account.H3),

(Account.H1.IFRSTRATE/Account.H1.WKSYR)*Account.H1.WKSCURMTH, NULL)

I don't understand why I have to take care of other hierarchy (H2 or H3) when I'm in H1 by comparing the CurrentMember with (All Account.H (not 1)) ?

note: I changed character square bracket with '(' and ')' to avoid formatting.

Regards

Halomoan

Edited by: Halomoan Zhou on Jul 3, 2008 8:13 AM

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • author's profile photo Former Member
    Former Member
    Posted on Jul 03, 2008 at 06:39 AM

    To answer your second question; Microsoft MDX is a most terrible language.

    I'm not real good with MDX but I can tell you that this has no chance of working!

    You can go out to the Microsoft site and find everything you need for the MDX syntax.

    http://msdn.microsoft.com/en-us/library/aa216767(SQL.80).aspx

    There are SAP BPC documents on writing Logic.

    do a search here on "business planning and consolidation logic"

    https://www.sdn.sap.com/irj/sdn/advancedsearch?cat=sdn_all&query=businessplanningandconsolidation+logic&adv=true&sdn_author_name=&sortby=cm_rnd_rankvalue

    https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/80ffae4b-99d5-2a10-4e9f-96149969aabb

    https://www.sdn.sap.com/irj/sdn/go/portal/prtroot/docs/library/uuid/00cad586-6085-2a10-1e9e-94cbb932ad0d

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Former Member

      It has a chance of working, but you have to follow very strict the document of MDX dimension formula guide when you have more then 1 hierarchy in your account dimension. If you only have 1 hierarchy it is much easier and you don't have to write very complex formulas to handle H2 or H3.

      I had a lot of problems with dimension formulas when I had a account dimension with 2 hierarchies at a customer migrating from 4 to 5. I had to rewrite all the formulas, but using the document about mdx formulas that was posted in this thread before me I got everything working. When I didn't follow the guidelines I had a lot of trouble with hierachy calculations in the parenth2 accounts. A lot of values where correct on the basemembers but were doubled or tripled in the parent members. But after editingg the formulas based on the guidelines this was fixed.

      -Joost

  • author's profile photo Former Member
    Former Member
    Posted on Jul 03, 2008 at 03:50 PM

    What you now have looks good. What you had in the question last night before editing it was very different and I don't recall what exactly it was to comment back to you on.

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jul 03, 2008 at 06:17 PM

    Below some explanation why the setup of MDX formulas with multiple hierarchies is necessary (coming from the document that Joost is referring to):

    OutlookSoft 5 implements multiple hierarchies as separate dimensions in AS. For example if the account dimension has two hierarchies AS will create two dimensions named account.h1 and account.h2. The top member of these dimensions will be named [all account.h1] and [all account.h2.] All members of the dimension roll up to this top level member. An understanding of this concept is important because it relates to how MDX formulas need to be written.

    Suppose we want to define a formula as following in a dimension with 3 hierarchies:

    #CF_ST_FIN_RATE= ((#IFRSTRATE/#WKSYR)*#WKSCURMTH)

    Where IFRSTRATE, WKSYR and WKSCURMTH are base members of hierarchy H1 of Account dimension, and CF_ST_FIN_RATE is a dimension formula in H1 of account dimension.

    The correct formula for H1 is

    IIF(Account.H2.CurrentMember is Account.H2.[All Account.H2] And

    Account.H3.CurrentMember is Account.H3.[All Account.H3],

    (Account.H1.IFRSTRATE/Account.H1.WKSYR)*Account.H1.WKSCURMTH, NULL)

    An example of this formula for H2:

    IIF(Account.H1.CurrentMember is Account.H1.[All Account.H1] And

    Account.H3.CurrentMember is Account.H3.[All Account.H3],

    (Account.H2.IFRSTRATE/Account.H2.WKSYR)*Account.H2.WKSCURMTH, NULL)

    Note:

    1. All variables need to be fully qualified with dimension name and hierarchy name, such as account.h1.IFRSTRATE. Otherwise AS returns a syntax error.

    2. When defining a formula on H1, you have to specify that formula returns a valid result only if the current members on other hierarchies are at the top level of each hierarchy.

    Alwin

    Add a comment
    10|10000 characters needed characters exceeded

    • Former Member Jeff Hattendorf

      Hi Jeff

      Do you know where i'm wrong ?

      I tried to add this formula :

      IIF(Account.H2.CurrentMember is Account.(all account.H2) AND Account.H3.CurrentMember is Account.(all account.H3),overtime_hours/TotalBaseHours,NULL)

      and I got error like this:

      cannot find dimension member ("ACCOUNT.H2")

      cannot find dimension member ("ACCOUNT.H3")

      is my BPC cannot parse (all account.H2) and (all account.H3)? Does it work on your side? What is the correct syntax?

      Sorry, this is my first time using more then 1 dimension.

      Thanks

      ps: I replace square bracket with "(" and ")" to avoid formatting. The formatting is very annoying. any workarround ?

      Halomoan

      Edited by: Halomoan Zhou on Jul 7, 2008 12:12 PM

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.