CUBEVALUE function

In Jspreadsheet Formulas Pro, the CUBEVALUE function is a tool that retrieves the value of a specific cell in a cube. This cube is essentially a data set analyzed by a Multidimensional Expressions (MDX) query. Using this function, you can access and manipulate large amounts of complex data easily and efficiently. It's an advanced function and a powerful tool in handling and managing multidimensional data in Jspreadsheet.

Documentation

Returns the value of a cell in a cube, as calculated by a Multidimensional Expressions (MDX) query.

Syntax

CUBEVALUE(connection, member_expression1, [member_expression2], …)

Parameter	Description
`connection`	Text name of the OLAP data source (e.g., "Sales Data").
`member_expression`	One or more CUBEMEMBER results, MDX member strings, or tuples that identify the cell to return from the cube.

Behavior

The 'CUBEVALUE' function returns an aggregated value from a cube. A cube in this context refers to a multi-dimensional data model used in business intelligence applications. Here's how it handles various data types:

Empty Cells: If the connection and members arguments are empty, the function will return an error.
Text: The 'CUBEVALUE' function expects text arguments that represent connections and members in the cube.
Booleans: Booleans are not directly handled by the 'CUBEVALUE' function.
Errors: If the function encounters an error, it will display the error message. For example, if a specified member doesn't exist in the cube, it will return a '#N/A' error.

Common Errors

Error	Description
#N/A	The 'CUBEVALUE' function returns this error if the specified member or connection does not exist in the cube.
#VALUE!	This error is displayed if the function receives invalid inputs. For example, it will return this error if given a non-string or non-number argument.

Best practices

Always ensure that the connection and members arguments are valid and exist in the cube. Invalid values will lead to errors.

Be careful when handling errors. Consider using error handling functions to manage any errors that might occur when using the 'CUBEVALUE' function.

Make sure your cube is up-to-date. The 'CUBEVALUE' function will return values based on the current state of the cube.

Use the 'CUBEMEMBER' function to get a valid member for use with the 'CUBEVALUE' function. This can help avoid errors and ensure you're pulling the correct data.

Usage

A few examples using the CUBEVALUE function.

// Static MDX strings
=CUBEVALUE("Sales Data",
           "[Date].[Calendar Year].&[2022]",
           "[Product].[All Products].[Bikes]",
           "[Measures].[Revenue]")

// With references
A1: =CUBEMEMBER("Sales Data", "[Department].[All Departments].[Sales]")
A2: =CUBEMEMBER("Sales Data", "[Employee].[All Employees].[Jane]")
A3: =CUBEMEMBER("Sales Data", "[Measures].[Salary]")
B1: =CUBEVALUE("Sales Data", A1, A2, A3)

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('MmI3Yjg4NmM5MDRlMGVmY2U3MDBmNjg5MDE2ZWRkZWNlNTM0MmVjN2E3NDljNzA5MjI4Y2M0NTIyMWQzNDM4Mzc1ZTk1OWIxNjJiMzg4ZGNjZmY3OGY0MWNlZDk1NTU5YWU3Yzk4ODFlNWNjYTI0NGFhMTg5Nzk1M2RkODc4MjYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNREk1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Connection",
        "MDX Query",
        "Result"
    ],
    [
        "Sales Data",
        "([Date].[Calendar Year].&[2022],[Product].[All Products].[Bikes],[Measures].[Revenue])",
        "=CUBEVALUE(A2,B2)"
    ],
    [
        "Budget Data",
        "([Department].[All Departments].[Sales],[Employee].[All Employees].[Jane],[Measures].[Salary])",
        "=CUBEVALUE(A3,B3)"
    ],
    [
        "Inventory Data",
        "([Store].[Store Name].[Seattle],[Product].[All Products].[Accessories],[Measures].[Inventory Count])",
        "=CUBEVALUE(A4,B4)"
    ]
]
  }]
});
</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('MmI3Yjg4NmM5MDRlMGVmY2U3MDBmNjg5MDE2ZWRkZWNlNTM0MmVjN2E3NDljNzA5MjI4Y2M0NTIyMWQzNDM4Mzc1ZTk1OWIxNjJiMzg4ZGNjZmY3OGY0MWNlZDk1NTU5YWU3Yzk4ODFlNWNjYTI0NGFhMTg5Nzk1M2RkODc4MjYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNREk1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "Connection",
        "MDX Query",
        "Result"
    ],
    [
        "Sales Data",
        "([Date].[Calendar Year].&[2022],[Product].[All Products].[Bikes],[Measures].[Revenue])",
        "=CUBEVALUE(A2,B2)"
    ],
    [
        "Budget Data",
        "([Department].[All Departments].[Sales],[Employee].[All Employees].[Jane],[Measures].[Salary])",
        "=CUBEVALUE(A3,B3)"
    ],
    [
        "Inventory Data",
        "([Store].[Store Name].[Seattle],[Product].[All Products].[Accessories],[Measures].[Inventory Count])",
        "=CUBEVALUE(A4,B4)"
    ]
];

    // 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('MmI3Yjg4NmM5MDRlMGVmY2U3MDBmNjg5MDE2ZWRkZWNlNTM0MmVjN2E3NDljNzA5MjI4Y2M0NTIyMWQzNDM4Mzc1ZTk1OWIxNjJiMzg4ZGNjZmY3OGY0MWNlZDk1NTU5YWU3Yzk4ODFlNWNjYTI0NGFhMTg5Nzk1M2RkODc4MjYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNREk1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Connection",
        "MDX Query",
        "Result"
    ],
    [
        "Sales Data",
        "([Date].[Calendar Year].&[2022],[Product].[All Products].[Bikes],[Measures].[Revenue])",
        "=CUBEVALUE(A2,B2)"
    ],
    [
        "Budget Data",
        "([Department].[All Departments].[Sales],[Employee].[All Employees].[Jane],[Measures].[Salary])",
        "=CUBEVALUE(A3,B3)"
    ],
    [
        "Inventory Data",
        "([Store].[Store Name].[Seattle],[Product].[All Products].[Accessories],[Measures].[Inventory Count])",
        "=CUBEVALUE(A4,B4)"
    ]
]

        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('MmI3Yjg4NmM5MDRlMGVmY2U3MDBmNjg5MDE2ZWRkZWNlNTM0MmVjN2E3NDljNzA5MjI4Y2M0NTIyMWQzNDM4Mzc1ZTk1OWIxNjJiMzg4ZGNjZmY3OGY0MWNlZDk1NTU5YWU3Yzk4ODFlNWNjYTI0NGFhMTg5Nzk1M2RkODc4MjYsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBNREk1TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

// 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",
        "MDX Query",
        "Result"
    ],
    [
        "Sales Data",
        "([Date].[Calendar Year].&[2022],[Product].[All Products].[Bikes],[Measures].[Revenue])",
        "=CUBEVALUE(A2,B2)"
    ],
    [
        "Budget Data",
        "([Department].[All Departments].[Sales],[Employee].[All Employees].[Jane],[Measures].[Salary])",
        "=CUBEVALUE(A3,B3)"
    ],
    [
        "Inventory Data",
        "([Store].[Store Name].[Seattle],[Product].[All Products].[Accessories],[Measures].[Inventory Count])",
        "=CUBEVALUE(A4,B4)"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub