When the workbook is connected to a Microsoft SQL Server 2005 Analysis Services or later data source, this function returns an aggregated value from the cube
Syntax:
The CUBEVALUE function syntax has the following arguments:
- Connection Required. A text string of the name of the connection to the cube.
-
Member_expression Optional. 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.
Example: Let’s look at some Excel CUBEVALUE function examples and explore how to use the CUBEVALUE function as a worksheet function in Microsoft Excel:
Syntax:
=CUBEVALUE(“Sales”,”[Measures].[Profit]”,”[Time].[2004]”,”[All Product].[Beverages]”)
=CUBEVALUE($A$1,”[Measures].[Profit]”,D$12,$A23)
=CUBEVALUE(“Sales”,$B$7,D$12,$A23)
Note:
- the CUBEVALUE function is supported only when the workbook is connected to a Microsoft SQL Server 2005 Analysis Services or later data source
- 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 an #NAME? error value. If the Online Analytical Processing (OLAP) server is not running, not available or returns an error message, CUBEVALUE returns an #NAME? error value
- if at least one element within the tuple is invalid, CUBEVALUE returns an #VALUE! error value
- CUBEVALUE returns an #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 an #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 formulae (on the ‘Options’ tab in the Ribbon, in the ‘Tools’ group, click ‘OLAP Tools’, and then click ‘Convert to Formulas’).
