Friday 26 April 2013

CUBEVALUE() and CUBEMEMBER() and CUBESET(), oh my!

Excel Cube functions (reference)


Return a value from the cube.
=CUBEVALUE(connection,member_expression1,member_expression2…)
=CUBEVALUE("Cube Connection",$B$4,$B$5,$B$52,$B$50,$A40)
=CUBEVALUE("Sales","[Measures].[Profit]","[Time].[2004]","[All Product].[Beverages]")



Returns a member or tuple from the cube.
=CUBEMEMBER(connection, member_expression, [caption])
=CUBEMEMBER("Cube Connection","[Invoice Date].[Calendar FY-M].&[MAR FY2013])
=CUBEMEMBER("Cube Connection","[Invoice Date].[Calendar FY-M].&["&B6&"]")


Defines a calculated set of members or tuple. Can be used as a member expression by the CUBEVALUE() function.
=CUBESET(connection, set_expression, [caption], [sort_order], [sort_by])

=CUBESET("Cube Connection", "FILTER( [Invoice Date].[Hierarchy].members, [Invoice Date].[Hierarchy].Currentmember.Properties('Key0', TYPED) > "&B47&" AND [Invoice Date].[Hierarchy].Currentmember.Properties('Key0', TYPED) < 65000)", "(Multiple Items)")

=CUBESET("Cube Connection","{[Invoice Date].[Hierarchy].&[2012],[Invoice Date].[Hierarchy].&[JAN FY2013],[Invoice Date].[Hierarchy].&[FEB FY2013],[Invoice Date].[Hierarchy].&[MAR FY2013]}","(Multiple Items)")



Return the Key value from selected member
[Invoice Date].[Hierarchy].Currentmember.Properties('Key0', TYPED)
Posted by at
Labels: ,

5 comments:

  1. Zan21 May 2013 at 16:20

    Hi Paul, can you use this construct in an excel formula?

    [Invoice Date].[Hierarchy].Currentmember.Properties('Key0', TYPED)

    I'm trying to do something similar, in order to return an attribute of a member, but just can't figure out the syntax.

    ReplyDelete
    Replies
    1. Zan23 May 2013 at 10:48

      This comment has been removed by the author.

      Delete
    2. Zan23 May 2013 at 10:50

      I managed to figure it out. The syntax is:
      =CUBEMEMBER("[DIMENSION_NAME].[HIERARCHY_NAME].[ELEMENT_NAME].Parent")

      I didn't realise that it was possible to specify an element at any level of the hierarchy, straight after the hierarchy name, and then traverse back up the tree via .Parent

      I'm just not sure how to handle the slowly-changing dimension scenario, where appears at two different points in a hierarchy...

      Delete
    3. Unknown24 May 2013 at 12:37

      Good to hear you found a solution.

      In the past I've managed to mix the parent child levels of a hierarchy to generate a CUBESET.

      =CUBESET("Cube Connection", "{[Info Date].[Hierarchy].&[2011],[Info Date].[Hierarchy].&[2010],[Info Date].[Hierarchy].&[2012],[Info Date].[Hierarchy].&[JUL FY2013]}", "(Multiple Items)")

      The formula became unwieldy so I modified to include a FILTER on the CUBESET using the Key0 values:

      =CUBESET("Cube Connection","FILTER( [Info Date].[Hierarchy].members, [Info Date].[Hierarchy].Currentmember.Properties('Key0', TYPED) > 30000 AND [Info Date].[Hierarchy].Currentmember.Properties('Key0', TYPED) < 41393)","(Multiple Items)")

      Delete
  2. Unknown16 April 2021 at 05:35

    Any suggestions on how to display multiple values from one category?

    I am trying something like: =CUBEMEMBER("ThisWorkbookDataSet","[Products].[GlblDivDesc].[All].[EQUIPMENT DIVISION].&.[APPAREL DIVISION]")

    To then use it in a CubeValue formula but this seem not to be working.

    Neither when I want to display sales for two categories together such as:

    =CUBEVALUE("ThisWorkbookDataSet","[Products].[GlblDivDesc].[All].[APPAREL DIVISION].&.[EQUIPMENT DIVISION]","[Measures].[Sls Grs Futs Bkgs Qty]")

    ReplyDelete

Subscribe to: Post Comments (Atom)