CUBESET function
Summary
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 Office 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.
• 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.
Example
=CUBESET("Finance","Order([Product].[Product].[Product Category].Members,[Measures].[Unit Sales],ASC)","Products")
=CUBESET("Sales","[Product].[All Products].Children","Products",1,"[Measures].[Sales Amount]")
=CUBESET("Sales","[Product].[All Products].Children","Products",1,"[Measures].[Sales Amount]")