Skip to main content
Version: I2023.2.1

CUBESET

The 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.

Before you begin​

Ensure you know the syntax and specifics of the CUBESET. For details, see CUBESET function.

Syntax​

CUBESET(connection, set_expression, [caption], [sort_order], [sort_by])

Consider that for set_expression in-line MDX functions besides the range operator ":" are not supported. It can also be a cell reference to an Excel range that contains one or more members, tuples, or sets included in the set.

The following examples show how you can use the CUBESET function.

CUBESET(connection, set_expression(CUBEMEMBERs), caption)​

Here, the CUBEMEMBERs are from the same dimension.

MDX query name: :

C3= CUBEMEMBER- United States
C4=CUBEMEMBER- Accessories
C5= CUBEMEMBER- Germany
C6= CUBEMEMBER-France

Example: :

C24=CUBESET("Connection Name",C4:C6, "My Countries")
C25=CUBESET("Connection Name",(C3,C5,C6),"My Countries")

CUBESET(connection, set_expression, caption)​

Here, set_expression uses attributes from the same dimension.

Example: :

CUBESET("Connection Name", {"[Customer].[Customer Geography].[Customer Country].&[Germany]","[Customer].[Customer Geography].[Customer Country].&[France]","[Customer].[Customer Geography].[Customer Country].&[United States]"},"My Countries")

CUBESET(connection, set_expression, caption)​

Here, set_expression uses attributes from lower level of the hierarchy.

MDX query name: :

[CA] - California
[US] - United States
[TX] - Texas

[4] = Accessories (Product Category)
[26] = Bike Racks
[27] = Bike Stands

Example: :

C26=CUBESET("Connection Name", {"[Customer].[Customer Geography].[Customer Country].& [United States].& [CA] & [US]","[Customer].[Customer Geography].[Customer Country].& [United States].& [TX] & [US]"} ,"My US States")

C27= CUBESET("Connection Name", ,"{[Product].[Product Categories].[Category].&[4].&[26],[Product].[Product Categories].[Category].&[4].&[27]}","My Accessories")

CUBESET(connection, set_expression(), caption)​

Here, set_expression is defined by member range - 'Date Range'.

MDX query name: :

C24-startdate= 5/1/2007
C25-enddate= 5/30/2007

Example: :

C26=CUBESET("Connection Name","[DateCustom].[StandardMonth].["&C21&"]:[DateCustom].[StandardMonth].["&C22&"]","Date Range")

CUBESET(connection,set_expression(CUBEMEMBER),caption)​

MDX query name: :

C10=CUBEMEMBER("Connection Name",  (C3,C4),"US-Accessories")

Example: :

C28=CUBESET("Connection Name",(C10),"Tuple Set")

CUBESET(connection, set_expression(CUBEMEMBER1,CUBEMEMBER2,...), caption)​

Here, CUBEMEMBER1, CUBEMEMBER2, and so on are different dimensions.

MDX query name: :

C3= CUBEMEMBER- United States
C4= CUBEMEMBER- Accessories

Example: :

C29=CUBESET("Connection Name",C3:C4,"Tuple Set")

CUBESET(connection, set_expression(), caption)​

Here, set_expression uses dimension attribute with Children function

MDX query name: :

[4]  -(Accessories Product Category)

Example: :

C30=CUBESET("Connection Name","[Product].[Product Categories].&[4].Children","Products")

CUBESET(connection,EXCEPT(),caption)​

Here, EXCEPT uses multidimensional tuple.

MDX query name: :

C3= CUBEMEMBER- United States
C5= CUBEMEMBER- Germany

Example: :

C31=CUBESET("Connection Name",EXCEPT(C3,C5),"My Set")

More information​

Using CUBE functions