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,

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..

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!<col1-cells textbox-Name>.Value +ReportItems!<col2-cells textbox-Name>

Now my page header textbox consisting an expression as =sum(ReportItems!<(col1+col2)-cells textbox-Name>.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 .

 

Th

Hi Rajkumar Yelugu,

You are Right.. Formulas are reflected only in BODY of the Report and NOT in HEADER of the Report.

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&n

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 .

http://social.msdn.microsoft.com/Forums/en-US/sqlreportingservices/thread/e61bc1d2-a749-4e3b-821f-dcacc7bfaa72

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 :-)

 

I guess you are interviewing me :-)

Hi Gayathri ,

:)

IT was just a keen intrest to know  whether preseving excel formulae is supported in all possible cases .

Th

Hi Rajkumar,

 :) you can preserveExcel formula only when you refer items by using REPORTITEMS. Else you cannot.

 

 

Hi Simon,

Yes, since SSRS 2008, RDL expressions are not translated to excel formulas, which is documented at http://msdn.microsoft.com/en-us/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

Hi Jerry,

thanks, that confirms what I (unfortunately) knew, since using SSRS 2005 or earlier is not an option, I guess I have to submit a feedback and hope it'll come back at some point.

 

thanks again

 

Simon

It does not work this way for me. Any other idea?

Thanks