Syntax. CUBEKPIMEMBER(connection,kpi_name,kpi_property,caption)
Definition. This function returns a Key Performance Indicator (KPI) property and displays the KPI name in the cell.
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 later in this chapter in the description of CUBEMEMBER()).
kpi_name (required) Specifies the name of the KPI in the cube.
kpi_property (required) A KPI consists of several components that are specified by using an integer (see Table 14-2).
Table 14-2. Integers for the Third Argument of the CUBEKPIMEMBER() Function
Integer
MDX expression
Description
1
[KPIValue]
Actual value
2
[KPIGoal]
Target value
3
[KPIStatus]
State of the KPI at a specific moment in time
4
[KPITrend]
Measure of the value over time
5
[KPIWeight]
Relative importance assigned to the KPI
6
[KPICurrentTimeMember]
Temporal context for the KPI
caption (optional) A string displayed in the cell instead of the caption of the KPI components in the cube.
Background
Note
In the cell containing the function, the message #GETTING_DATA temporarily appears while the data is being queried.
Error values and messages provide information about incorrect or missing entries:
If the connection name is not a valid workbook connection, the CUBEKPIMEMBER() 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.
CUBEKPIMEMBER() returns the
#N/Aerror value when kpi_name or kpi_property is invalid.CUBEKPIMEMBER() might return the
#N/Aerror when the connection to the data source is interrupted and cannot be re-established
You can combine CUBEKPIMEMBER() with CUBEVALUE(). Specify CUBEKPIMEMBER() as the second argument or reference for CUBEVALUE().
Example. In this example, a KPI named average is saved in the cube. This cube calculates the average of the sales and the total number of sales as integers. Both values are also saved as measures in the cube but cannot be used to calculate fields in the PivotTable. The target value (goal) is $1,500. Figure 14-3 shows the example for cookies.
=CUBEKPIMEMBER("offline","average",1)displays the word average. The formula
=CUBEVALUE("offline",CUBEKPIMEMBER("offline","average",1))returns 1453 (the rounded average of all sales). In the second formula, you can enter a reference to the cell containing the first formula as the second argument. To get the target value of the average, use the formula
=CUBEVALUE("offline",CUBEKPIMEMBER("offline","average",2))The value of 2 in the last argument is important, because it indicates, in this case, the target value.
You can use the cell containing the formula to create cell captions. The real content of the cell is more informative, as shown by using the CUBEVALUE() function.