Cube Functions
CUBEMEMBER | This function will fetch the member or tuple defined by the member_expression. For example, (from the illustration above,) the formula: =CUBEMEMBER ("Adventure Works", "[Sales Reason].[On Promotion]") returns the member named "On Promotion" from the "Sales Reason" dimension of the Adventure Works cube. |
CUBEVALUE | This function will fetch the aggregated value from the cube filtered by the various member_expression arguments. For example, the formula: =CUBEVALUE ("Adventure Works","[Measures].[Gross Profit]","[Product].[Category].[Bikes]","[Date].[Fiscal Year].[FY 2004]") returns the value $5,035,271.22 which is the aggregated amount in the Adventure Works cube for Gross Profit for Bikes in Fiscal 2004. |
CUBESET | This function will fetch the set that is defined by the set_expression parameter. Optional parameters allow you to specify the ordering of the set as well as the caption to be displayed in the Excel cell that contains this formula. (Note that the set itself won't have a display value.) For example, the formula: =CUBESET ("Adventure Works","[Customer].[Customer Geography].[All Customers].children","Countries") returns the set of countries in the Customer Geography hierarchy and shows "Countries" as the cell's display value. |
CUBECUBESETCOUNT | This function returns the number of items in a set. Typically the argument to this function will be a CUBESET function or a reference to a CUBESET function. |
CUBERANKEDMEMBER | This function returns the Nth item from a set. This can be very useful when building a Top N (or Bottom N) report in Excel. |
CUBEMEMBERPROPERTY | This function returns a property of a member in the OLAP cube. |
CUBEKPIMEMBER | This function returns a KPI (Key Performance Indicator) from the OLAP cube. |
CUBEMEMBER This function will fetch the member or tuple defined by the member_expression. For example, (from the illustration above,) the formula: =CUBEMEMBER ("Adventure Works", "[Sales Reason].[On Promotion]") returns the member named "On Promotion" from the "Sales Reason" dimension of the Adventure Works cube. |
CUBEVALUE This function will fetch the aggregated value from the cube filtered by the various member_expression arguments. For example, the formula: =CUBEVALUE ("Adventure Works","[Measures].[Gross Profit]","[Product].[Category].[Bikes]","[Date].[Fiscal Year].[FY 2004]") returns the value $5,035,271.22 which is the aggregated amount in the Adventure Works cube for Gross Profit for Bikes in Fiscal 2004. |
CUBESET This function will fetch the set that is defined by the set_expression parameter. Optional parameters allow you to specify the ordering of the set as well as the caption to be displayed in the Excel cell that contains this formula. (Note that the set itself won't have a display value.) For example, the formula: =CUBESET ("Adventure Works","[Customer].[Customer Geography].[All Customers].children","Countries") returns the set of countries in the Customer Geography hierarchy and shows "Countries" as the cell's display value. |
CUBECUBESETCOUNT This function returns the number of items in a set. Typically the argument to this function will be a CUBESET function or a reference to a CUBESET function. |
CUBERANKEDMEMBER This function returns the Nth item from a set. This can be very useful when building a Top N (or Bottom N) report in Excel. |
CUBEMEMBERPROPERTY This function returns a property of a member in the OLAP cube. |
CUBEKPIMEMBER This function returns a KPI (Key Performance Indicator) from the OLAP cube. |
These functions are useful when creating a pivot table is not necessary.
There are other scenarios that involve bringing OLAP data from SQL Server Analysis Services into Excel, though, that are not as well suited to using a PivotTable.
CUBE functions (except for CUBESETCOUNT) return a result which is more complex than this. CUBE functions return two distinct values. One is the value that is displayed in the cell. But there is also a second hidden value which can be thought of as an MDX expression defining the result. When one CUBE function uses another CUBE function as its argument, the argument takes on the MDX value rather than the display value
New Cube function can be used to extract OLAP data (sets and values) from Analysis Services)
OLAP formulas can be automatically generated when you convert pivottable formulas to cell formulas.
OLAP formulas can be generated when you convert Pivot Table formulas to cell formulas.
© 2024 Better Solutions Limited. All Rights Reserved. © 2024 Better Solutions Limited TopPrevNext