Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

How to insert Excel formula to cell in Report Builder 3.0?

There is RDL report template for SQL Server Reporting Services.
I need to set value for cell in table in the report template which must be calculated from other values in the report.
When the report is exported to Excel file I need to see the Excel formula in that cell.

Example:

1) Sample report template with three columns table. First cell must be sum of second and third: step 1

2) I set expression for first column to reproduce excel formula in output report:

step 2

3) When report was exported to Excel file I see raw text of formula in cell: step 3 4) But when I click on value in excel formula input cell converted to right value: step 4

like image 948
asolovyov Avatar asked Mar 15 '23 15:03

asolovyov


2 Answers

Excel Formulas support has ended since SSRS 2008 (see Breaking Changes in SQL Server Reporting Services).

No Formula Support in Excel

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.

If you really need to have the formulas in the excel file and can't use SSRS 2005, you could do the following workaround:

  • Append an extra ' before every formula (ex: ="'=C3+B3") in your report
  • Open the exported excel file
  • Do a find and replace to remove the leading '

But it will not suit every cases...

like image 137
Sébastien Sevrin Avatar answered Mar 20 '23 09:03

Sébastien Sevrin


It's not possible to export Excel formulas from SQL Server Reporting Services. The only thing you can do is use standard SSRS expressions but these will export as values in Excel (and any other export format.)

like image 45
DavidG Avatar answered Mar 20 '23 10:03

DavidG