locked
Powerpivot cuberankedmember function on named set returns "All" for caption RRS feed

  • Question

  • I've created a named set that when added to a pivot table looks like this:

    

    I now want to use the set in a report.  When I use the CUBERANKEDMEMBER function I get this:

    Is there any way to get the same display as on the pivot table using the cube functions?  

    Here is what the named set looks like:

    I am using the set name in the CUBERANKEDMEMBER function as a variable so the user can use a slicer to change the content of the report.

    =IFERROR(CUBERANKEDMEMBER("ThisWorkbookDataModel","["&GeoLevel&"]",ROW(A1)),"")

    Thanks for any help you can give!

    Joan

    Tuesday, May 16, 2017 3:02 PM

Answers

  • Are you able to modify the design of the cube at all?

    My guess is that your set is made up of attributes from 3 separate hierarchies. If you look in the "Manage Sets" dialog box there is a hierarchies column and I suspect that it will list "Subregion, Health Link, Care Group".

    If you could create a hierarchy in your cube with these 3 attributes as levels and then use that in your named set it would work the way you want. 

    The problem is that Excel has to return the caption from one of the hierarchies as the caption in the cell, when you have multiple hierarchies in the one set it looks like Excel just grabs the last one. If you create a user defined hierarchy in your dimension this problem goes away. The output will look identical, but internally Excel will be able to call [<dimension>].[<hierarchy>].CurrentMember.Caption and because you are only using one hierarchy it will all work as expected. (I double checked this by comparing how the sets behaved when using the Adventure Works [Product].[Product Categories] hierarchy compared to using the separate Category and Subcategory attributes)


    http://darren.gosbell.com - please mark correct answers

    Thursday, May 18, 2017 7:48 AM

All replies

  • Hi Joan,

    You want to create a table in Excel, whose format looks like the Pivot table, right? If it is, here is an article which describe how to do in details. Please review it and try based on your resource table. If this still resolved your issue, please post your sample table for further analysis. 

    Best Regards,
    Angelia

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 17, 2017 8:48 AM
  • Hi Angelia,

    Thanks for your response.  I have reviewed the article in the past and again now but my question goes beyond the concepts there.  I probably didn't explain it well though.  Essentially when using the CUBERANKEDMEMBER function, to return a member of a named set, the label shown represents the Care Group field shown in the third image above.  Notice the "All" labels in the second image.  What I want to see is how it looks in the pivot table in the first image i.e. the first label should be "Algoma" instead of "All" and the second should be "North Algoma" instead of "All".  The bolding and indenting would be a bonus. 

    A workaround is to have a pivot table on another sheet and use the optional Caption component of the CUBERANKEDMEMBER function to reference each cell.  Not ideal.

    Joan

    Wednesday, May 17, 2017 3:26 PM
  • Are you able to modify the design of the cube at all?

    My guess is that your set is made up of attributes from 3 separate hierarchies. If you look in the "Manage Sets" dialog box there is a hierarchies column and I suspect that it will list "Subregion, Health Link, Care Group".

    If you could create a hierarchy in your cube with these 3 attributes as levels and then use that in your named set it would work the way you want. 

    The problem is that Excel has to return the caption from one of the hierarchies as the caption in the cell, when you have multiple hierarchies in the one set it looks like Excel just grabs the last one. If you create a user defined hierarchy in your dimension this problem goes away. The output will look identical, but internally Excel will be able to call [<dimension>].[<hierarchy>].CurrentMember.Caption and because you are only using one hierarchy it will all work as expected. (I double checked this by comparing how the sets behaved when using the Adventure Works [Product].[Product Categories] hierarchy compared to using the separate Category and Subcategory attributes)


    http://darren.gosbell.com - please mark correct answers

    Thursday, May 18, 2017 7:48 AM