Sometimes, a customer will request for an SSRS excel export to contain some formulas rather than literal values. Usually this is when they require a ‘workbook’ rather than a normal report. i.e. they can make changes to detailed values and see what the calculated results are, rather than a read-only high-level information summary.
Unfortunately, SSRS does not really support excel formulas. It doesn’t really recognize them as such. You can export some text that looks like a formula e.g. =A1 + B1 but when it reaches excel it will be treated as text and won’t actually calculate anything. If you then refresh the cell e.g. click in the cell and hit return, the formula will get recognized and you get the result you were looking for. However, it’s an extra manual step.
If you have a lot of formulas in one export, it would be a pain to go through all of them and refresh every cell individually. A way around this is to put a character or character sequence at the beginning of the text string e.g. ^ or MattBarwick that you know won’t normally come up and then do a find (your unique characters) and replace (with nothing) on the sheet or whole workbook. This has the same effect as refreshing all the cells. This can either be a manual step, or you can set up an excel macro or VBA code in a separate workbook that will do the job for you (you can also use this code to apply any formatting needed to the formula cells, as excel won’t pick up your specified formatting from SSRS, as it thinks they are purely text).
The trickiest part of this workaround can be working out what the formula strings will be, since it’s not always obvious where everything will come out in the export and what columns and row references to use. There are several ways you can combat this. The first is to just do an export and see which column letters the columns come out into. Then create a hidden row above your normal report, and enter the column letters. You can reference these report cells in SSRS e.g.
=ReportItems!Textbox39.Value
That way if the field moves to another column letter in the future, there’s only one place you have to update the excel column letter.
For the row number: if you’re always referencing another column in the same row, you can use the excel functions Row() (to get row number) and Indirect(string) to convert string into excel reference. If you’re referencing a different row, you may need to work out what the number will be using an offset from the current row, or doing a calculation based on the row number of the dataset – use RowNumber() in SSRS expressions. Once you have the column and cell references, you can use them to construct a formula string. If you’re doing lots of these, it can be beneficial to set up some custom code that will build your formula string for you – you just pass to it the type of formula you want, the column letter and row number.
SSRS Tips 
One thought on “Exporting Excel Formulas”