CUBERANKEDMEMBER function

The CUBERANKEDMEMBER function in Jspreadsheet Formulas Pro is a tool that provides the nth ranked member from a selected set in a data cube. In simpler terms, it returns the member at a specific rank (position) from an MDX-defined set in an OLAP data cube. This function is particularly useful when you are dealing with a large amount of data and need to quickly identify items based on their rank or position.

Documentation

Returns the nth ranked member from a set in a cube.

Syntax

CUBERANKEDMEMBER(connection, set_expression, rank)

Parameter	Description
`connection`	The name of the Excel data source connection.
`set_expression`	A valid Multidimensional Expressions (MDX) expression that returns a set from the cube.
`rank`	A 1-based integer indicating the position of the member to retrieve from the MDX set.

Behavior

The CUBERANKEDMEMBER function is used to return the nth, or ranked, member in a set. This function is mainly used in conjunction with OLAP cubes, which are multi-dimensional arrays of data. Here's how it handles different types of data:

Empty Cells: This function will generally ignore empty cells in its operation, as it mainly operates on the OLAP cube provided as its argument.
Text: Any text provided to this function that is not a valid part of the OLAP cube or a valid numeric index will result in an error.
Booleans: Boolean values are not applicable to this function.
Errors: If any errors occur in the data retrieval from the OLAP cube, this function will return the corresponding error.

Common Errors

Error	Description
#VALUE!	Returned when the connection name or MDX set expression is invalid or not text.
#N/A	Returned if the specified rank is out of range (e.g., greater than the set size).
#NUM!	Returned if the rank argument is non-numeric or less than 1.

Best practices

Make sure that the cube name and set expression provided to CUBERANKEDMEMBER are valid and recognized by the cube.

The rank argument should be a positive integer and within the range of the total members in the set.

Always handle possible errors with appropriate error handling functions or techniques to ensure your spreadsheet continues to function properly even when the CUBERANKEDMEMBER function encounters an error.

As this function interacts with OLAP cubes, which are typically large data structures, try to use it in an optimized manner to avoid potential performance issues.

Usage

A few examples using the CUBERANKEDMEMBER function.

CUBERANKEDMEMBER("Sales Data", "[Product].[Product Line].&[Mountain].[Products].Children", 1)
// Returns: First product in the Mountain product line (likely top-sorted by default cube logic)

CUBERANKEDMEMBER("Budget Data", "ORDER([Employee].[Employees].MEMBERS, [Measures].[Salary], DESC)", 2)
// Returns: Second highest-paid employee (requires ordered MDX set)

CUBERANKEDMEMBER("Inventory Data", "[Geography].[Country].MEMBERS", 5)
// Returns: Fifth member in the Country hierarchy

Interactive Spreadsheet Demo

<html>
<script src="https://jspreadsheet.com/v11/jspreadsheet.js"></script>
<script src="https://jsuites.net/v5/jsuites.js"></script>
<link rel="stylesheet" href="https://jsuites.net/v5/jsuites.css" type="text/css" />
<link rel="stylesheet" href="https://jspreadsheet.com/v11/jspreadsheet.css" type="text/css" />

<link rel="stylesheet" href="https://fonts.googleapis.com/css?family=Material+Icons" />
<script src="https://cdn.jsdelivr.net/npm/@jspreadsheet/formula-pro/dist/index.min.js"></script>

<div id="spreadsheet"></div>

<script>
// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NDMyYmM2MmVjMjhkNTk1ZjhiNGQyNTkxZjYwMjI5NTUxMmVmNTQ5ZWUyMmFiNTI1MzYwYWIxMmFiODJlMDE1YWM0YTg2MWUzNDY4NjZmNDUxMTAwNTA0MmQ4ZmM4ZmY2MjcyZmRkZDNlMTAxZTgzZDA1NDFiYzQ2OWQ5YzQ2MDQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQTBNRFkxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Sales Data Connection",
        "Product Rankings",
        "Top Products"
    ],
    [
        "Sales Data",
        "=CUBERANKEDMEMBER(A2,\"[Product].[Category].&[Electronics].[Items].Children\",1)",
        "Best Selling Electronics"
    ],
    [
        "Sales Data",
        "=CUBERANKEDMEMBER(A3,\"[Product].[Category].&[Electronics].[Items].Children\",2)",
        "Second Best Electronics"
    ],
    [
        "Sales Data",
        "=CUBERANKEDMEMBER(A4,\"[Product].[Category].&[Clothing].[Items].Children\",1)",
        "Best Selling Clothing"
    ],
    [
        "Sales Data",
        "=CUBERANKEDMEMBER(A5,\"[Employee].[Department].&[Sales].[Staff].Children\",3)",
        "Third Top Salesperson"
    ]
]
  }]
});
</script>
</html>

import React, { useRef } from "react";
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/react";
import formula from "@jspreadsheet/formula-pro";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";

