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.
Category
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('OGUyNGE1MzQ0ZDI1OTdjMDY0OTE2OGVmYzk4ZjhkNzNhMGZlY2Y3ZTNhZjdlYTJmYjUxNTE4YTZkNjZmOGQ3M2Q2YjU5ZjhjOGQyZTc1OTRjOTdmNDlmY2Y4ZDBlYTk0YTM3YTlhYmNlNjI2M2FmMGIyNDM5MjM3OGJlNWU1ZmMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGMyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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('OGUyNGE1MzQ0ZDI1OTdjMDY0OTE2OGVmYzk4ZjhkNzNhMGZlY2Y3ZTNhZjdlYTJmYjUxNTE4YTZkNjZmOGQ3M2Q2YjU5ZjhjOGQyZTc1OTRjOTdmNDlmY2Y4ZDBlYTk0YTM3YTlhYmNlNjI2M2FmMGIyNDM5MjM3OGJlNWU1ZmMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGMyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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('OGUyNGE1MzQ0ZDI1OTdjMDY0OTE2OGVmYzk4ZjhkNzNhMGZlY2Y3ZTNhZjdlYTJmYjUxNTE4YTZkNjZmOGQ3M2Q2YjU5ZjhjOGQyZTc1OTRjOTdmNDlmY2Y4ZDBlYTk0YTM3YTlhYmNlNjI2M2FmMGIyNDM5MjM3OGJlNWU1ZmMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGMyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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('OGUyNGE1MzQ0ZDI1OTdjMDY0OTE2OGVmYzk4ZjhkNzNhMGZlY2Y3ZTNhZjdlYTJmYjUxNTE4YTZkNjZmOGQ3M2Q2YjU5ZjhjOGQyZTc1OTRjOTdmNDlmY2Y4ZDBlYTk0YTM3YTlhYmNlNjI2M2FmMGIyNDM5MjM3OGJlNWU1ZmMsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVMk56a3pNRGMyTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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"
]
]
}]
});
}
}