Buzz
Besides the CUBESET function in Excel for defining calculated element sets, readers can explore additional articles on Mytour to learn more about the FILTERXML function and CUBEKPIMEMBER function in Excel.
CUBESET function
1. Description of CUBESET function in Excel, determining calculated element sets
The CUBESET function in Excel determines calculated elements or sets by sending a set expression to the server, creating a set, and then returning that set to Microsoft Excel.
2. Syntax of CUBESET function in Excel, determining calculated element sets
CUBESET function in Excel, determining calculated element sets has the syntax:
CUBESET(connection, set_expression, [caption], [sort_order], [sort_by])
In which:
- The Connection parameter is mandatory. It's a text string name of the connection to the cube.
- The Set_expression parameter is mandatory. Set_expression text string returns a set of elements or tuples. Set_expression can also be a reference to a range of cells in Excel containing one or more elements, tuples, or sets included in that set.
- The Caption parameter is optional. It's a text string displayed in the cell instead of a caption, if one is defined in the cube.
- The Sort_order parameter is optional. The type of sorting, if any, to perform and can be one of the following:
The default value is 0. Sort Alpha for a set of tuples will sort by the last component in each tuple. For more information on these different sorting orders, you can access the Microsoft Office SQL Analysis Services help system.
- The Sort_by parameter is optional. It's a text string with a value to sort by. For example, to get a list of cities with the highest revenue, the set_expression would be a set of cities and sort_by would be the sales measure. Or, to get a list of cities with the highest population, the set_expression would be a set of cities and sort_by would be the population measure. If sort_order requires sort_by and sort_by is omitted, CUBESET returns the #VALUE! error value.
3. Important Notes about the CUBESET Function in Excel, Defining Calculated Element Sets
- When the Excel CUBESET function evaluates, it temporarily displays the message '# GETTING_DATA ...' in the cell until all data is retrieved.
- If the connection name is not a valid workbook connection stored within the workbook, the CUBESET function returns the #NAME? error. If the Online Analytical Processing (OLAP) server is not running, unavailable, or returns an error message, the CUBESET function returns the #NAME? error.
- If the set_expression function syntax is incorrect or the set contains at least one element with a different size than the remaining elements, the CUBESET function returns the #N/A error.
- If set_expression is longer than 255 characters, which is the limit for the function argument, the CUBESET function returns the #VALUE! error. To use text strings longer than 255 characters, you enter the text string into a cell (limited to 32,767 characters), and then use the cell reference as an argument.
- The Excel CUBESET function may return the #N/A error if you reference an object based on session, such as a calculated item or a named set, in a PivotTable when sharing a connection and the PivotTable is deleted or successfully converted to formulas. (In the Options tab, in the Tools group, click OLAP Tools, then click Convert to Formulas).
4. Example of the CUBESET Function in Excel, Defining Calculated Element Sets
Here are some examples of the CUBESET function in Excel for defining calculated element sets:
=CUBESET('Finance','Order([Product].[Product].[Product Category].Members,[Measures].[Unit Sales],ASC)','Products')
=CUBESET('Sales','[Product].[All Products].Children','Products',1,'[Measures].[Sales Amount]')
The article above on Mytour has just introduced you to the CUBESET function in Excel, which defines calculated element sets. In addition to the CUBESET function, readers can also refer to other articles on Mytour to learn more about some other common Excel functions.
With a data list containing thousands of rows of names, you have received a request to edit, capitalizing the first letter of each name. It is impractical to do this manually for each name. At this point, the function for capitalizing the first letter in Excel will be your special helper, which includes functions like PROPER, UPPER, depending on the intended use, which you can combine with each other.
