A to Z of Excel Functions: The CUBESET Function

Welcome back to our regular A to Z of Excel Functions blog. Today we look at the CUBESET function.

The CUBESET function

When the workbook is connected to a Microsoft SQL Server 2005 Analysis Services or later data source, this 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 Microsoft Excel.

The CUBESET function employs the following syntax to operate:

The CUBESET function has the following arguments:

• connection: this is required and represents a text string of the name of the connection to the cube
• set_expression: this is also required. This is 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: this is optional. This is a text string that is displayed in the cell instead of the caption, if one is defined, from the cube
• sort_order: this is also optional. This is the type of sort, if any, to perform and can be one of the following:

• sort_by: this is optional and represents 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.  Alternatively, 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.

It should be further noted that: 

• the CUBESET function is supported only when the workbook is connected to a Microsoft SQL Server 2005 Analysis Services or later data source
• when the CUBESET function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved
• if the connection name is not a valid workbook connection stored in the workbook, CUBESET returns an #NAME? error value.  If the Online Analytical Processing (OLAP) server is not running, not available or returns an error message, CUBESET returns an #NAME? error value
• if the set_expression syntax is incorrect or the set contains at least one member with a different dimension than the other members, CUBESET returns an #N/A error value
• If set_expression is longer than 255 characters, which is the limit for an argument to a function, CUBESET returns an #VALUE! error value.  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
• CUBESET may return an #N/A error value if you reference a session-based object, such as a calculated member or named set, in a PivotTable when sharing a connection, and that PivotTable is deleted or you convert the PivotTable to formulae (on the ‘Options’ tab in the Ribbon, in the ‘Tools’ group, click ‘OLAP Tools’, and then click ‘Convert to Formulas’).

Please see my examples below:

We’ll continue our A to Z of Excel Functions soon.  Keep checking back – there’s a new blog post every business day.