Thursday, January 06, 2011

How to Use Excel's CUBESET Function |

For Cube function reporting in Excel, one function that doesn’t get translated during a pivot table formula convert is CUBESET.

Here is a way to get the leaves (lowest levels) of a dimension using the cube set formula in Excel 2007/2010.

=CUBESET(connectionstring,filter([Account].[AcctHier].members,isleaf([Account].[AcctHier].currentmember))","Account Leaves")

To retrieve the members, create a list of numbers ending at the CUBESETCOUNT() of above, and use the following formula:


CUBESET(connectionstring,filter([Account].[AcctHier].members,isleaf([Account].[AcctHier].currentmember))","Account Leaves")


Retrieves the first member of the set.

How to Use Excel's CUBESET Function |

No comments: