Clever Geek Handbook

How to insert an Excel formula into a cell in Report Builder 3.0?

There is an RDL report template for SQL Server Reporting Services.
I need to set a value for a cell in a table in a report template to be calculated from other values ​​in the report.
When the report is exported to Excel file, I need to see the Excel formula in this cell.

Example:

1) An example of a report template with three columns. The first cell should be the second and third: step 1

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

step 2

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

+4


source to share


2 answers


Support for Excel formulas has ended with SSRS 2008 (see Emergency Changes in SQL Server Reporting Services ).

No support for formulas in Excel

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

If you really need to have formulas in an Excel file and cannot use SSRS 2005, you can do the following solution:



  • Add an additional one '

    before each formula (for example ="'=C3+B3"

    ) in your report
  • Open the exported Excel file
  • Search and replace to remove the lead '

But this will not work for all cases ...

+6


source


Unable to export Excel formulas from SQL Server Reporting Services. The only thing you can do is use the standard SSRS expressions, but they will be exported as values ​​in Excel (and any other export format.)



+1


source






More articles:


All Articles