The `CUBESET` function in Excel is used to define a calculated set of members or tuples in a cube that are extracted from an OLAP (Online Analytical Processing) database. This function is useful when you need to analyze data from an OLAP cube and create dynamic reports.
Here’s a basic guide on how to use the CUBESET function:
Syntax
CUBESET(connection, set_expression, , [sort_order], [sort_by])
Arguments
- connection: This is a required argument. It specifies the connection to an OLAP cube. This is typically a connection string or a cell reference to a cell containing the connection string.
- set_expression: This is a required argument. It is an MDX (Multidimensional Expressions) set expression that returns a set of members or tuples from the cube.
- caption: This is an optional argument. It is the text string that is displayed in the cell instead of the caption, if you want to override the caption provided by the cube.
- sort_order: This is an optional argument. It specifies how the set is sorted, if at all. The possible values are:
- 0 – Unsorted (the default)
- 1 – Sorted ascending by value
- 2 – Sorted descending by value
- 3 – Sorted ascending by name
- 4 – Sorted descending by name
- 5 – Sorted ascending by key
- 6 – Sorted descending by key
- sort_by: This is an optional argument. You can specify a value on which to sort the set, especially useful when your `sort_order` is 1 or 2.
Example
Suppose you have a connection string named `SalesCubeConnection` and you want to create a set that consists of products from the “Product” dimension.
=CUBESET("SalesCubeConnection", "[Product].[All Products].Children", "Product Set")
In this example:
- `”SalesCubeConnection”` is the connection to the OLAP cube.
- `”[Product].[All Products].Children”` is the MDX expression that gets all child members of the “All Products” member in the “Product” dimension.
- `”Product Set”` is the caption for this set in Excel.
Tips
- Ensure that your OLAP cube is properly connected to Excel before attempting to use the `CUBESET` function.
- Understanding MDX expressions is crucial for effectively using this function, as it involves querying multidimensional data.
- Consider using the `CUBESET` function in conjunction with `CUBEVALUE` and other cube functions to create robust analytical reports.
By using the `CUBESET` function, you can define complex data sets which are dynamic and update automatically when the data in the cube changes.
Unlock Your Potential
Excel
Access
Power BI
