Cube functions

Click one of the links in the following list to see detailed help about the function.

See Also

• Get Help about SQL Server 2005 Analysis Services
• List of worksheet functions (alphabetical)
• List of worksheet functions (by category)

 

 

Excel > Function reference > Cube

CUBEKPIMEMBER function

Returns a key performance indicator (KPI) property and displays the KPI name in the cell. A KPI is a quantifiable measurement, such as monthly gross profit or quarterly employee turnover, that is used to monitor an organization's performance.

 Note    The CUBEKPIMEMBER function is supported only when the workbook is connected to a Microsoft SQL Server 2005 Analysis Services or later data source.

Syntax

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 and can be one of the following:

If you specify KPIValue for kpi_property, only kpi_name is displayed in the cell.

Caption   is an alternative text string that is displayed in the cell instead of kpi_name and kpi_property.

Remarks

• When the CUBEKPIMEMBER function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved.
• To use the KPI in a calculation, specify the CUBEKPIMEMBER function as a member_expression argument in the CUBEVALUE function.
• If the connection name is not a valid workbook connection that is stored in the workbook, CUBEKPIMEMBER returns a #NAME? error value. If the Online Analytical Processing (OLAP) server is not running, not available, or returns an error message, CUBEKPIMEMBER returns a #NAME? error value.
• CUBEKPIMEMBER returns a #N/A error value when kpi_name or kpi_property is invalid.
• CUBEKPIMEMBER may return a #N/A error value if you reference a session-based object, such as a calculated member or named set, in a PivotTable when sharing a connection, and that PivotTable is deleted or you convert the PivotTable to formulas. (On the Options tab, in the Tools group, click OLAP Tools, and then click Convert to Formulas.)

Examples

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

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

See Also

• Convert PivotTable cells to worksheet formulas
• Cube functions
• Get Help about SQL Server 2005 Analysis Services
• GETPIVOTDATA function
• Use Formula AutoComplete

 

 

Excel > Function reference > Cube

CUBEMEMBER function

Returns a member or tuple from the cube. Use to validate that the member or tuple exists in the cube.

Syntax

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 of a multidimensional expression (MDX) that evaluates to a unique member in the cube. Alternatively, member_expression can be a tuple, specified as a cell range or an array constant.

Caption   is a text string displayed in the cell instead of the caption, if one is defined, from the cube. When a tuple is returned, the caption used is the one for the last member in the tuple.

Remarks

• When the CUBEMEMBER function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved.
• When you use CUBEMEMBER as an argument to another CUBE function, the MDX expression that identifies the member or tuple is used by that CUBE function, not the displayed value in the cell of the CUBEMEMBER function.
• If the connection name is not a valid workbook connection stored in the workbook, CUBEMEMBER returns a #NAME? error value. If the Online Analytical Processing (OLAP) server is not running, not available, or returns an error message, CUBEMEMBER returns a #NAME? error value.
• If at least one element within the tuple is invalid, CUBEMEMBER returns a #VALUE! error value.
• If member_expression is longer than 255 characters, which is the limit for an argument to a function, CUBEMEMBER returns a #VALUE! error value. To use text strings longer than 255 characters, enter the text string in a cell (for which the limit is 32,767 characters), and then use a cell reference as the argument.
• CUBEMEMBER returns a #N/A error value when:
  • The member_expression syntax is incorrect.
  • The member specified by the MDX text string doesn't exist in the cube.
  • The tuple is invalid because there is no intersection for the specified values. (This can occur with multiple elements from the same hierarchy.)
  • The set contains at least one member with a different dimension than the other members.
• CUBEMEMBER may return a #N/A error value if you reference a session-based object, such as a calculated member or named set, in a PivotTable when sharing a connection, and that PivotTable is deleted or you convert the PivotTable to formulas. (On the Options tab, in the Tools group, click OLAP Tools, and then click Convert to Formulas.)

Examples

=CUBEMEMBER("Sales","[Time].[Fiscal].[2004]")

=CUBEMEMBER($A$1,D$12)

=CUBEMEMBER("Sales",(B4, C6, D5),"SalesFor2004")

=CUBEMEMBER("xlextdat8 FoodMart 2000 Sales","([Product].[Food],[Time].[1997])")

