CUBEMEMBER

What it does

CUBEMEMBER(connection, member_expression, [caption])

CUBEMEMBER is not a computation. It is a question asked of a server. Everything on this page follows from that.

Given the name of a workbook connection to an OLAP cube and a multidimensional-expression (MDX) string, CUBEMEMBER resolves that expression against the cube and returns the member it names — after first validating that the member exists. Microsoft's own framing for the whole family is "use to validate that the member or tuple exists in the cube", and validation is the operative verb: a CUBEMEMBER cell that resolves is a proof of existence, and one that does not resolve is a diagnostic.

The subtlety that earns this page is that the resulting cell carries two different things at once:

1. a display value — the member's caption from the cube, or the caption argument if one was supplied, and
2. an identity — the MDX expression itself.

Which of the two a consumer sees depends on the consumer. A human, a LEN, or a text concatenation sees the caption. Another CUBE function that takes the cell as an argument sees the MDX expression. Microsoft states this explicitly for CUBEVALUE: when a cell reference is used for member_expression and that cell contains a CUBE function, the MDX expression of the referenced cell is used, "not the value displayed in that referenced cell".

That is why the idiomatic cube report is built in two layers: a grid of CUBEMEMBER cells that pin down coordinates, and a grid of CUBEVALUE cells that point at those coordinate cells to fetch numbers. The CUBEMEMBER layer is the addressing layer.

member_expression may also denote a tuple — an intersection of members from distinct hierarchies — supplied as a cell range or an array constant. A tuple is a single point in the cube's coordinate space, not a set; the set-valued sibling is CUBESET.

Arguments

CUBEMEMBER(connection, member_expression, [caption])

• connection (required) — text; the name of a connection stored in the workbook, not a connection string, not a server name, not a file path. This is the argument position most often misunderstood: it is a lookup key into the workbook's connection collection, which is why a mistyped or removed connection produces #NAME? rather than a connectivity error.
• member_expression (required) — text holding an MDX expression that evaluates to a unique member of the cube; or a tuple given as a cell range or array constant. Microsoft documents a 255-character limit on the string passed directly in the formula, and notes that a cell reference can carry up to the ordinary cell limit of 32,767 characters instead. That asymmetry between an inline literal and a referenced cell is a real trap in generated workbooks.
• caption (optional) — text displayed instead of the cube's own caption. When the result is a tuple, Microsoft documents that the caption used is the one belonging to the last member of the tuple. Omitting caption means "use whatever the cube says", which makes the displayed text a server- and language-pack-dependent value.

Try it

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

Quirks

Result and edge cases

Nothing in the shared coercion chapter governs this function usefully: there is no numeric domain, no array lift worth describing, and the interesting behaviour lives entirely in resolution against a remote server.

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. Note that both a naming failure and a connectivity failure land on the same code, which makes #NAME? ambiguous here in a way it is not elsewhere in Excel.
• #N/A — the member_expression syntax is incorrect; the member the MDX names does not exist in the cube; the tuple has no valid intersection (typically two elements drawn from the same hierarchy); or the expression refers to a session-scoped object — a calculated member or named set belonging to a PivotTable that has since been deleted or converted to formulas.
• #VALUE! — at least one element within a tuple is invalid; or the expression exceeds the documented 255-character inline limit.

The Handbook has not observed any of these against a live Excel build. They are reported here as documented behaviour with the source named.

How to implement

Notes for implementers

• There is no kernel to port. Any implementation is an MDX parser plus a client for an analysis server; the "function" is a thin request wrapper. This is exactly why the reference engine defers it.
• The dual display/identity value is the load-bearing design decision. An implementation that returns plain caption text will look correct in isolation and then fail the moment a CUBEVALUE cell references it — the composition breaks, not the individual cell.
• Captions are localized by the server and its language packs. Two workbooks against the same cube on differently configured servers can display different text from identical formulas. A caption is therefore not a stable identifier and should never be used as a join key.
• The 255-versus-32,767 character asymmetry between inline literals and referenced cells is a documented behaviour that a generator emitting long MDX must respect.

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

Seven functions form the cube family, and they divide cleanly by what they return:

CUBEMEMBER and CUBERANKEDMEMBER are the two coordinate producers; CUBEVALUE is the only consumer that yields a number.

Readers confuse CUBEMEMBER with GETPIVOTDATA. They are not siblings: GETPIVOTDATA reads a PivotTable already present on a sheet, while CUBEMEMBER addresses the cube directly and needs no PivotTable at all. They do meet at one edge — a CUBEMEMBER expression naming a session-scoped calculated member created inside a PivotTable breaks when that PivotTable goes away.

Named variants

No named variants are recorded yet.