How to Use the CUBEKPIMEMBER Function in Excel
The CUBEKPIMEMBER function in Microsoft Excel and Google Sheets is designed to retrieve the value of a Key Performance Indicator (KPI) for a specified measure in a data cube. Specifically, it fetches the KPI assessment for a chosen measure from an Analysis Services cube.
How the CUBEKPIMEMBER Function Operates
The syntax for the CUBEKPIMEMBER function is:
CUBEKPIMEMBER(connection, kpi_name[, KPI_property][, caption_flag])- connection: A text string that identifies the OLAP cube, which can be located either in the current workbook or on a server.
- kpi_name: A text string that names the Key Performance Indicator.
- KPI_property (optional): An integer specifying which property to retrieve. By default, it returns the KPI’s current value. Typical properties include 0 for the current value, 1 for the goal, 2 for the status, 3 for the trend, and 4 for the weight.
- caption_flag (optional): A boolean value determining whether the caption of the KPI is returned.
Examples of Using the CUBEKPIMEMBER Function
For instance, consider an OLAP cube with a KPI named “Revenue” from which we wish to extract the current value and the goal:
| Formula | Description |
|---|---|
=CUBEKPIMEMBER("OLAPCube","Revenue",0) |
This formula retrieves the current value of the “Revenue” KPI. |
=CUBEKPIMEMBER("OLAPCube","Revenue",1) |
This formula fetches the goal value of the “Revenue” KPI. |
Another example involves obtaining the status and trend of a KPI:
| Formula | Description |
|---|---|
=CUBEKPIMEMBER("OLAPCube","Revenue",2) |
This formula retrieves the status of the “Revenue” KPI. |
=CUBEKPIMEMBER("OLAPCube","Revenue",3) |
This formula fetches the trend of the “Revenue” KPI. |
By using the CUBEKPIMEMBER function, you can effectively extract and analyze key performance indicator data from OLAP cubes, enhancing your ability to conduct thorough analysis and reporting within your Excel or Google Sheets environments.
More information: https://support.microsoft.com/en-us/office/cubekpimember-function-744608bf-2c62-42cd-b67a-a56109f4b03b