ssrs report export to excel (formulas)

2
votes

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!

1
excelreporting-servicesssrs-2008
possible duplicate of Excel Function within SSRS 2012 – Ian Preston
Check out my workaround solution that I created for that here: stackoverflow.com/a/56016094/3262289. – grafgenerator

1 Answers

4
votes

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.