// Set license
jspreadsheet.setLicense('NDMyYmM2MmVjMjhkNTk1ZjhiNGQyNTkxZjYwMjI5NTUxMmVmNTQ5ZWUyMmFiNTI1MzYwYWIxMmFiODJlMDE1YWM0YTg2MWUzNDY4NjZmNDUxMTAwNTA0MmQ4ZmM4ZmY2MjcyZmRkZDNlMTAxZTgzZDA1NDFiYzQ2OWQ5YzQ2MDQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQTBNRFkxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default function App() {
    // Spreadsheet array of worksheets
    const spreadsheet = useRef();

    // Worksheet data
    const data = [
    [
        "Sales Data Connection",
        "Product Rankings",
        "Top Products"
    ],
    [
        "Sales Data",
        "=CUBERANKEDMEMBER(A2,\"[Product].[Category].&[Electronics].[Items].Children\",1)",
        "Best Selling Electronics"
    ],
    [
        "Sales Data",
        "=CUBERANKEDMEMBER(A3,\"[Product].[Category].&[Electronics].[Items].Children\",2)",
        "Second Best Electronics"
    ],
    [
        "Sales Data",
        "=CUBERANKEDMEMBER(A4,\"[Product].[Category].&[Clothing].[Items].Children\",1)",
        "Best Selling Clothing"
    ],
    [
        "Sales Data",
        "=CUBERANKEDMEMBER(A5,\"[Employee].[Department].&[Sales].[Staff].Children\",3)",
        "Third Top Salesperson"
    ]
];

    // Render component
    return (
        <Spreadsheet ref={spreadsheet}>
            <Worksheet data={data} />
        </Spreadsheet>
    );
}

<template>
    <Spreadsheet ref="spreadsheet">
        <Worksheet :data="data" />
    </Spreadsheet>
</template>

<script>
import { Spreadsheet, Worksheet, jspreadsheet } from "@jspreadsheet/vue";
import "jsuites/dist/jsuites.css";
import "jspreadsheet/dist/jspreadsheet.css";
import formula from "@jspreadsheet/formula-pro";

// Set license
jspreadsheet.setLicense('NDMyYmM2MmVjMjhkNTk1ZjhiNGQyNTkxZjYwMjI5NTUxMmVmNTQ5ZWUyMmFiNTI1MzYwYWIxMmFiODJlMDE1YWM0YTg2MWUzNDY4NjZmNDUxMTAwNTA0MmQ4ZmM4ZmY2MjcyZmRkZDNlMTAxZTgzZDA1NDFiYzQ2OWQ5YzQ2MDQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQTBNRFkxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Sales Data Connection",
        "Product Rankings",
        "Top Products"
    ],
    [
        "Sales Data",
        "=CUBERANKEDMEMBER(A2,\"[Product].[Category].&[Electronics].[Items].Children\",1)",
        "Best Selling Electronics"
    ],
    [
        "Sales Data",
        "=CUBERANKEDMEMBER(A3,\"[Product].[Category].&[Electronics].[Items].Children\",2)",
        "Second Best Electronics"
    ],
    [
        "Sales Data",
        "=CUBERANKEDMEMBER(A4,\"[Product].[Category].&[Clothing].[Items].Children\",1)",
        "Best Selling Clothing"
    ],
    [
        "Sales Data",
        "=CUBERANKEDMEMBER(A5,\"[Employee].[Department].&[Sales].[Staff].Children\",3)",
        "Third Top Salesperson"
    ]
]

        return {
            data
        };
    }
}
</script>

import { Component, ViewChild, ElementRef } from "@angular/core";
import jspreadsheet from "jspreadsheet";
import * as formula from "@jspreadsheet/formula-pro";

// Set your JSS license key (The following key only works for one day)
jspreadsheet.setLicense('NDMyYmM2MmVjMjhkNTk1ZjhiNGQyNTkxZjYwMjI5NTUxMmVmNTQ5ZWUyMmFiNTI1MzYwYWIxMmFiODJlMDE1YWM0YTg2MWUzNDY4NjZmNDUxMTAwNTA0MmQ4ZmM4ZmY2MjcyZmRkZDNlMTAxZTgzZDA1NDFiYzQ2OWQ5YzQ2MDQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE5UQTBNRFkxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// Set the extensions
jspreadsheet.setExtensions({ formula });

@Component({
    standalone: true,
    selector: "app-root",
    template: `<div #spreadsheet></div>`
})
export class AppComponent {
    @ViewChild("spreadsheet") spreadsheet: ElementRef;
    // Worksheets
    worksheets: jspreadsheet.worksheetInstance[];

    // Create a new data grid
    ngAfterViewInit() {
        // Create spreadsheet
        this.worksheets = jspreadsheet(this.spreadsheet.nativeElement, {
            worksheets: [{
                data: [
    [
        "Sales Data Connection",
        "Product Rankings",
        "Top Products"
    ],
    [
        "Sales Data",
        "=CUBERANKEDMEMBER(A2,\"[Product].[Category].&[Electronics].[Items].Children\",1)",
        "Best Selling Electronics"
    ],
    [
        "Sales Data",
        "=CUBERANKEDMEMBER(A3,\"[Product].[Category].&[Electronics].[Items].Children\",2)",
        "Second Best Electronics"
    ],
    [
        "Sales Data",
        "=CUBERANKEDMEMBER(A4,\"[Product].[Category].&[Clothing].[Items].Children\",1)",
        "Best Selling Clothing"
    ],
    [
        "Sales Data",
        "=CUBERANKEDMEMBER(A5,\"[Employee].[Department].&[Sales].[Staff].Children\",3)",
        "Third Top Salesperson"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub