Excel is the first choice browser to create reports from an Analysis Service cube. The cube data is displayed as a pivot table format which is extremely convenient and flexible format to slice and dice information. Pivot tables, however can only display data that exist in the data source. There may be instances where you might need additional calculations to be displayed in your report that could be derived from existing cube measures. For e.g. having a different method of determining sales quota for a particular region, calculation of Averages and Variances, what-if analysis and so on. In these circumstances, the Convert to Formula feature of Excel comes really handy.
In this post, I walk-through this feature using Adventure Works cube. For instance I have the following report from Adventure Works (Screen Capture 1).
To this report, I would like to add two simple new calculations that do not exist in the cube.
- Freight Cost per Order = Freight Cost/Order Count
- Freight Cost % of Sales = Freight Cost/Sales Amount
To get going, click on any cell in the pivot table so that the PivotTable Tools ribbon is active. Navigate as shown in Screen Capture 2 and click “Convert to Formula”.
:!:When building a report, use Convert to Formula feature as the final step in report building.
This will prompt a dialog to inform that the change is irreversible (Screen Capture 3). It is recommended not to convert the report filters unless you want to remove the ability to filter data.
Click Convert and this action will unpivot the data into tabular form. Now add the additional columns and specify the excel calculations as required to get the final output as shown in Screen Capture 4.
As you can see, Convert to Formula is really cool feature to create calculated measures that don’t necessarily need to exist in a enterprise-wide cube. Just exercise caution when using this feature, because it is irreversible.