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]")
