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

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

:!: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.

## 8 thoughts on “Convert to Formulas”

1. Masson says:

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?

1. Benny Austin says:

Yes the data will be refreshed

2. If you want to move to a fixed layout report or if you need to create calculations and don’t know MDX, this is a good approach. Otherwise, you might look at adding calculations directly to your PivotTable with OLAP PivotTable Extensions:
http://olappivottableextend.codeplex.com

3. Paul says:

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

4. Paul says:

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. Benny Austin says:

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.

5. Ketan says:

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. Benny Austin says:

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