How to use the CUBESET function in Excel

This function returns a calculated set of members by sending a set expression to the cube on the server, which then creates and returns that set to Excel. Note that the displayed content of the cell and its actual value will differ.

Syntax

CUBESET(connection; set_expression; caption; sort_order; sort_by)

Arguments

• connection (required): A string that specifies the name of the workbook connection to the cube, enclosed in quotation marks. As you type the first quotation mark, existing context-sensitive data connections will be displayed.
• set_expression (required): Defines the number of elements in the cube based on a Multidimensional Expression (MDX). You can enter the expression directly or reference a cell containing the expression. Tuples can also be used in expressions.
• caption (optional): A string to be displayed in the cell instead of the member’s caption from the cube. If a tuple is used, the function returns the caption of the last member in the tuple.
• sort_order (optional): This integer value determines the type of sorting and affects how the fifth argument, sort_by, is treated (see Table 1). The available options are context-sensitive.

Table 1. Integers for the Fourth Argument of the CUBESET() Function

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

• sort_by (optional): This argument depends on the fourth argument (sort_order) and defines the values within the set that will be sorted. If sort_by is not provided but sort_order requires it, the function returns the #VALUE! error.

Background

When you use CUBESET() as an argument for another cube function, the set itself (not its 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 unavailable, an error message will appear, but the content of the affected cell will not 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 does not exist in the cube.
  • The set contains at least one member with a different dimension from the other members.
• CUBESET() might also 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 Name].Children », « all sales »,2, »[Measures].[Sale] ») returns the sorted set of all stores based on their sales (across all products and years). The store with the largest sale will be listed first.

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