reporting services - ssrs report export to excel (formulas)

Question

I have an ssrs report that sums a column for a total, like so:

=Sum(CDEC(Fields!Month01Balance.Value))

Now, when exporting to excel this does not export as a formula. I was told there is a way to do it using the ReportFields collection, however I cannot get that to work. Does anyone know of a way I can get a total column to export a formula to excel? I don't know how many rows there will be so I cannot manually reference each ReportField text box.

Thanks for any pointers here!

Answer 1

You must replace the VALUE of the cell with text representing the formula, but the formula must be preceded by a single apostrophe: e.g.,

A | B | C

2 | 4 | 6

Becomes:

A | B | C

2 | 4 | '=A1+B1

When you export the report to Excel, replace all the single apostrophes with [nothing]. The formulas should now run.

Suggestion: Create a new boolean report parameter called "Show_Formulas." Then, write an expression for field C: =IIF(Parameters!ShowFormulas.Value,"'=A" & RowNumber("myQuery") & "+B" & RowNumber("myQuery"),Fields!C.Value)

This way, the enduser can either see the values in the report, or re-run the report showing the formulas for export.