CUBEKPIMEMBER

What it does

CUBEKPIMEMBER(connection, kpi_name, kpi_property, [caption])

A KPI in an OLAP cube is not a number. It is a small bundle of related expressions — an actual value, a target, a status, a trend, a weight, and a time context — defined once by whoever built the cube. CUBEKPIMEMBER selects one component of that bundle and hands back a member expression naming it.

CUBEKPIMEMBER(connection, kpi_name, kpi_property, [caption]) resolves the named KPI against the cube and returns the member corresponding to the requested component.

The crucial point — and the one that makes the function make sense — is that the result is a coordinate, not a value. Microsoft states the usage rule directly: "to use the KPI in a calculation, specify the CUBEKPIMEMBER function as a member_expression argument in the CUBEVALUE function." A bare CUBEKPIMEMBER cell displays the KPI's name; it does not display the KPI's number. Getting the number takes two functions.

That is the design the whole family shares (CUBEMEMBER addresses, CUBEVALUE fetches), but it surprises people here more than anywhere else, because a "KPI" sounds like a value by name.

The six components Microsoft documents:

KPIStatus and KPITrend are the two whose scale is a cube convention rather than an Excel one: they are typically normalized indicator values, and a workbook that treats them as currency or as raw counts will render nonsense. KPICurrentTimeMember is different in kind from the other five — it names a time member rather than a numeric component, which is what makes "compare this KPI against the same period last year" expressible.

Arguments

• connection (required) — text; the name of a connection stored in the workbook, used as a lookup key rather than as a connection string.
• kpi_name (required) — text; the KPI's name as defined in the cube. Cube metadata, not a label of the workbook's choosing.
• kpi_property (required) — the component selector from the table above. Microsoft documents both integers and enumerated-constant names. Note that this argument is required, which means there is no "just give me the KPI" call — you must always choose a component.
• caption (optional) — alternative text displayed instead of kpi_name and kpi_property.

One display detail Microsoft records and which is easy to trip over: if kpi_property is KPIValue, only kpi_name is displayed in the cell. So the KPIValue cell and a KPIGoal cell for the same KPI look different by default, and the difference is a display convention rather than a difference in what the cells are.

Try it

This example needs an Excel cube connection. OneCalc cannot provide that connection in this preview.

Quirks

Result and edge cases

The cell displays text — the KPI name, or the name plus component, or the supplied caption — and carries the underlying member expression as its identity for consumption by CUBEVALUE. The identity-versus-display split is the same one described on CUBEMEMBER.

While the request is outstanding the cell displays #GETTING_DATA… — a transient extended-family placeholder catalogued in the value universe, not a failure.

The Handbook cannot say from documentation whether kpi_property accepts the enumerated constant names as text in a formula or only the integers; Microsoft's table lists both columns without saying which is accepted where. That is a genuine ambiguity, not a stylistic one, and it is listed as a probe below.

Errors

As documented by Microsoft on the page linked below:

• #NAME? — the connection name is not a valid workbook connection; or the OLAP server is not running, not available, or returns an error message.
• #N/A — kpi_name or kpi_property is invalid; or the expression refers to a session-scoped object whose PivotTable has been deleted or converted to formulas.

Note that an invalid component selector and an invalid KPI name collapse onto the same code, so #N/A here does not tell a workbook author which of the two arguments is wrong.

How to implement

Notes for implementers

• There is no kernel; the function is an MDX-shape helper over a cube metadata lookup.
• Returning a value rather than a coordinate would be the natural, wrong simplification. It would make single cells look right and break every CUBEVALUE that references them.
• The KPIValue-only-shows-kpi_name display rule means the displayed text is not a function of the arguments in a uniform way. An implementation reproducing display text needs that special case explicitly.

Rust code

This entry has no Rust implementation yet.

No implementation has been published with a Handbook test suite for this function.

Version differences

No comparison between specific Excel builds is recorded here yet. This does not establish that all builds behave alike.

Alternatives

Relationships

• CUBEVALUE — the necessary partner. CUBEKPIMEMBER produces the coordinate; CUBEVALUE turns it into a number. Neither is useful alone for KPI reporting.
• CUBEMEMBER — the general coordinate producer. CUBEKPIMEMBER is best read as a specialized CUBEMEMBER that knows the MDX shape of KPI components so the author does not have to write KPIGoal("…") by hand.
• CUBEMEMBERPROPERTY — also returns "something attached to a member", but terminally: a property is a value, a KPI component is a coordinate. The two are easy to confuse and behave oppositely downstream.

Outside the family, the confusion is with PivotTable KPI display fields and with Power BI's KPI visual. Those render KPIs; this function addresses them.

Named variants

No named variants are recorded yet.