CUBESET

Syntax:

CUBESET ( connection , set-expression [ , [ caption ] [ , [ sort-order ][ , [ sort-by ] ] ] ] )

Description: Fetches from the OLAP cube on the SQL Server designated by connection the set of members or tuples that is defined by set-expression.
Note: : Use this function to build dynamic reports that aggregate and filter data, by using the return value as a slicer in the CUBEVALUE function, the CUBERANKEDMEMBER function to choose specific members from the calculated set, and the CUBESETCOUNT function to control the size of the set.

Arguments:

Name

Type

Description

connection

text

The name of the connection to the cube.

set-expression

text, reference

A set expression that results in a set of members or tuples. set-expression can also be a cell reference to range that contains one or more members, tuples, or sets included in the set.

caption

text

The text displayed in the cell instead of the caption from the cube (assuming it defines such a caption).

sort-order

text

The type of sort, if any, to perform; it can be one of the following:

Value

Description

sort-by argument

0 or default

Leaves the set in existing order

Ignored

1

Sorts set in ascending order by sort_by

Required

2

Sorts set in descending order by sort_by

Required

3

Sorts set in alphabetic ascending order

Ignored

4

Sorts set in alphabetic descending order

Ignored

5

Sorts set in natural ascending order

Ignored

6

Sorts set in natural descending order

Ignored

An alphabetic sort for a set of tuples sorts on the last element in each tuple.

sort-by

text
The value by which to sort.
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. To get the city with the highest population, set-expression would be a set of cities, and sort-by would be the population measure.

Return Type and Value: any – The set of members or tuples.

However, if

  • The connection name is not a valid workbook connection stored in the workbook, the return value is unspecified.

  • The OLAP server is not running, not available, or returns an error message, the return value is unspecified.

  • The syntax of member-expression is incorrect, the return value is unspecified.

  • The set contains at least one member with a different dimension than the other members, the return value is unspecified.

  • sort-order is outside the range 0–6, #N/A is returned.

  • sort-order requires sort-by, but sort-by is omitted, #VALUE! is returned.

example:

CUBESET("Sales","[Product].[All Products].Children", "Products",1,"[Measures].[Sales Amount]")]