Using cube functions
A pivot table can be a very useful tool for analyzing data. The ability to dynamically drop a hierarchy from the model and expose all members, then sorting, filtering, and restricting those members can be used in a large number of analytical situations. However, one problem with the pivot table is that all the elements of the object (Filters, rows, and columns) are connected and cannot be formatted with the complete discretion of the user. For example, the user cannot insert a row between rows in the pivot table. While there are some options for formatting a pivot table, these mainly relate to styles, and often the user wishes to have a larger amount of control over what they are presenting. Additionally, using the formula that refers to a cell in the pivot table is troublesome, because the pivot table can change and invalidate the formula.
This recipe examines the use of the CUBE FUNCTION formulas, which allows the user to access model data without the restrictions imposed...