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