Converting an Ad-hoc report

There are four types of Formula report to which you can convert Ad-hoc reports: Cell based, query based, Excel Aggregated, and Values. The first three differ in the type of formula by which the report values are calculated. In a Values report, the values are displayed, but not calculated.

In an Ad-hoc report which is converted to a formula report you can use the full range of Excel formulas in addition to Office Plus formulas. Reports remain connected to the cube when you convert them.

To convert an Ad-hoc report:

Click the arrow on the Convert to Formula Report button in the Ad-hoc Report group on the ribbon.

Select one of these options from the Conversion Formula list:

Option	Description
Cell based (CELL.GETC)	Cell based formula reports have a static structure which is defined by a number of CELL.GETC formulas. Each formula requires a separate database query. You can write back data in a cell based report. The writeback operation is handled by the server.
Query based (MDX.EXECUTE)	Query based formula reports have a dynamic structure which is saved in a single MDX.EXECUTE formula. This means that only one query needs to be sent to the database. You cannot write back data in an MDX.EXECUTE formula report. When you convert to a Query based formula report, the MDX.EXECUTE formula is hidden in columns A and B. Unhide the columns to access the formula.
Excel Aggregated	An Excel Aggregated report is similar to a cell based report but uses Excel formulas in place of CELL.GET to calculate aggregated elements wherever possible. That means that aggregated elements are calculated in the spreadsheet instead of being called from the server. Note: Excel SUM formulas require client-side splashing in order to work. Therefore, if client-side splashing is disabled, Excel SUM formulas are overwritten. Conversion to Excel Aggregated reports is not available with CPM Relational Provider database aliases.
Values	Values can be written back through Excel formulas and you can specify to which elements the values are distributed. For example, you could splash a percentage salary increase on an All Staff element but exclude certain staff from the distribution. The writeback operation is handled on the client. A value report displays the values of the Ad-hoc report in an Excel spreadsheet. The values are not calculated by any formula.

Click Convert to Formula Report.
The Formula report is displayed. If the report is query based, the MDX.EXECUTE formula which calculates the report values is hidden in columns A and B. Unhide the columns to display the formula. If the report is cell based, the formula by which each cell is calculated is displayed in the Excel Formula Bar.