Applies ToExcel for Microsoft 365 Excel for Microsoft 365 for Mac Excel for the web Excel 2024 Excel 2024 for Mac Excel 2021 Excel 2021 for Mac Excel 2019 Excel 2016 Excel for iPad Excel Web App Excel for iPhone Excel for Android tablets Excel for Android phones

This article describes the formula syntax and usage of the CUBESET function in Microsoft Excel.

Description

Defines a calculated set of members or tuples by sending a set expression to the cube on the server, which creates the set, and then returns that set to Microsoft Excel.

Syntax

CUBESET(connection, set_expression, [caption], [sort_order], [sort_by])

The CUBESET function syntax has the following arguments:

  • Connection    Required. A text string of the name of the connection to the cube.

  • Set_expression    Required. A text string of a set expression that results in a set of members or tuples. Set_expression can also 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 is displayed in the cell instead of the caption, if one is defined, from the cube.

  • Sort_order    Optional. The type of sort, if any, to perform and can be one of the following:

Integer

Enumerated constant

Description

Sort_by argument

0

SortNone

Leaves the set in 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

  • The default value is 0. An alpha sort for a set of tuples sorts on the last element in each tuple. For more information on these different sort orders, see the Microsoft Office SQL Analysis Services help system.

  • Sort_by    Optional. A text string of the value by which to sort. For example, to get the city with the highest sales, set_expression would be a set of cities, and sort_by would be the sales measure. Or, to get the city with the highest population, set_expression would be a set of cities, and sort_by would be the population measure. If sort_order requires sort_by, and sort_by is omitted, CUBESET returns the #VALUE! error message.

Remarks

  • When the CUBESET function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved.

  • If the connection name is not a valid workbook connection stored in the workbook, CUBESET returns a #NAME? error value. If the Online Analytical Processing (OLAP) server is not running, not available, or returns an error message, CUBESET returns a #NAME? error value.

  • If the set_expression syntax is incorrect or the set contains at least one member with a different dimension than the other members, CUBESET returns a #N/A error value.

  • If set_expression is longer than 255 characters, which is the limit for an argument to a function, CUBESET returns a #VALUE! error value. To use text strings longer than 255 characters, enter the text string in a cell (for which the limit is 32,767 characters), and then use a cell reference as the argument.

  • CUBESET may return a #N/A error value if you reference a session-based object, such as a calculated member or named set, in a PivotTable when sharing a connection, and that PivotTable is deleted or you convert the PivotTable to formulas. (On the Options tab, in the Tools group, click OLAP Tools, and then click Convert to Formulas.)

Examples

=CUBESET("Finance","Order([Product].[Product].[Product Category].Members,[Measures].[Unit Sales],ASC)","Products")

=CUBESET("Sales","[Product].[All Products].Children","Products",1,"[Measures].[Sales Amount]")

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.