CUBEMEMBERPROPERTY function

The CUBEMEMBERPROPERTY function in Jspreadsheet Formulas Pro is a tool that allows you to retrieve the value property associated with a specific cube member, using a connection to an OLAP data source and a valid MDX member expression. A data cube is a multi-dimensional array of values, typically used to describe a dataset. This function is commonly used in data analysis and reporting where you need to extract certain properties from your dataset. It's like asking for specific details about a certain member of your data set.

Documentation

Returns the value of a member property from the cube.

Syntax

CUBEMEMBERPROPERTY(connection, member_expression, property_name)

Parameter	Description
`connection`	The name of the data source connection.
`member_expression`	A valid MDX expression that identifies a single member in the cube (e.g., `[Product].[Category].&[Bikes]`
`property_name`	The name of the member property to retrieve from the cube.

Behavior

The CUBEMEMBERPROPERTY function is used to return the value of a member property from a cube. It does this by using a connection to an OLAP (Online Analytical Processing) cube. Here is how it handles different inputs:

Empty Cells: If the CUBEMEMBERPROPERTY function references an empty cell, it will return an error since a connection or property cannot be found.
Text: The function requires text inputs for specifying the connection and member_expression. If it doesn't find the specified member property in the cube, it will return an error.
Booleans: This function does not interact with boolean values.
Errors: If there are any errors (like referencing non-existing connections or properties), the CUBEMEMBERPROPERTY function will return an appropriate error message.

Common Errors

Error	Description
#N/A	This error is displayed when the function cannot find the specified connection or property.
#VALUE!	This error is displayed when the given arguments are of the wrong data type.
#REF!	This error occurs when the referred cell is not valid.

Best practices

Always verify that your connection to the OLAP cube is active and stable before using the CUBEMEMBERPROPERTY function.

Ensure that the member_expression correctly refers to a member in the cube. Incorrect references will result in errors.

Use clear and specific property names to avoid confusion and errors.

To avoid errors, handle empty cells that this function might refer to in your worksheet.

Usage

A few examples using the CUBEMEMBERPROPERTY function.

CUBEMEMBERPROPERTY("Sales Data","[Date].[Calendar Year].&[2022]","Calendar Year") returns the calendar year for the 2022 member from the Date dimension in the Sales Data cube  
CUBEMEMBERPROPERTY("Budget Data","[Product].[All Products].[Bikes]","Color") returns the color property for the Bikes member from the Product dimension in the Budget Data cube  
CUBEMEMBERPROPERTY("Inventory Data","[Store].[Store Name].&[Seattle]","Region") returns the region property for the Seattle store member from the Store dimension in the Inventory Data cube

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

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Connection",
        "Member Expression",
        "Property Value"
    ],
    [
        "Sales Data",
        "[Product].[Category].&[Electronics]",
        "=CUBEMEMBERPROPERTY(A2,B2,\"Department\")"
    ],
    [
        "Budget Data",
        "[Employee].[Name].&[John Smith]",
        "=CUBEMEMBERPROPERTY(A3,B3,\"Title\")"
    ],
    [
        "Inventory Data",
        "[Location].[Store].&[Store001]",
        "=CUBEMEMBERPROPERTY(A4,B4,\"Manager\")"
    ]
]
  }]
});
</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('MjYyYjFjNDI1MWUwMTViYWU5MTBhODRmZDJhYWI1MWE2ZmU0YzQyOTUzODM4N2RlMDlhMDgyNGNiZGNlZmVmNGE3ODQ2YWM5ZTk1ZTNhZGZlODg5OTQ5ZDBmZTkwZDRjYzg4M2VlMGUwM2Q4ZjZhZDdkZTk5YTNhYTY3ODRjNGQsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVNE56YzBPRFEwTENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "Connection",
        "Member Expression",
        "Property Value"
    ],
    [
        "Sales Data",
        "[Product].[Category].&[Electronics]",
        "=CUBEMEMBERPROPERTY(A2,B2,\"Department\")"
    ],
    [
        "Budget Data",
        "[Employee].[Name].&[John Smith]",
        "=CUBEMEMBERPROPERTY(A3,B3,\"Title\")"
    ],
    [
        "Inventory Data",
        "[Location].[Store].&[Store001]",
        "=CUBEMEMBERPROPERTY(A4,B4,\"Manager\")"
    ]
];

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

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Connection",
        "Member Expression",
        "Property Value"
    ],
    [
        "Sales Data",
        "[Product].[Category].&[Electronics]",
        "=CUBEMEMBERPROPERTY(A2,B2,\"Department\")"
    ],
    [
        "Budget Data",
        "[Employee].[Name].&[John Smith]",
        "=CUBEMEMBERPROPERTY(A3,B3,\"Title\")"
    ],
    [
        "Inventory Data",
        "[Location].[Store].&[Store001]",
        "=CUBEMEMBERPROPERTY(A4,B4,\"Manager\")"
    ]
]

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

// 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",
        "Member Expression",
        "Property Value"
    ],
    [
        "Sales Data",
        "[Product].[Category].&[Electronics]",
        "=CUBEMEMBERPROPERTY(A2,B2,\"Department\")"
    ],
    [
        "Budget Data",
        "[Employee].[Name].&[John Smith]",
        "=CUBEMEMBERPROPERTY(A3,B3,\"Title\")"
    ],
    [
        "Inventory Data",
        "[Location].[Store].&[Store001]",
        "=CUBEMEMBERPROPERTY(A4,B4,\"Manager\")"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub