locked
Get the expression of a calculated member? RRS feed

  • Question

  • Hi,

    Is there a way to get the expression of a calculated member in MDX? For example, I created a calculated member:

    CREATE MEMBER [Product].[Product].[#Part#] as 'Aggregate({[Product].[All].[Drink].[Pepsi], [Product].[All].[Drink].[Coke]})'

    In my query I would like to get the expression ("Aggregate({[Product].[All].[Drink].[Pepsi], [Product].[All].[Drink].[Coke]})") of the #Part# member so that I can parse it and get a list of items being selected. How do I do that?

    Thank you!
    Thursday, December 17, 2009 2:03 AM

Answers

  • There is not really an API for doing this and I don't think trying to parse the MDX is really a good idea.

    Possibly a better solution would be to create a named set which you aggregate and then you can just reference that set in your queries.

    eg.

    CREATE SET [Part Set] as {[Product].[All].[Drink].[Pepsi], [Product].[All].[Drink].[Coke]};

    CREATE MEMBER [Product].[Product].[#Part#] as Aggregate([Part Set]);


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Marked as answer by HJCHEN Friday, December 18, 2009 10:05 PM
    Thursday, December 17, 2009 2:22 AM

All replies

  • Did you try this? WITH MEMBER allows creation of calculated member at MDX level.

    WITH MEMBER [Product].[Product].[#Part#] as 'Aggregate({[Product].[All].[Drink].[Pepsi], [Product].[All].[Drink].[Coke]})'

    Select [Product].[Product].[#Part#]  on columns
    From YourCube


    Thanks, Ashok -http://dugaputiashok.blogspot.com/
    Thursday, December 17, 2009 2:06 AM
  • There is not really an API for doing this and I don't think trying to parse the MDX is really a good idea.

    Possibly a better solution would be to create a named set which you aggregate and then you can just reference that set in your queries.

    eg.

    CREATE SET [Part Set] as {[Product].[All].[Drink].[Pepsi], [Product].[All].[Drink].[Coke]};

    CREATE MEMBER [Product].[Product].[#Part#] as Aggregate([Part Set]);


    http://geekswithblogs.net/darrengosbell - please mark correct answers
    • Marked as answer by HJCHEN Friday, December 18, 2009 10:05 PM
    Thursday, December 17, 2009 2:22 AM
  • Hi Ashok,

    Thank you for your reply. Unfortunately in my case, the #Part# member and the query cannot be created at the same time.
    Friday, December 18, 2009 9:59 PM
  • Thank you, Darren. Creating a named set solved my problem! I can use [Part Set] to get the items being selected.

    This is how I use the [Part Set]:

    IIF([Product.Product].CurrentMember.Name = '#Part#',
         sum([Part Set], [Measures].[SomeMeasure]),
         ......(do something else)....
        )

    Thank you very much for the help!
    Friday, December 18, 2009 10:05 PM
  • Thank you, Darren. Creating a named set solved my problem! I can use [Part Set] to get the items being selected.

    This is how I use the [Part Set]:

    IIF([Product.Product].CurrentMember.Name = '#Part#',
         sum([Part Set], [Measures].[SomeMeasure]),
         ......(do something else)....
        )

    Thank you very much for the help!

    Actually there is a more efficient way of doing this, if you are using this in the cube's calculation script. Doing a scope statement would be the preferred approach.

    SCOPE ([Product].[Product].[#Part#]);
       This = Aggregate([Part Set]);
    END SCOPE;

    If it is used dynamically in a query, it is better to do a member comparison that a string comparison on the name

    IIF([Product].[Product].CurrentMember IS [Product].[Product].[#Part#],
         sum([Part Set], [Measures].[SomeMeasure]),
         ......(do something else)....
        )

    All three variations would produce the same result, but the SCOPE and IS comparison will be more performant.
    http://geekswithblogs.net/darrengosbell - please mark correct answers
    Sunday, December 20, 2009 12:53 PM
  • Hi Darren,

    It's used dynamically in a query. I will change the name string comparison to IS. Thanks again for your help!
    Monday, December 21, 2009 7:57 PM