CUBERANKEDMEMBER function
Returns the nth, or ranked, member in a set. Use to return one or more elements in a set, such as the top sales performer or the top 10 students.
Syntax
CUBERANKEDMEMBER(Connection, Set_expression, Rank, [Caption])Square brackets indicate optional arguments.
Arguments
| Argument | Required | Meaning |
|---|---|---|
| Connection | Yes | Required. A text string of the name of the connection to the cube."Sales" |
| Set_expression | Yes | Required. A text string of a set expression, such as "{[Item1].children}". Set_expression can also be the CUBESET function, or a reference to a cell that contains the CUBESET function."[Product].[All Products].Children" |
| Rank | Yes | Required. An integer value specifying the top value to return. If rank is a value of 1, it returns the top value, if rank is a value of 2, it returns the second most top value, and so on. To return the top 5 values, use CUBERANKEDMEMBER five times, specifying a different rank, 1 through 5, each time.1 |
| Caption | No | Optional. A text string displayed in the cell instead of the caption, if one is defined, from the cube."Top Product" |
Query a local multidimensional report
Use CUBERANKEDMEMBER in a connected seven-function report. This fictional local cube has Region, Month and Product hierarchies. A displayed caption can differ from its unique member identifier. Refresh after fact edits; every checked answer comes from the complete current source.
Browser exercise: no external business connection. Downloads contain checked fixed values, every original fact and formula text. Native Excel CUBE recalculation requires a real OLAP connection and remains separate.
Member identifiers and source-defined measures
Revenue, Cost and Target sum matching fact rows; Profit is Revenue minus Cost; Margin divides total Profit by total Revenue. An unobserved valid intersection returns empty text, while an observed numeric zero stays zero. The Revenue target KPI compares Revenue with Target: green at target, amber from 90%, red below; Trend compares the selected or latest month with its previous metadata month; Weight is one. These definitions belong to this source.
Use one member per hierarchy in a tuple. Supported sets are explicit homogeneous nonoverlapping tuples or the All member’s Children. Set sorting supports numeric, caption and metadata order. General MDX functions and arbitrary provider connections require a different runtime.
| Hierarchy | Caption | Unique identifier |
|---|---|---|
| Region | All Region | [Region].[Region].[All] |
| Region | North | [Region].[Region].&[North] |
| Region | South | [Region].[Region].&[South] |
| Region | No observed facts | [Region].[Region].&[Empty] |
| Month | All Month | [Month].[Month].[All] |
| Month | 2026-01 | [Month].[Month].&[2026-01] |
| Month | 2026-02 | [Month].[Month].&[2026-02] |
| Month | 2026-03 | [Month].[Month].&[2026-03] |
| Product | All Product | [Product].[Product].[All] |
| Product | Service | [Product].[Product].&[Service] |
| Product | Parts | [Product].[Product].&[Parts] |
| Measures | Revenue | [Measures].[Revenue] |
| Measures | Cost | [Measures].[Cost] |
| Measures | Target | [Measures].[Target] |
| Measures | Profit | [Measures].[Profit] |
| Measures | Margin | [Measures].[Margin] |
| Measures | Count | [Measures].[Count] |
| Measures | Status | [Measures].[Status] |
| Measures | Trend | [Measures].[Trend] |
| Measures | Weight | [Measures].[Weight] |
| Cell / result | Current calculated value | Independent target | Type |
|---|---|---|---|
| J2 / First member caption | Blank cell | Caption; identity checked in linked queries | Unchecked |
| J3 / Second member caption | Blank cell | Caption; identity checked in linked queries | Unchecked |
| J4 / First member revenue | Blank cell | 410 | Unchecked |
| J5 / Second member revenue | Blank cell | 320 | Unchecked |
| J6 / Revenue measure caption | Blank cell | Caption; identity checked in linked queries | Unchecked |
| J7 / All observed revenue | Blank cell | 730 | Unchecked |
| J8 / Ranked region set | Blank cell | Caption; identity checked in linked queries | Unchecked |
| J9 / Members including empty | Blank cell | 3 | Unchecked |
| J10 / Top ranked member | Blank cell | Caption; identity checked in linked queries | Unchecked |
| J11 / Top region revenue | Blank cell | 410 | Unchecked |
| J12 / Original top caption | Blank cell | "North" | Unchecked |
| J13 / Top unique name | Blank cell | "[Region].[Region].&[North]" | Unchecked |
| J14 / Region / first month tuple | Blank cell | Caption; identity checked in linked queries | Unchecked |
| J15 / Tuple revenue | Blank cell | 150 | Unchecked |
| J16 / KPI value member | Blank cell | Caption; identity checked in linked queries | Unchecked |
| J17 / KPI actual | Blank cell | 730 | Unchecked |
| J18 / KPI goal member | Blank cell | Caption; identity checked in linked queries | Unchecked |
| J19 / KPI goal | Blank cell | 800 | Unchecked |
| J20 / KPI status member | Blank cell | Caption; identity checked in linked queries | Unchecked |
| J21 / KPI status | Blank cell | 0 | Unchecked |
| J22 / KPI trend member | Blank cell | Caption; identity checked in linked queries | Unchecked |
| J23 / KPI trend | Blank cell | -1 | Unchecked |
| J24 / KPI weight member | Blank cell | Caption; identity checked in linked queries | Unchecked |
| J25 / KPI weight | Blank cell | 1 | Unchecked |
| J26 / KPI current time member | Blank cell | Caption; identity checked in linked queries | Unchecked |
| J27 / Current month revenue | Blank cell | 190 | Unchecked |
| J28 / Set revenue | Blank cell | 730 | Unchecked |
| J29 / Reserved empty member / current facts | Blank cell | Empty text ("") | Unchecked |
| J30 / Original zero coordinate / current value | Blank cell | 0 | Unchecked |
| J31 / Inline member preserves ID | Blank cell | 410 | Unchecked |
| J32 / Unknown member error | Blank cell | #N/A (formula error) | Unchecked |
| J33 / Unknown connection error | Blank cell | #NAME? (formula error) | Unchecked |
| J34 / Conflicting hierarchy error | Blank cell | #N/A (formula error) | Unchecked |
| J35 / Unknown property error | Blank cell | #N/A (formula error) | Unchecked |
Reference and compatibility
Univer 1.0.4 / Apache-2.0. Calculation checks cover the supplied examples only. The browser worksheet does not implement every Excel feature, external service or connected data type.