Microsoft Analysis Services provides an OLAP (Online Analytical Processing) engine that provides fast access to aggregated business data such as sales, budgets, inventory and costs. Analysis Services publishes data in form of a cube which enables you to filter and group data by predefined categories or dimensions. A cube is often used with Excel PivotTable feature which enables you to browse data interactively in Excel with just a few mouse clicks.
PivotTable report is an excellent tool for structured browsing. On the other hand, it is too inflexible for many common reporting needs. I will describe some of these scenarios later.
The new “Convert to formulas” feature in Excel 2007 addresses this gap. Using this feature, you can convert the cells in a PivotTable to formulas. This feature converts the cells to formulas that use Cube functions. Once you have done that, you can then take full advantage of all Excel features (such as layouts, formats, coloring, annotations, etc.) to produce a report the way you desire. You are no longer limited by the PivotTable’s rigid predefined structure. In addition to converting PivotTable cells to formulas, you can also enter new formulas using Cube functions to create a custom report from scratch or add information to an existing report converted from a PivotTable.
When you convert cells to formulas, these formulas access the same data as the PivotTable report and can be refreshed to see up-to-date results. However, with the possible exception of report filters, you no longer have access to the interactive features of a PivotTable report, such as filtering, sorting, or expanding and collapsing levels.
Common scenarios for converting PivotTable reports to formulas
The following are a few examples of what you can do after you convert PivotTable cells to worksheet formulas to customize the layout of the converted cells.
Rearrange and delete cells
Example: There are two divisions in your company: Bicycles and Accessories. For the sales report for the Bicycles division managers, you want to include sales data for both divisions but the cost information only for the Bicycles division. You also want to rearrange and group certain products in the report and add subtotals for the group. You want to format some cells by changing the cell color and making them bold. In this case, you can create the PivotTable, convert it to worksheet formulas, rearrange cells to a design layout you desire, delete the cells that are not necessary (cost information for Accessories division) and format the cells (bold, color, conditional formatting, etc.) to suit your preference.
Insert rows and columns
Example: For the sales report, you want to insert a row to display a subtotal for two products. You also want to add three columns to show the Budget data, Budget Variance and Year Over Year Percentage change in sales for each product and product group.
Use multiple data sources
Example: You want to include the budget data stored in Excel spreadsheets or a relational database on your sales report for variance analysis that shows the difference between budgeted and actual sales.
Use cell references to vary user input
Example: When the user changes a cell value from “MTD” to “YTD”, you want to refresh the report with the Year to Date information and vice versa. You can reference the input cell in your worksheet formulas to achieve this result.
Create a nonuniform row or column layout (also called asymmetric reporting)
Example: You need to create a report that contains a 2008 column called Actual Sales, a 2009 column called Forecast, but you don’t want any other columns. You can create a report that contains just those columns unlike a PivotTable report, which requires symmetric reporting.
Create your own Cube formulas and MDX expressions
As mentioned earlier, you can create a completely new report from scratch by using the combination of cube functions and MDX expressions. For example, you may enter a cube formula to retrieve sales amount for the Western region for the salesperson Smith for 2007 Q4 by product or product category. You may also enhance your converted PivotTable report by entering new formulas using Cube functions.
Steps to create a free form report
Next, let us see how all this works using an example. In this example, we will create the following custom report.
Note: Sorry about the missing screenshot. This is an old post, and I am unable to find this image. However, I have posted an update here with a similar report.
Follow these steps to create the above custom report.
1. Create a PivotTable Report as shown in the screen shot below. I used the Adventure Works Sales cube for the purpose of this example. I recommend making a copy of the workbook before converting to formulas, in case you need to refer to the original PivotTable or reuse it to create another free form report later. You would want to prepare the PivotTable report as close to the layout you want as possible. This will minimize the effort required in making changes to the report after you have converted it to formulas. You can use filtering, sorting and layout features of the PivotTable to achieve this.
2. Click PivotTable. On the Options tab, in the Tools group, click OLAP tools, and then click Convert to Formulas. If you want to convert the entire PivotTable to formulas, select Convert Report Filters checkbox. Clear this checkbox to convert only the PivotTable row labels, column labels, and values area, but keep the Report Filters. In this example, we do not have the Report Filters for our report. Therefore, clear the checkbox and click Convert.You will notice that Excel 2007 has converted the PivotTable cells to use the cube functions CUBEMEMBER and CUBEVALUE. I will discuss these and the other CUBE functions in the following section.
For cells that are converted, cell formatting is preserved, but PivotTable styles are removed because these styles are applicable to PivotTables only.
3. Delete unwanted columns and rows. In our case, we will delete the following columns: 2001, 2002, Grand Total.
4. Switch 2003 and 2004 columns as desired for our custom report.
5. Insert the following 3 columns: Budget, Variance %, YOY Change %. The Budget column was populated by copying and pasting values from a separate Budget spreadsheet. Variance % column is calculated as: (Actual – Budget) / Budget. YOY Change % column is calculated as: (Current Year Actual – Previous Year Actual) / Previous Year Actual.
6. Format the report and cells as desired.
That’s all! We were able to create a custom report with a few simple steps.
Explanation of CUBE functions
In this section, I will briefly explain the CUBE functions available in Excel 2007 using examples.
The CUBEMEMBER function returns a member or tuple from the cube. A tuple uniquely identifies a cell, based on a combination of attribute members that consist of an attribute from one or more attribute hierarchies in the cube. For example, a tuple may represent members from two dimensions (Geography and Product) or even one or more dimensions and measure. Here are a couple of examples showing the use of CUBEMEMBER function.
A1=CUBEMEMBER("Adventure Works","[Ship Date].[Calendar].[Calendar Year].&[2002]") retrieves the cube member representing calendar year 2002 of Ship Date dimension.
A2=CUBEMEMBER("Adventure Works","[Measures].[Reseller Sales Amount]") retrieves the cube member representing Reseller Sales Amount measure of the cube.
The above two cells (A1 and A2) can be used as references to retrieve the Reseller Sales Amount for the calendar year 2002 as shown below.
The CUBEVALUE function returns an aggregated value from the cube for a member or tuple from the cube. For example, the following function retrieves a tuple representing Reseller Sales Amount for calendar year 2002.
A3=CUBEVALUE("Adventure Works",A1,A2)
A member is a value of a dimension attribute (such as customer names, product names, month names, etc.).
A tuple uniquely identifies a cell, based on a combination of attribute members that consist of an attribute from every attribute hierarchy in the cube. When defining a tuple in an MDX query or expression, you do not need to explicitly include the attribute member from every attribute hierarchy. If a member from an attribute hierarchy is not explicitly included in a query or an expression, the default member for that attribute hierarchy is the attribute member implicitly included in the tuple. Unless otherwise explicitly defined in a cube, the default member for every attribute hierarchy is the (All) member, if an (All) member exists. If an (All) member does not exist within an attribute hierarchy, the default member is a member of the attribute hierarchy's top level. The default measure is the first measure specified in the cube, unless a default measure is explicitly defined. For more information, see Defining a Default Member and DefaultMember (MDX).
The following are a few more Cube functions available in Excel 2007.
CUBEKPIMEMBER
Returns a key performance indicator (KPI) property and displays the KPI name in the cell. A KPI is a quantifiable measurement, such as monthly gross profit or quarterly employee turnover, that is used to monitor an organization's performance.
CUBEMEMBERPROPERTY
Returns the value of a member property from the cube. Use to validate that a member name exists within the cube and to return the specified property for this member.
CUBERANKEDMEMBER
Returns the nth, or ranked, member in a set. Use to return one or more elements in a set, such as the top sales performer or the top 10 students.
CUBESET
Defines a calculated set of members or tuples by sending a set expression to the cube on the server, which creates the set, and then returns that set to Microsoft Office Excel.
CUBESETCOUNT
Returns the number of items in a set.
Conclusion
In this article, I have described how you can create free form reports in any desirable format using Excel 2007 and Cube functions.
Related:
Creating Free Form Reports using Cube functions in Excel - Follow Up
