Excel CUBESET Function
The CUBESET function defines a calculated set of members or tuples by sending a set expression to the cube on the server. The function can extract all unique values from a set expression, and it offers options for sorting.
Syntax
=CUBESET(connection, set_expression, [caption], [sort_order], [sort_by])
Arguments
- connection (required): A text string of the name of the connection to the data model (cube).
- set_expression (required): A text string of a set expression that results in a set of members or tuples. Alternatively, set_expression can be a cell reference to an Excel range that contains one or more members, tuples, or sets included in the set.
- caption (optional): A text string that overwrites the caption which is defined from the cube.
- sort_order (optional): An integer from 0 to 6, representing the type of sort to perform. The default value is 0.
-
Integer Enumerated constant Description Argument sort_by 0 or omitted SortNone Leaves the set in its existing order Ignored 1 SortAscending Sorts set in ascending order by sort_by Required 2 SortDescending Sorts set in descending order by sort_by Required 3 SortAlphaAscending Sorts set in alpha ascending order Ignored 4 Sort_Alpha_Descending Sorts set in alpha descending order Ignored 5 Sort_Natural_Ascending Sorts set in natural ascending order Ignored 6 Sort_Natural_Descending Sorts set in natural descending order Ignored - sort_by (optional): A text string of the value (MEASURE) by which to sort. This argument is ignored unless you set 1 or 2 as sort_order. For example, to obtain the region with the highest sales, use set_expression to define a set of regions, and set sort_by as the sales measure.
Return Value
The CUBESET function defines a calculated set.
Function Notes
- CUBESET results in a set containing the items defined by set_expression in the specified sort_order by sort_by.
- The #GETTING_DATA… message will show when the CUBESET function evaluates and before all the data is retrieved.
- CUBESET returns the #NAME? error value if:
- connection is not a valid workbook connection stored in the workbook, e.g., ThisWorkbookDataModel;
- Online Analytical Processing (OLAP) server is unavailable, not running, or returned an error message.
- CUBESET returns the #VALUE! error value if:
- sort_order is set to 1 or 2 that requires sort_by, and the argument is omitted;
- set_expression is longer than 255 characters. Note: You can enter the text string in a cell and then reference the cell as the argument as a workaround.
- CUBESET returns the #N/A error value if:
- set_expression is not valid;
- The set contains one or more members with a different dimension than the other members.
- Arguments of CUBESET, except sort_order and cell references, should be closed with double quotes ("").
- Names of members, dimensions or tables, etc. you write in set_expression should be wrapped in square brackets ([ ]).
Example
I have a table here named “sales2021” which includes the information of sales and rating of different products of 2 categories across the year 2021. To use the CUBESET Function to define a calculated set (total sales) of products in different months, you should first add the data from this table to the Data Model in the existing workbook, its name will always be ThisWorkbookDataModel. Then please copy or enter the formula below in cell where you want to get the totals (here I will get the total of specific products in a month as an example), and press Enter to get the result:
=CUBESET("ThisWorkbookDataModel",($K$5,$J$5,$I$5,$H$5),"Grand Total")
Where
$K$5 =CUBEMEMBER("ThisWorkbookDataModel","[sales2021].[Product].&[Tights]")
$J$5 =CUBEMEMBER("ThisWorkbookDataModel","[sales2021].[Product].&[Socks]")
$I$5 =CUBEMEMBER("ThisWorkbookDataModel","[sales2021].[Product].&[Shorts]")
$H$5 =CUBEMEMBER("ThisWorkbookDataModel","[sales2021].[Product].&[Caps]")
√ Note: The cell contains a CUBESET formula only displays the caption you specified. The set is stored invisibly behind the cell. CUBESET is useless by itself. In the formula above, the cell references are the results returned by CUBEMEMBER function.
Related functions
The CUBEMEMBER function retrieves a member or tuple from a cube if it exists. Otherwise, a #N/A error value will be returned.
The CUBEVALUE function returns an aggregated value from a cube filtered by multiple member_expression arguments.
The CUBEKPIMEMBER function returns the key performance indicator (KPI) property and displays the KPI name in the cell.
The CUBESETCOUNT function returns the number of items in a set.
The Best Office Productivity Tools
Kutools for Excel - Helps You To Stand Out From Crowd
Would you like to complete your daily work quickly and perfectly? Kutools for Excel brings 300 powerful advanced features (Combine workbooks, sum by color, split cell contents, convert date, and so on...) and save 80% time for you.
- Designed for 1500 work scenarios, helps you solve 80% Excel problems.
- Reduce thousands of keyboard and mouse clicks every day, relieve your tired eyes and hands.
- Become an Excel expert in 3 minutes. No longer need to remember any painful formulas and VBA codes.
- 30-day unlimited free trial. 60-day money back guarantee. Free upgrade and support for 2 years.
Office Tab - Enable Tabbed Reading and Editing in Microsoft Office (include Excel)
- One second to switch between dozens of open documents!
- Reduce hundreds of mouse clicks for you every day, say goodbye to mouse hand.
- Increases your productivity by 50% when viewing and editing multiple documents.
- Brings Efficient Tabs to Office (include Excel), Just Like Chrome, Firefox, And New Internet Explorer.
