CUBEKPIMEMBER function
The CUBEKPIMEMBER
function in Jspreadsheet Formulas Pro is a tool that allows you to retrieve specific information from a data cube. This function specifically gives you the value of a key performance indicator (KPI), a measurable value that demonstrates how effectively a company is achieving its key business objectives, and the name of the KPI in the data cube. This function is particularly useful when you're dealing with large datasets and need to quickly identify and extract specific performance metrics.
Documentation
Returns the value of a key performance indicator (KPI) and the name of the KPI in the cube.
Category
Cube
Syntax
CUBEKPIMEMBER(connection, kpi_name, [caption])
Parameter | Description |
---|---|
connection |
The name of the Excel data source connection. |
kpi_name |
The KPI property to return, such as "Value" , "Goal" , "Status" , "Trend" , or "Weight" . |
[caption] |
Optional. The caption of the KPI to retrieve from the cube. If omitted, the name parameter is used as the caption. |
Behavior
The CUBEKPIMEMBER
function in Jspreadsheet is used to return a key performance indicator (KPI) from a cube. The CUBEKPIMEMBER function returns a KPI property (such as value or goal) from an OLAP cube, using the data source connection and KPI name. In handling different types of data:
- Empty cells: If required arguments like connection or kpi_name are empty, the function returns a
#VALUE!
error. - Text: The function expects the cube, member expression, and KPI name to be text. If non-text values are provided, the function will return an error.
- Booleans: Boolean values are not applicable as arguments for this function. If provided, the function will return an error.
- Errors: If any of the arguments are cells that contain errors, the function will propagate that error.
Common Errors
Error | Description |
---|---|
#VALUE! | The function returns this error if the cube, member expression, or KPI name is not a text value, or if it fails to get data from the cube. |
#N/A | The function returns this error when it fails to find the specified KPI. |
Best Practices
- Always ensure that the cube, member expression, and KPI name are text values to avoid
#VALUE!
errors.- Make sure that the specified KPI exists in the cube to avoid
#N/A
errors.- Avoid referring to cells that may contain errors as arguments to the function to prevent those errors from being propagated.
- It's always good practice to validate your data before using it as an argument in the function. This can help to prevent errors and ensure that the function works as expected.
Usage
A few examples using the CUBEKPIMEMBER function.
CUBEKPIMEMBER("Sales Data","Revenue") returns the revenue KPI for the Sales Data cube along with its name
CUBEKPIMEMBER("Budget Data","Profit", "Net Profit") returns the net profit KPI for the Budget Data cube along with its name
CUBEKPIMEMBER("Inventory Data","Turnover Rate") returns the turnover rate KPI for the Inventory Data cube along with its name
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('N2QxNjZkZWFkZmIwYTBjMjg1ZjQ1NTU4MWI4ZjdhOTdmYjk0YTE3Y2IxMjE5NmNkMWI2NzRiNDU2MWY3YmEwMDU0ZWMzNDJlNzI4NTc0MWJkYmFhYjI5Yzk3YTEwNmY0MjQ2MDI2MzZkMTI3MzE4MWM4MjAyMDE1NzY5MjdiYjIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNekkxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
worksheets: [{
data: [
[
"Connection",
"KPI Name",
"KPI Value & Name"
],
[
"Sales Data",
"Revenue",
"=CUBEKPIMEMBER(A2,B2)"
],
[
"Budget Data",
"Profit",
"=CUBEKPIMEMBER(A3,B3,\"Net Profit\")"
],
[
"Inventory Data",
"Turnover Rate",
"=CUBEKPIMEMBER(A4,B4)"
],
[
"HR Data",
"Employee Satisfaction",
"=CUBEKPIMEMBER(A5,B5)"
]
]
}]
});
</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('N2QxNjZkZWFkZmIwYTBjMjg1ZjQ1NTU4MWI4ZjdhOTdmYjk0YTE3Y2IxMjE5NmNkMWI2NzRiNDU2MWY3YmEwMDU0ZWMzNDJlNzI4NTc0MWJkYmFhYjI5Yzk3YTEwNmY0MjQ2MDI2MzZkMTI3MzE4MWM4MjAyMDE1NzY5MjdiYjIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNekkxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default function App() {
// Spreadsheet array of worksheets
const spreadsheet = useRef();
// Worksheet data
const data = [
[
"Connection",
"KPI Name",
"KPI Value & Name"
],
[
"Sales Data",
"Revenue",
"=CUBEKPIMEMBER(A2,B2)"
],
[
"Budget Data",
"Profit",
"=CUBEKPIMEMBER(A3,B3,\"Net Profit\")"
],
[
"Inventory Data",
"Turnover Rate",
"=CUBEKPIMEMBER(A4,B4)"
],
[
"HR Data",
"Employee Satisfaction",
"=CUBEKPIMEMBER(A5,B5)"
]
];
// 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('N2QxNjZkZWFkZmIwYTBjMjg1ZjQ1NTU4MWI4ZjdhOTdmYjk0YTE3Y2IxMjE5NmNkMWI2NzRiNDU2MWY3YmEwMDU0ZWMzNDJlNzI4NTc0MWJkYmFhYjI5Yzk3YTEwNmY0MjQ2MDI2MzZkMTI3MzE4MWM4MjAyMDE1NzY5MjdiYjIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNekkxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// Set the extensions
jspreadsheet.setExtensions({ formula });
export default {
components: {
Spreadsheet,
Worksheet,
},
data() {
// Worksheet data
const data = [
[
"Connection",
"KPI Name",
"KPI Value & Name"
],
[
"Sales Data",
"Revenue",
"=CUBEKPIMEMBER(A2,B2)"
],
[
"Budget Data",
"Profit",
"=CUBEKPIMEMBER(A3,B3,\"Net Profit\")"
],
[
"Inventory Data",
"Turnover Rate",
"=CUBEKPIMEMBER(A4,B4)"
],
[
"HR Data",
"Employee Satisfaction",
"=CUBEKPIMEMBER(A5,B5)"
]
]
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('N2QxNjZkZWFkZmIwYTBjMjg1ZjQ1NTU4MWI4ZjdhOTdmYjk0YTE3Y2IxMjE5NmNkMWI2NzRiNDU2MWY3YmEwMDU0ZWMzNDJlNzI4NTc0MWJkYmFhYjI5Yzk3YTEwNmY0MjQ2MDI2MzZkMTI3MzE4MWM4MjAyMDE1NzY5MjdiYjIsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNekkxTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');
// 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: [
[
"Connection",
"KPI Name",
"KPI Value & Name"
],
[
"Sales Data",
"Revenue",
"=CUBEKPIMEMBER(A2,B2)"
],
[
"Budget Data",
"Profit",
"=CUBEKPIMEMBER(A3,B3,\"Net Profit\")"
],
[
"Inventory Data",
"Turnover Rate",
"=CUBEKPIMEMBER(A4,B4)"
],
[
"HR Data",
"Employee Satisfaction",
"=CUBEKPIMEMBER(A5,B5)"
]
]
}]
});
}
}