=CUBEMEMBER($A$1,C$12:D$12)

See Also

• Convert PivotTable cells to worksheet formulas
• Cube functions
• Get Help about SQL Server 2005 Analysis Services
• GETPIVOTDATA function
• Use Formula AutoComplete

 

 

Excel > Function reference > Cube

CUBEMEMBERPROPERTY function

Returns the value of a member property from the cube. Use to validate that a member name exists within the cube and to return the specified property for this member.

Syntax

CUBEMEMBERPROPERTY(connection,member_expression,property)

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

Member_expression   is a text string of a multidimensional expression (MDX) of a member within the cube.

Property   is a text string of the name of the property returned or a reference to a cell that contains the name of the property.

Remarks

• When the CUBEMEMBERPROPERTY function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved.
• If the connection name is not a valid workbook connection stored in the workbook, CUBEMEMBERPROPERTY returns a #NAME? error value. If the Online Analytical Processing (OLAP) server is not running, not available, or returns an error message, CUBEMEMBERPROPERTY returns a #NAME? error value.
• If the member_expression syntax is incorrect or if the member specified by member_expression doesn't exist in the cube, CUBEMEMBERPROPERTY returns a #N/A error value.
• CUBEMEMBERPROPERTY may return a #N/A error value if you reference a session-based object, such as a calculated member or named set, in a PivotTable when sharing a connection, and that PivotTable is deleted or you convert the PivotTable to formulas. (On the Options tab, in the Tools group, click OLAP Tools, and then click Convert to Formulas.)

Examples

=CUBEMEMBERPROPERTY("Sales","[Time].[Fiscal].[2004]",$A$3)

=CUBEMEMBERPROPERTY("Sales","[Store].[MyFavoriteStore]","[Store].[Store Name].[Store Sqft]")

See Also

• Convert PivotTable cells to worksheet formulas
• Cube functions
• Get Help about SQL Server 2005 Analysis Services
• GETPIVOTDATA function
• Use Formula AutoComplete

 

 

Excel > Function reference > Cube

CUBERANKEDMEMBER function

Returns the nth, or ranked, member in a set. Use to return one or more elements in a set, such as the top sales performer or the top 10 students.

Syntax

CUBERANKEDMEMBER(connection,set_expression,rank,caption)

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

Set_expression  is a text string of a set expression, such as "{[Item1].children}". Set_expression can also be the CUBESET function, or a reference to a cell that contains the CUBESET function.

Rank   is an integer value specifying the top value to return. If rank is a value of 1, it returns the top value, if rank is a value of 2, it returns the second most top value, and so on. To return the top 5 values, use CUBERANKEDMEMBER five times, specifying a different rank, 1 through 5, each time.

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

Remarks

• When the CUBERANKEDMEMBER function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved.
• If the connection name is not a valid workbook connection stored in the workbook, CUBERANKEDMEMBER returns a #NAME? error value. If the Online Analytical Processing (OLAP) server is not running, not available, or returns an error message, CUBERANKEDMEMBER returns a #NAME? error value.
• CUBERANKEDMEMBER returns a #N/A error value when the syntax of set_expression is incorrect or when the set contains at least one member with a different dimension than the other members.

Examples

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

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

Tip  To return the bottom n values, use the sort_order and sort_by arguments of the CUBESET function to reverse the order of the set so that the top values in the sorted set are the bottom values. For example, CUBERANKEDMEMBER ("Sales", $D$4,1) returns the last member, CUBERANKEDMEMBER ("Sales", $D$4, 2) returns the next to last member, and so on.

See Also

• Convert PivotTable cells to worksheet formulas
• Cube functions
• Get Help about SQL Server 2005 Analysis Services
• GETPIVOTDATA function
• Use Formula AutoComplete

 

 

Excel > Function reference > Cube

CUBESET 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 Office Excel.

Syntax

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

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

Set_expression   is a text string of a set expression that results in a set of members or tuples. Set_expression can also be a cell reference to an Excel range that contains one or more members, tuples, or sets included in the set.

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

Sort_order   is the type of sort, if any, to perform and can be one of the following:

