Cube Functions in Microsoft Excel 2010

CUBESET()

Syntax CUBESET(connection,set_expression,caption,sort_order,sort_by)

Definition This function returns a calculated set of members by sending a set expression to the cube on the server, which creates the set and then returns that set to Excel. The content of the cell and the actual value of the cell are different.

Arguments

• connection (required) A string with the name of the workbook connection to the cube. After you enter the first quotation mark, the existing context-sensitive data connections are displayed (see Figure 14-4, shown earlier).

• set_expression (required) Defines the number of elements in the cube based on an MDX. The expression can be entered directly or can be in a cell that is referenced. You can also use tuples in expressions.

• caption (optional) A string displayed in the cell instead of the caption of the member in the cube. If a tuple is used, the function returns the caption of the last member in the tuple.

• sort_order (optional) The type of sorting; the values are integers that affect the treatment of the fifth argument, sort_by (see Table 14-3). The formulas are context-sensitive (see Figure 14-7).

  

  Figure 14-7 Tooltips show the choices for the CUBESET() function.

  Table 14-3 Integers for the Fourth Argument of the CUBESET() Function

  Integer	Description	Impact on the Fifth Argument
  0	Leaves the set in the existing order in the cube	Ignored
  1	Sorts the set in ascending order by sort_by	Required
  2	Sorts the set in descending order by sort_by	Required
  3	Sorts the set in ascending alphabetical order	Ignored
  4	Sorts the set in descending alphabetical order	Ignored
  5	Sorts the set in natural ascending order	Ignored
  6	Sorts the set in natural descending order	Ignored

  The default value of the fourth argument is 0. An alphabetical sorting for a set of tuples is based on the last element in the tuple. You will find more information about the different sort orders in the SQL Analysis Services Help.

• sort_by (optional) The sort_by argument depends on the fourth argument and defines the values in the set that is sorted. If sort_by is not provided but sort_order requires sort_by, the function returns the #VALUE! error.

Background When you use CUBESET() as an argument for another cube function, the set instead of the displayed value is used in the argument.

Error values and messages provide information about incorrect or missing entries:

• If the connection name is not a valid workbook connection, the CUBESET() function returns the #NAME? error.

• If the OLAP server (or the offline cube) is not available, you get an error message. The content of the affected cell doesn’t change.

• If set_expression is longer than 255 characters, the CUBESET() function returns the #VALUE! error.

• CUBESET() returns the #N/A error when:

  • The set_expression syntax is incorrect.

  • The set specified in the MDX query doesn’t exist in the cube.

  • The set contains at least one member with a different dimension from the other members.

• CUBESET() might return the #N/A error when the connection to the data source is interrupted and cannot be re-established.

Example The formula

=CUBESET("offline","[Stores].[Store].Children",
   "all store sales",2,"[Measures].[Sale]")

returns the sorted set of all stores based on the sales (all products and years). The store with the largest sale is listed first.

You can use the cell containing the formula to create cell labels. The actual content of the cell is more informative if it refers to the cells with the CUBESET() entries.