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. end note]

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. end example]

 

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("Finance","Order([Product].[Product].[Product Category]
.Members,[Measures].[Unit Sales],ASC)","Products")

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