Welcome back to our regular blog of Excel functions from A to Z. Today we look at the CUBEKPIMEMBER function.
The CUBEKPIMEMBER function
When the workbook is connected to a Microsoft SQL Server 2005 Analysis Services or a later data source, tThis function returns a KPI property (KPI) and displays the name of the KPI in the cell. A KPI is a quantifiable measure, such as monthly gross profit or quarterly employee turnover, used to monitor the performance of an organization.
The CUBEKPIMEMBER The function uses the following syntax to operate:
CUBEKPIMEMBER (Connection, kpi_name, kpi_property, )
The CUBEKPIMEMBER The function has the following arguments:
- Connection: this is mandatory and represents a text string of the name of the connection to the cube
- kpi_name: this is also necessary. This is a text string of the name of the KPI in the cube.
- kpi_property: again, this is obligatory. This is the returned KPI component and it can be one of the following:
- subtitle: this is optional. This represents an alternative text string that is displayed in the cell instead of kpi_name Y kpi_property.
It should also be noted that:
- the CUBEKPIMEMBER The function is supported only when the workbook is connected to a Microsoft SQL Server 2005 Analysis Services or a later data source.
- when the CUBEKPIMEMBER The function evaluates, temporarily display a message “# GETTING_DATA …” in cell before all data is retrieved.
- to use the KPI in a calculation, specify the CUBEKPIMEMBER function as a member_expression argument in the CUBEVALUE function
- if the connection name is not a valid workbook connection that is stored in the workbook, CUBEKPIMEMBER returns a #NAME? error value. If the online analytical processing server (OLAP) it's not working, is not available or returns an error message, CUBEKPIMEMBER returns a #NAME? error value
- CUBEKPIMEMBER returns a #N / A error value when kpi_name O kpi_property is invalid
- if you specify KPIValue by kpi_property, only kpi_name displayed in cell
- CUBEKPIMEMBER can return a #N / A error value if you reference a session-based object, as a calculated member or named set, in a pivot table when a connection is shared, and that pivot table is dropped or converts pivot table to formulas (in the 'Options' tab’ of the tape , in the group 'Tools', click on 'OLAP Tools’ and then click on 'Convert to formulas').
Please, see my examples below:
= CUBEKPIMEMBER (“Sales”, “MySalesKPI”, 1)
= CUBEKPIMEMBER (“Sales”, “MySalesKPI”, KPIGoal, “Sales KPI target”)
Soon we will continue with our functions from A to Z of Excel. Keep checking: there is a new blog post every business day.