Convert to Formulas

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). 

Screen Capture 1- Pivot Table Report
Screen Capture 1- Pivot Table Report

To this report, I would like to add two simple new calculations that do not exist in the cube. 

  1. Freight Cost per Order = Freight Cost/Order Count
  2. 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”. 

Screen Capture 2 - Convert to Formula
Screen Capture 2 - 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. 

Screen Capture 3 - Do not Convert Report Filters
Screen Capture 3 - Do not Convert Report Filters

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. 

Screen Capture 4 - Unpivoted Cube Report
Screen Capture 4 - Unpivoted Cube Report

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. 

Benny Austin

8 thoughts on “Convert to Formulas

  1. So, after performing this convert and adding calculations, what happens when the SSAS cube is updated?

    Do all the calculations hold and will the data be up to date?

  2. Hi, Office 2007 and SSAS 2008 R2. I think your solutions might work but I am not in the IT departement and so I don’t have the right to do that.
    Anyway, thank you for the advise

  3. Hi,
    I’m trying to sell the full SSIS, SSAS and sql server to my company. I have my cubes nice and everything but I try to build some prototype dashboards with Excel, just to show what is possible. Unfortunatly when I convert into formula, the check box doesn’t appear.
    Yesterday, it was working and I saved one converted to formula file, with report filters (which were perfectly fine). Unfortunatly, this morning, excel told me that my files are corrupted and after it repairs the files, the report filters are gone…
    Any idea of where this problem comes from?

    1. hi Paul, which version of excel are you using ? Try to repair office and try again. If that doesn’t work install the latest SP for office – just in case. I was unable to simulate the error in Excel 2010 following the same sequence as you did. Good luck.

  4. Hey Benny,

    Can you confirm if the “Value Field Settings” option when using Pivot table for Cube (SSAS) is disabled by Microsoft? Is there a way around / may be Excel Macro to enable this?

    Thanks,
    Ketan

    1. hi Ketan, I’am using Excel 2010 and can confirm that “Value Field Settings” is enabled for Pivot Table from SSAS cube. I noticed few interesting things though. While in “Value Field Settings” the option under “Summarize Values by” tab is disabled and I believe that is a correct beahviour beacuse aggregation is defined by the Aggregation Function for the measure at design time. However the “Show Value As” tab is enabled which help you display the result in a lot of different and useful ways like % of Grand Total. I found this tip from online help very helpful – “You can use this feature to try different calculations in a value field. However, because you can add the same value fields to a PivotTable more than once, you can also use this feature to show the actual value and other calculations, such as a running total calculation, side by side.”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s