Excel formulas are not being generated by Reporting Services

Question

I'm working on a report for my employer where the end product is a SSRS report, but one that will almost ALWAYS be exported to Excel for further manipulation. In this report, there is a row that is really just a row of calculations based on other cell values.

I've read that Reporting Services can "figure out" Excel formulas, and will generate and insert them where it can so long as your expressions for the cell's value do not contain database field references, only report item references.

Well, I've done this and I'm still not able to get the formulas to come through. I've tried rendering the report every way I could think of, and I've tried modifying the DeviceInfo parameters to include the OmitFormulas option set to False, and still I've come up empty. I've even tried creating a VERY simply table based report with hard-coded values, and even then I don't get formulas.

Here's a sample expression from my report:

=(ReportItems!Balance2.Value + ReportItems!Supply3.Value) - ReportItems!Demand3.Value

My environment is VS 2008 (fully patched/updated) running on SSRS 2008 (not fully patched). I've talked to the server admin, and he says it's running CU1 (I think, can't remember for sure). Our company's Excel is version 2007.

I'm really at my wits end here, so I'm hoping someone out there has dealt with this before and can give me an answer. Thanks!

score 1 · Answer 1 · answered Dec 12 '12 at 19:21

1

I found a way to do it IF you know the cell addresses, though it's a bit of a hack. In your report, enter the cell formula as a string preceded by an apostrophe, the "this is text" escape character in Excel:

="'=A2+A3"

When you export into Excel the apostrophe will be invisible, but the formula appears as text:

=A2+A3

Now if you do a find/replace and remove the apostrophes (replace with nothing), the formulas will calculate correctly.

answered Dec 12 '12 at 19:21

Russell Fox

5,273
1
24
28

How can I make the row number auto increment? – BobNoobGuy Jul 24 '15 at 17:50
In your query you can use ROW_NUMBER() OVER(ORDER BY [ID]). – Russell Fox Jul 28 '15 at 18:37

score 1 · Answer 2 · answered Jul 20 '11 at 07:19

I have also tried this, the problem here is that SSRS 2008 simply does not support this functionality. It is true that in earlier versions of SSRS it was somewhat supported (2005), however they took that out in the later versions... too bad..

see link: http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/ddf2f19d-3f70-4bb0-a035-00709ffee2f9

score -1 · Answer 3 · answered Dec 12 '12 at 20:40

SSRS has quite a few limitations when it comes to Excel content. As you mentioned Excel Formulas or even charts for example are rendered as images instead of Excel Charts.

Seeing as you mentioned that you want a report that works in SSRS, but will always be exported to Excel you should really checkout OfficeWriter at http://www.officewriter.com/.

OfficeWriter can integrate with SSRS and will allow you to export a full fidelity Excel document. By that I mean you can have Excel Formulas, not the rendered SSRS formulas, or real Excel charts not the rendered image. These types of things won’t change if your data changes after the file have been rendered and opened by SSRS.

If the “further manipulation” happens by end users then this is great, they can change numbers and the formulas will recalculate and the charts will update.

If the “further manipulation” is programmatic, then you can use OfficeWriters API to continue to manipulate the document.

You may want to give it a look, I hate for you to be on your wits end.

DISCLAIMER: I am one of the developers on OfficeWriter.

Excel formulas are not being generated by Reporting Services

3 Answers3