The default value is 0. An alpha sort for a set of tuples sorts on the last element in each tuple. For more information on these different sort orders, see the Microsoft Office SQL Analysis Services help system.

Sort_by   is a text string of the value by which to sort. For 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. Or, to get the city with the highest population, set_expression would be a set of cities, and sort_by would be the population measure. If sort_order requires sort_by, and sort_by is omitted, CUBESET returns the #VALUE! error message.

Remarks

• When the CUBESET function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved.
• If the connection name is not a valid workbook connection stored in the workbook, CUBESET returns a #NAME? error value. If the Online Analytical Processing (OLAP) server is not running, not available, or returns an error message, CUBESET returns a #NAME? error value.
• If the set_expression syntax is incorrect or the set contains at least one member with a different dimension than the other members, CUBESET returns a #N/A error value.
• If set_expression is longer than 255 characters, which is the limit for an argument to a function, CUBESET returns a #VALUE! error value. To use text strings longer than 255 characters, enter the text string in a cell (for which the limit is 32,767 characters), and then use a cell reference as the argument.
• CUBESET may return a #N/A error value if you reference a session-based object, such as a calculated member or named set, in a PivotTable when sharing a connection, and that PivotTable is deleted or you convert the PivotTable to formulas. (On the Options tab, in the Tools group, click OLAP Tools, and then click Convert to Formulas.)

Examples

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

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

See Also

• Convert PivotTable cells to worksheet formulas
• Cube functions
• Get Help about SQL Server 2005 Analysis Services
• GETPIVOTDATA function
• Use Formula AutoComplete

 

 

Excel > Function reference > Cube

CUBESETCOUNT function

Returns the number of items in a set.

Syntax

CUBESETCOUNT(set)

Set   is a text string of a Microsoft Office Excel expression that evaluates to a set defined by the CUBESET function. Set can also be the CUBESET function, or a reference to a cell that contains the CUBESET function.

Remark

When the CUBESETCOUNT function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved.

Examples

=CUBESETCOUNT(A3)

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

See Also

• Convert PivotTable cells to worksheet formulas
• Cube functions
• Get Help about SQL Server 2005 Analysis Services
• GETPIVOTDATA function
• Use Formula AutoComplete

 

 

Excel > Function reference > Cube

CUBEVALUE function

Returns an aggregated value from the cube.

Syntax

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 of a multidimensional expression (MDX) that evaluates to a member or tuple within the cube. Alternatively, member_expression can be a set defined with the CUBESET function. Use member_expression as a slicer to define the portion of the cube for which the aggregated value is returned. If no measure is specified in member_expression, the default measure for that cube is used.

Remarks

• When the CUBEVALUE function evaluates, it temporarily displays a "#GETTING_DATA…" message in the cell before all of the data is retrieved.
• If a cell reference is used for member_expression, and that cell reference contains a CUBE function, then member_expression uses the MDX expression for the item in the referenced cell, and not the value displayed in that referenced cell.
• If the connection name is not a valid workbook connection stored in the workbook, CUBEVALUE returns a #NAME? error value. If the Online Analytical Processing (OLAP) server is not running, not available, or returns an error message, CUBEVALUE returns a #NAME? error value.
• If at least one element within the tuple is invalid, CUBEVALUE returns a #VALUE! error value.
• CUBEVALUE returns a #N/A error value when:
  • The member_expression syntax is incorrect.
  • The member specified by member_expression doesn't exist in the cube.
  • The tuple is invalid because there is no intersection for the specified values. (This can occur with multiple elements from the same hierarchy.)
  • The set contains at least one member with a different dimension than the other members.
  • CUBEVALUE may return a #N/A error value if you reference a session-based object, such as a calculated member or named set, in a PivotTable when sharing a connection, and that PivotTable is deleted or you convert the PivotTable to formulas. (On the Options tab, in the Tools group, click OLAP Tools, and then click Convert to Formulas.)

Issue: Null values are converted to zero-length strings

Examples

=CUBEVALUE("Sales","[Measures].[Profit]","[Time].[2004]","[All Product].[Beverages]")

=CUBEVALUE($A$1,"[Measures].[Profit]",D$12,$A23)

=CUBEVALUE("Sales",$B$7,D$12,$A23)