#9 Tip Custom Report Layouts with the Excel CUBE Functions

​Seven CUBE functions were introduced with Excel 2007 to enable retrieving Analysis Services data model (including PowerPIvot data models) members, sets, and values. These functions play an important role in allowing a report author to create custom report layouts cell-by-cell, and to allow interactivity through user input and filter features, like slicers and the timeline (introduced in Excel 2013).

cube

An easy way to become familiar with the CUBE functions is to create a PivotTable based on an Analysis Services data model. The PivotTable can then be converted to formulas: Each cell of the PivotTable is converted into a formula that uses a CUBE function to retrieve a member or values.

In brief, the CUBEMEMBER function returns a single member, or tuple, from the data model. The CUBESET function retrieves a set based on any valid set expression, and the CUBESETCOUNT function allows determining the number of members in a CUBESET result. The CUBERANKEDMEMBER function retrieved a member from a CUBESET result. The CUBEVALUE function retrieves a value from the data model based on input filters. Cube members, sets, slicers and timelines can be used to filter the CUBEVALUE function. Finally, the CUBEKPIMEMBER function enables retrieving KPI metrics from a data model KPI, and the CUBEMEMBERPROPERTY function enables retrieving a member property for a member (supported on multidimensional data models only).

Generally, the CUBE functions work well for dashboard-style layouts (see image), especially when published to SharePoint or Office 365 for rendering in a browser. Their use would not be recommended for report layouts that could be generated with PivotTables, PivotCharts or Power View.

Working with CUBE functions is fully covered in the Microsoft BI Academy Self-Service BI course, written and presented by the author of this article.