Cube Functions in Microsoft Excel 2010

CUBEVALUE()

Syntax CUBEVALUE(connection,member_expression1,member_expression2,...)

Definition This function returns the value of a member (cell) from a cube.

Arguments

• connection (required) A string with the name of the workbook connection to the cube. After you enter the first quotation mark, the existing context-sensitive data connections are displayed (see Figure 14-4, shown earlier).

• member_expression1 (required) and member_expression2 (optional) At least one and up through 255 expressions that define the position of a member in the cube based on an MDX. The expression can be entered directly or can be in a cell that is referenced. You can also use tuples in expressions. Alternatively, member_expression can be a set defined with the CUBESET() function. If no measure is specified in member_expression, the default measure for that cube is used.

  Because the argument can be repeated, you can define intersections. You can also use tuples.

Background When you use CUBEVALUE() as an argument for another cube function, the MDX expression instead of the displayed value is used in the argument.

Error values and messages provide information about wrong or missing entries:

• If the connection name is not a valid workbook connection, the CUBEVALUE() function returns the #NAME? error.

• If the OLAP server (or the offline cube) is not available, you get an error message. The content of the affected cell doesn’t change.

• If at least one member within the arguments or the tuple is invalid, the CUBEVALUE() function returns the #VALUE! error.

• If member_expression is longer than 255 characters, the CUBEVALUE() function returns the #VALUE! error.

• CUBEVALUE() returns the #N/A error when:

  • The member_expression syntax is incorrect.

  • The member specified in the MDX query doesn’t exist in the cube.

  • The tuple is invalid because there is no intersection for the specified values.

  • The set contains at least one member with a different dimension from the other members.

• CUBEVALUE() might return the #N/A error when the connection to the data source is interrupted and cannot be re-established

The formula

=CUBEVALUE("offLine","[Measures].[GrossSales]","[Stores].[Store].[All].
   [NorthEast]"," [Years].[Year].[All].[2009]","[Products].[Product].
   [All].[Cookies]")

calculates the gross sales for cookies in the store NorthEast in the year 2009: $1,856.40. You get the same result if you use a tuple (the arguments of the previous formula are enclosed in parentheses):

=CUBEVALUE("offline","([Measures].[GrossSales],[Stores].[Store].[All].[NorthEast],
   [Years].[Year].[All].[2009],[Products].[Product].[All].[Cookies])")

If you enter the formula

=CUBEMEMBER("offLine","[Products].[Product].[All].[Cookies]")

in cell B3, the formula

=CUBEVALUE("offline",B3)

returns the total sales for cookies: $21,796.

You can also use the examples for the CUBEKPIMEMBER() function. The formula

=CUBEVALUE("offline",CUBERANKEDMEMBER("offline",CUBESET("offline","[Stores]
   .[Store].Children","all store sales",2;"[Measures].[sale]"),1))

returns $10,814 for the total sales of the best store (NorthEast).