Answered by:
Excel Formulas in SSRS 2008

Hello,
I need to have a report that will have formulas in excel. I understand that the limited support for exporting excel formulas has been dropped in SSRS 2008. Is there any way to work around this and still output formulas?
obvioulsy the way to do it in previous versions of SSRS are not working (using report items). I tried to add my formulas in my dataset (I can know which excel cells I need in the formulas) but they end up being displayed as text. I then have to get in the excel file and enter and exit each cell so that it "becomes" a formula.
thanks a lot!
Simon
Question
Answers

Hi Simon,
Yes, since SSRS 2008, RDL expressions are not translated to excel formulas, which is documented at http://msdn.microsoft.com/enus/library/ms143380.aspx by Microsoft. If you have any concern about this, please submit a feedback at http://connect.microsoft.com/SQLServer/Feedback and hope it can be improved in the next release. However, if you have to do it, currently, you must use SSRS 2005 or earlier with report items.
thanks for your understanding,
Jerry
 Marked as answer by Simon Ringuet Wednesday, July 21, 2010 10:46 AM
All replies

Hi,
You can do this by using ReportItems!Textbox1.value.
Eg :
Column1 Column2 Column 3
5 10 =5+10
Here text box 1 .value = 5
textbox2.value = 10
textbox3.value = text box1 .value + textbox2.value
Instead of referring Fields in Formula, Use Text box values...
=ReportItems!Textbox1.value + ReportItems!Textbox2.value
so when you export the Report, in Excel Formulas will be there..
Regards, Gayathri devi P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :) Proposed as answer by Gayathridevi.msit Wednesday, July 21, 2010 6:38 AM

Column1 Column2 Column 3
5 10 =5+10
Here text box 1 .value = 5
textbox2.value = 10
textbox3.value = text box1 .value + textbox2.value
Instead of referring Fields in Formula, Use Text box values...
=ReportItems!Textbox1.value + ReportItems!Textbox2.value
HI Gayathri ,
How about this one
col1 co12 col1+col2
1 2 3
4 5 9
2 2 4
Assuming the col1+col2 columns cell expression to be Reportitems!<col1cells textboxName>.Value +ReportItems!<col2cells textboxName>
Now my page header textbox consisting an expression as =sum(ReportItems!<(col1+col2)cells textboxName>.Value
Now do you think changing col1 value reflects both col1+col2 value and Header value ?
I think it might reflect col1+col2 column value but not inturn reflects the headervalue .
Thanks .
Rajkumar Yelugu 

Hi Gayathri ,
col1 col2 col3
Field1 Field2 code.CustomFunction(Field1,Field2)
Assuming col3 cells expression depending on filed1 and field2 using some VB custom code [ Situation where reportiems collection cannot be refered ] type of calculation , Do you think the resultant report when exported to Excel persits the code logic as formulae for Excel ?
Rajkumar Yelugu Edited by Rajkumar Yelugu Wednesday, July 21, 2010 7:20 AM

Hello,
I need to have a report that will have formulas in excel. I understand that the limited support for exporting excel formulas has been dropped in SSRS 2008. Is there any way to work around this and still output formulas?
obvioulsy the way to do it in previous versions of SSRS are not working (using report items). I tried to add my formulas in my dataset (I can know which excel cells I need in the formulas) but they end up being displayed as text. I then have to get in the excel file and enter and exit each cell so that it "becomes" a formula.
thanks a lot!
Simon
HI Simon ,As far as i Know Preserving Excel Formulae is Not Supported in RS 2005 and RS 2008 .
Thanks
Rajkumar Yelugu 
Hi Rajkumar,
See actually Formula persists in Excel only when you use ReportItems.
If you use Field name or any codes using Fields, when exported to Excel Formula's wont be there..
I guess you are interviewing me :)
Regards, Gayathri devi P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :) 

Hi Rajkumar,
:) you can preserveExcel formula only when you refer items by using REPORTITEMS. Else you cannot.
Regards, Gayathri devi P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :) Proposed as answer by Gayathridevi.msit Wednesday, July 21, 2010 8:18 AM

Hi Simon,
Yes, since SSRS 2008, RDL expressions are not translated to excel formulas, which is documented at http://msdn.microsoft.com/enus/library/ms143380.aspx by Microsoft. If you have any concern about this, please submit a feedback at http://connect.microsoft.com/SQLServer/Feedback and hope it can be improved in the next release. However, if you have to do it, currently, you must use SSRS 2005 or earlier with report items.
thanks for your understanding,
Jerry
 Marked as answer by Simon Ringuet Wednesday, July 21, 2010 10:46 AM

