CUBESETCOUNT function

The CUBESETCOUNT function in Jspreadsheet Formulas Pro returns the number of items in a set defined by the CUBESET function or a valid MDX expression. It's particularly useful for validating the size of filtered or ranked sets in cube data models. Essentially, it performs a count of all the elements that are part of the set you're examining. This is particularly handy when you're dealing with large data sets and need a quick way to determine how many items you're working with. It simplifies data analysis by providing an automatic and accurate count.

Documentation

Returns the number of items in a set.

Syntax

CUBESETCOUNT(set)

Parameter	Description
`set`	A reference to a cell containing a CUBESET function, or a valid CUBESET expression.

Behavior

The CUBESETCOUNT function returns the number of items in a set. This function is specifically designed to work with OLAP cubes, which are multi-dimensional data structures used in Business Intelligence applications.

If the set is empty or the reference is invalid, the function will return 0.
The function does not handle text, booleans, or other non-numeric values as it specifically works with sets created by the CUBESET function.
If a cube function encounters a #N/A error, it could be because the OLAP server is not running, or the connection to the server has been lost.
It does not handle empty cells as it works with sets which are collections of tuples and not individual cell references.

Common Errors

Error	Description
#N/A	Returned when the set reference does not exist, or the cube connection is broken.
#VALUE!	Occurs when the set is not a valid reference or the formula syntax is incorrect.
#REF!	Appears when referencing a deleted or invalid cell.

Best practices

Always ensure that your OLAP server is running and your connection is stable before using CUBESETCOUNT.

Verify that the set reference used in the formula is valid and has been correctly created using the CUBESET function.

Be aware that CUBESETCOUNT only works with sets and cannot handle individual cell references or non-numeric values.

Always double-check your formula syntax to avoid #VALUE! errors.

Usage

A few examples using the CUBESETCOUNT function.

=CUBESETCOUNT(CUBESET("Sales Data", "[Product].[All Products].Children", "All Products"))
// Returns: Total number of products

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

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

// Create a new spreadsheet
jspreadsheet(document.getElementById('spreadsheet'), {
  worksheets: [{
    data: [
    [
        "Sales Data Analysis",
        ""
    ],
    [
        "Product Categories",
        "Count"
    ],
    [
        "Electronics, Clothing, Books",
        "=CUBESETCOUNT(\"SalesDB\",\"[Product].[Category].Members\")"
    ],
    [
        "Regional Sales Teams",
        "=CUBESETCOUNT(\"SalesDB\",\"[Sales].[Region].Children\")"
    ],
    [
        "Active Customers",
        "=CUBESETCOUNT(\"SalesDB\",\"[Customer].[Active].Members\")"
    ]
]
  }]
});
</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('M2Q0MDAxZDAzNzRlNWRlNTI1ZjRkNjAwNzBjY2EzOGZmMjljMDg3Mjk4N2ZlZjQ4ZWIxZTIwMmQ3ZDAyOTAwZTQ3YzY1NzgzNTBhOWViYWI2MmRkNzNlYmFiMTlmZDdkY2UzNjJiNDY0ZGMxNTY4MDhjMmVhOTNjNGIzYmE1N2IsZXlKamJHbGxiblJKWkNJNklpSXNJbTVoYldVaU9pSktjM0J5WldGa2MyaGxaWFFpTENKa1lYUmxJam94TnpVM01ETXhNek16TENKa2IyMWhhVzRpT2xzaWFuTndjbVZoWkhOb1pXVjBMbU52YlNJc0ltTnZaR1Z6WVc1a1ltOTRMbWx2SWl3aWFuTm9aV3hzTG01bGRDSXNJbU56WWk1aGNIQWlMQ0p6ZEdGamEySnNhWFI2TG1sdklpd2lkMlZpWTI5dWRHRnBibVZ5TG1sdklpd2lkMlZpSWl3aWJHOWpZV3hvYjNOMElsMHNJbkJzWVc0aU9pSXpOQ0lzSW5OamIzQmxJanBiSW5ZM0lpd2lkamdpTENKMk9TSXNJbll4TUNJc0luWXhNU0lzSW1Ob1lYSjBjeUlzSW1admNtMXpJaXdpWm05eWJYVnNZU0lzSW5CaGNuTmxjaUlzSW5KbGJtUmxjaUlzSW1OdmJXMWxiblJ6SWl3aWFXMXdiM0owWlhJaUxDSmlZWElpTENKMllXeHBaR0YwYVc5dWN5SXNJbk5sWVhKamFDSXNJbkJ5YVc1MElpd2ljMmhsWlhSeklpd2lZMnhwWlc1MElpd2ljMlZ5ZG1WeUlpd2ljMmhoY0dWeklpd2labTl5YldGMElsMHNJbVJsYlc4aU9uUnlkV1Y5');

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

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

    // Worksheet data
    const data = [
    [
        "Sales Data Analysis",
        ""
    ],
    [
        "Product Categories",
        "Count"
    ],
    [
        "Electronics, Clothing, Books",
        "=CUBESETCOUNT(\"SalesDB\",\"[Product].[Category].Members\")"
    ],
    [
        "Regional Sales Teams",
        "=CUBESETCOUNT(\"SalesDB\",\"[Sales].[Region].Children\")"
    ],
    [
        "Active Customers",
        "=CUBESETCOUNT(\"SalesDB\",\"[Customer].[Active].Members\")"
    ]
];

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

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

export default {
    components: {
        Spreadsheet,
        Worksheet,
    },
    data() {
        // Worksheet data
        const data = [
    [
        "Sales Data Analysis",
        ""
    ],
    [
        "Product Categories",
        "Count"
    ],
    [
        "Electronics, Clothing, Books",
        "=CUBESETCOUNT(\"SalesDB\",\"[Product].[Category].Members\")"
    ],
    [
        "Regional Sales Teams",
        "=CUBESETCOUNT(\"SalesDB\",\"[Sales].[Region].Children\")"
    ],
    [
        "Active Customers",
        "=CUBESETCOUNT(\"SalesDB\",\"[Customer].[Active].Members\")"
    ]
]

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

// 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 Analysis",
        ""
    ],
    [
        "Product Categories",
        "Count"
    ],
    [
        "Electronics, Clothing, Books",
        "=CUBESETCOUNT(\"SalesDB\",\"[Product].[Category].Members\")"
    ],
    [
        "Regional Sales Teams",
        "=CUBESETCOUNT(\"SalesDB\",\"[Sales].[Region].Children\")"
    ],
    [
        "Active Customers",
        "=CUBESETCOUNT(\"SalesDB\",\"[Customer].[Active].Members\")"
    ]
]
            }]
        });
    }
}

Edit this page on GitHub