CUBESET FunctionWayne Becker20151022T03:19:14+00:00
The CUBESET function 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 Excel.
 This function was introduced in Excel 2007 and so is not available in earlier versions.
Syntax
=CUBESET(connection,set_expression,[Caption],[sort_order])
Arguments
Argument 
Description 
connection 
A text string of the name of the connection to the cube 
set_expression 
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 (default) 
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 
SortAlphaDescending 
Sorts set in alpha descending order 
Ignored 

5 
SortNaturalAscending 
Sorts set in natural ascending order 
Ignored 

6 
SortNaturalDescending 
Sorts set in natural descending order 
Ignored 

[sort_by] 
Optional. A text string of the value by which to sort. E.g. to get the city with the highest sales, set_expression would be a set of cities, and sort_by would be the sales measure 
Examples

A 
B 
1 
Formula 

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

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

Note: Argument functions, such as set_expression, are limited to 255 characters. 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.
Common Function Error(s)
Problem 
What went wrong 
#VALUE! 
Occurs if either:

• 
the sort_by argument is not provided but is required by sort_order 

• 
the set_expression is longer than 255 characters 

#N/A! 
Occurs if either:

• 
the set_expression syntax is incorrect 

• 
the set contains at least one member with a different dimension than the other members 

#NAME? 
Occurs if either:

• 
the connection name is not a valid workbook connection stored in the workbook 

• 
the Online Analytical Processing (OLAP) server is not running, not available, or returns an error message 
