Buzz
In this article, Mytour introduces you to the CUBEVALUE function in Excel, describing its function, examples, and how to use the CUBEVALUE function in Excel to return aggregated values from a cube.
CUBEVALUE function
Description of CUBEVALUE function in Excel
CUBEVALUE function in Excel returns aggregated values from a cube.
Syntax of CUBEVALUE function in Excel, returning aggregated values from a cube
CUBEVALUE function in Excel has the following syntax:
CUBEVALUE(connection, [member_expression1], [member_expression2], ...)
Where:
- The Connection parameter is required, it is a text string of the name of the connection to the cube.
- The Member_expression parameter is optional. It is a text string of the Multidimensional Expressions (MDX) that estimates an element or a set within the cube. Or the member_expression can be a set defined by the CUBESET function.
Use member_expression as a slicer to specify a portion of the cube for the aggregated value returned. If there is no specific measure value in member_expression, the function will use the default measure of the cube.
Some notes on the CUBEVALUE function in Excel, returning aggregated values from a cube
When evaluating the CUBEVALUE function, it temporarily displays the message '#GETTING_DATA...' in the cell before all the data is retrieved.
Issue: Null value is converted to a string of length 0
In Excel, if a cell has no data because you have never entered or deleted the cell's data, it contains an empty value. In many database systems, an empty value is called a Null value.
An empty or Null value is understood to be 'no value'. However, formulas never return an empty string or Null value but will return one of three values: a numeric value, a text value that could be an empty string, and an error value such as #NUM! or #VALUE.
If a formula contains the CUBEVALUE function connected to an Online Analytical Processing (OLAP) database and querying this database returns a Null value, Excel will convert the Null value to a string of length 0 even if the formula could return a numeric value. This can result in the cell range containing both numeric and zero-length string values, affecting the results of other formulas referencing that cell range.
For example, if cells A1 and A3 contain numeric values and cell A2 contains a formula with the CUBEVALUE function returning a zero-length string, the following formula will return the #VALUE! error:
=A1+A2+A3
To avoid errors, you can check for zero-length strings using the ISTEXT function and use the IF function to replace zero-length strings as shown in the example below:
=IF(ISTEXT(A1),0,A1)+IF(ISTEXT(A2),0,A2)+IF(ISTEXT(A3),0,A3)
Alternatively, you can nest the CUBEVALUE function within an IF condition to return a value of 0 if the CUBEVALUE function evaluates to a zero-length string, as shown in the example below:
=IF (CUBEVALUE('Sales','[Measures].[Profit]','[Time].[2004]','[All Product].[Beverages]')='', 0, CUBEVALUE('Sales','[Measures].[Profit]','[Time].[2004]','[All Product].[Beverages]'))
Note that the SUM function does not require checking for zero-length strings because it automatically skips zero-length strings during the calculation process.
An example of the CUBEVALUE function in Excel, returning aggregated value from a cube
=CUBEVALUE('Sales','[Measures].[Profit]','[Time].[2004]','[All Product].[Beverages]')
=CUBEVALUE($A$1,'[Measures].[Profit]',D$12,$A23)
=CUBEVALUE('Sales',$B$7,D$12,$A23)
This article introduces you to the CUBEVALUE function in Excel, which returns aggregated values from a cube. In addition to the CUBEVALUE function, readers can explore other articles on Mytour to learn more about various Excel functions such as DVARP, SWITCH, REPT, and WEBSERVICE function in Excel, retrieving data from web services on the Internet.
