Skip to main content

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.

cubeset function 1


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.

cubeset function 2


Related functions

Excel CUBEMEMBER Function

The CUBEMEMBER function retrieves a member or tuple from a cube if it exists. Otherwise, a #N/A error value will be returned.

Excel CUBEVALUE Function

The CUBEVALUE function returns an aggregated value from a cube filtered by multiple member_expression arguments.

Excel CUBEKPIMEMBER Function

The CUBEKPIMEMBER function returns the key performance indicator (KPI) property and displays the KPI name in the cell.

Excel CUBESETCOUNT Function

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

🤖 Kutools AI Aide: Revolutionize data analysis based on: Intelligent Execution   |  Generate Code  |  Create Custom Formulas  |  Analyze Data and Generate Charts  |  Invoke Kutools Functions
Popular Features: Find, Highlight or Identify Duplicates  |  Delete Blank Rows  |  Combine Columns or Cells without Losing Data  |  Round without Formula ...
Super VLookup: Multiple Criteria  |  Multiple Value  |  Across Multi-Sheets  |  Fuzzy Lookup...
Adv. Drop-down List: Easy Drop Down List  |  Dependent Drop Down List  |  Multi-select Drop Down List...
Column Manager: Add a Specific Number of Columns  |  Move Columns  |  Toggle Visibility Status of Hidden Columns  Compare Columns to Select Same & Different Cells ...
Featured Features: Grid Focus  |  Design View  |  Big Formula Bar  |  Workbook & Sheet Manager | Resource Library (Auto Text)  |  Date Picker  |  Combine Worksheets  |  Encrypt/Decrypt Cells  |  Send Emails by List  |  Super Filter  |  Special Filter (filter bold/italic/strikethrough...) ...
Top 15 Toolsets12 Text Tools (Add Text, Remove Characters ...)  |  50+ Chart Types (Gantt Chart ...)  |  40+ Practical Formulas (Calculate age based on birthday ...)  |  19 Insertion Tools (Insert QR Code, Insert Picture from Path ...)  |  12 Conversion Tools (Numbers to Words, Currency Conversion ...)  |  7 Merge & Split Tools (Advanced Combine Rows, Split Excel Cells ...)  |  ... and more

Kutools for Excel Boasts Over 300 Features, Ensuring That What You Need is Just A Click Away...

Description


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, Edge and Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations