Exporting Excel Formulas

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

FindReplace
Find and Replace unique character
FindReplaceResult
Find and Replace Result

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.

ExpressionBuilder
Building a formula text expression

 

 

Advertisements

1 thought on “Exporting Excel Formulas”

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s