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")