How to Use Cube Functions in Excel

Cube functions allow Excel to access data from an Analysis Services data source without using a Pivot Table.

There may be times when you cannot use PivotTables to submit reports directly. It is also possible that you will have to fill in the data in the format provided by your company. In this case, you can use the cube function.

There are many different cube functions:

 

CUBEVALUE function

CubeValue function returns an aggregated value from the cube.

 

The syntax of the CUBEVALUE function

=CUBEVALUE(connection, member_expression1, member_expression2…)

connection is a text string of the name of the connection to the cube

member_expression is a text string that evaluates to a member within the cube

 

Example:

=CUBEVAULE ("Sales"," [Measures].[Profit]", "[Time].[2022]"," [All Product].[Beverages]")

=CUBEVALUE ($A$1,” [Member].[Profit]", D212,$A23)

CUBEVALUE

 

CUBEMEMBER function

CUBEMEMBER returns an element or a tuple from the cube.

 

The syntax of CUBEMEMBER function

=CUBEMEMBER (connection, member_expression, [caption])

Connection is a text string of the name of the connection to the cube

member_expression is a text string that evaluates to a member within the cube

Caption is a text string that is displayed in the cell instead of the caption. If defined, it is displayed from the cube.

 

Example:

=CUBEMEMBER ("Sales", [months].[september])

 

CUBEKPIMEMBER function

CUBEKPIMEMBER returns a key performance indicator (KPI) property and displays the KPI name in the cell

 

The syntax of CUBEKPIMEMBER

=CUBEKPIMEMBER (connection, kpi_name, kpi_property, [caption])

Connection is a text string of the name of the connection to the cube

Kpi_name is a text string of the name of the kpi in the cube

Kpi_property is the Kpi component returned

  • When the value is 1, the enumeration constant is "KPIValue", which represents the actual value.
  • When the value is 2, the enumeration constant is "KPIGoal", indicating the target value.
  • When the value is 3, the enumeration constant is "KPIStatus", which indicates the status of the KPI at a specific moment.
  • When the value is 4, the enumeration constant is "KPITrend", which represents the measure of the trend value.
  • When the value is 5, the enumeration constant is "KPIWeight", which indicates the relative weight assigned to the KPI.
  • When the value is 6, the enumeration constant is "KPICurrentTimeMember", which indicates the temporary content of KPI. 

Example:

=CUBEKPIMEMBER ("Sales","MySalesKPI", 1)

=CUBEKPIMEMBER ("Sales","MySalesKPI", KPIGoal,"Sales KPI Goal")

 

CUBERANKEDMEMBER function

Returns the nth, or ranked, member in a set.

 

CUBERANKEDMEMBER function syntax

=CUBERANKEDMEMBER(connection, set_expression, rank, [caption])

Connection is a text string of the name of the connection to the cube

Set_expression the text string of the set expression.

Rank is an integer used to determine the highest value to return. If the value of this parameter is 1, the highest value is returned. If the value is 2, the second highest value is returned, and so on.

Caption is optional. It is a string of text to display in the cube cells

 

Examples

=CUBERANKEDMEMBER("Sales",$D$4,1,"Top Month")

=CUBERANKEDMEMBER("Sales",CUBESET("Sales","Summer","[2022].[June]","[2022].[July]","[2022].August]"),3,"Top Month")

CUBERANKEDMEMBER

CUBESET function

Defines a calculated set of members or tuples by sending a set expression to the cube on the server. This creates the set, and returns that set to Microsoft Excel.

 

Syntax of CUBESET function

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

Set_expression is A text string of a set expression that results in a set of members or tuples.

Caption is a text string that is displayed in the cell instead of the caption. If one is defined, it is displayed from the cube.

Sort_order is the type of sort, if any, to perform.

 

Examples

=CUBESET("Finance","Order([Product].[Product].[Product Category].Members,[Measures].[UnitSales],ASC)","Products")

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

=CUBESET("Sales","Filter([Product].[Level_1].[Level_1].Members, Right([Product].[Level_1].CurrentMember.Name, 2)=""_Wisconsin"")")

=CUBESET("ThisWorkbookDataModel",(A1,A2),"Product1 & Product2")

 

CUBESETCOUNT function

Returns the number of items in a set.

 

The syntax of the CUBESETCOUNT function

=CUBESETCOUNT(set)

Where Set is a text string of a Microsoft Excel expression that evaluates to a set defined by the CUBESET function.

 

Examples:

=CUBESETCOUNT (A3)

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

CUBESETCOUNT

It is possible to create formulas using nested cube functions. For example you can create something like:

=CUBEVALUE("DataModel","MyMeasure","Date","MyFilter",CUBESET("DataModel","MyFilter2"))

Further reading: 
Create Pivot Table from Multiple Sheets
Engineering functions
How to create a year-over-year report using a pivot table?