Excel Function within SSRS 2012

Question

From what I have read this may not be possible but thought it was worth asking, I am trying to Export a SSRS 2012 report but maintain an Excel Function at the same time.

I have a Text Box in my SSRS report which when export always appears in Row/Column D33 I have a figure of 20. I have coded in to a Cell on my report =D33* "a figure that is returned in the report, the Expression I have used is ="=DD3*"&Cint(Fields!LABC_Hours.Value), when exported this comes out as =D33*5 but what is should be doing is the calculation =20*5. Is there a way of doing this?

Thanks in advance.

Answer 1

Short answer is no.

As you've probably seen in your research, there was limited support for this in SSRS 2005 but not in subsequent versions.

See Breaking Changes in SSRS 2008.

In earlier versions of Reporting Services, there was limited support for translating expressions in RDL to Microsoft Excel formulas. In this release, when you export a report to Excel, RDL expressions are not translated to Excel formulas.

Needless to say, this is a popular request, see this Connect item:

SSRS 2008 export formulas to Excel.

While refreshing my memory about all of this, I did see a few notes that suggested if you use expressions which directly reference the ReportItems collection like:

=ReportItems!val1.Value + ReportItems!val2.Value

Then this would work, but I ran a simple test now in SSRS 2008 and it made no difference.

I think the general consensus is that you're just out of luck on this one, sadly.

Answer 2

If someone is still interested with this, I developed solution for that, it can be considered ugly but it let me escape many pain in my experience with SSRS.

For those who wants detailed description of ideas (I think its needed anyway because the solution is not so obvious): https://github.com/GrafGenerator/ssrs-formularize.

In short: this is custom rendering extension, which intended to hide ExcelRE from SSRS (Formularizer uses ExcelRE under the hood), and trigger formulas processing when needed. The idea is to mark some report items with "anchors", and then in other report items write textual formulas in special simple DSL that will link to cells with anchors after excel doc is generated.

Important: anchor and formulas added to report item's Action URL, as it seems the only report item field that can be accessed when rendering extension is working without breaking functionality (we can send almost anything here is cook it right way).

So, words report items "has anchor" or "has formula" means that report item action set to URL and expression =code.EncodeFormula("current item identitier (anchor)", "current item formula")

Sample steps are (with table):

1. Did once per SSRS instance: compile binaries and install to SSRS per instructions in repository.
2. Strict: Add custom function to your report: https://github.com/GrafGenerator/ssrs-formularize/blob/master/encode-function.vb
3. Strict: Add custom property to report - key is "formularize", value can be any

4. table row has report items (textboxes) txtA, txtB and txtC.

   4.1 txtA has anchor comp_value

   4.2 txtB has anchor output_value

   4.3 txtC has formula IF({cell 'comp_value' row} > 0, {cell 'output_value' row}, "N/A").

5. Result in excel file is formula IF(A1 > 0, B1, "N/A") for first row, row numbers 2 for second row, 3 for third and so on.

6. The same is for column and all scopes. More examples are on project github page.

I hope this helps someone, feel free to ask question and post issues to the github repo.