none
Excel Formulas in SSRS 2008

    Question

  • 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

    Tuesday, July 20, 2010 5:50 PM

Answers

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! :)
    Wednesday, July 21, 2010 4:48 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!<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 .

     

    Thanks .


    Rajkumar Yelugu
    Wednesday, July 21, 2010 6:13 AM
  • Hi Rajkumar Yelugu,

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


    Regards, Gayathri devi P.S. : Please click the 'Mark as Answer' button if a post solves your problem! :)
    Wednesday, July 21, 2010 6:38 AM
  • 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
    Wednesday, July 21, 2010 6:49 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 .

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

    Thanks


    Rajkumar Yelugu
    Wednesday, July 21, 2010 6:54 AM
  • 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! :)
    Wednesday, July 21, 2010 6:56 AM
  • 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 .

    Thanks .


    Rajkumar Yelugu
    Wednesday, July 21, 2010 7:16 AM
  • 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! :)
    Wednesday, July 21, 2010 8:17 AM
  • 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
    Wednesday, July 21, 2010 8: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

    Wednesday, July 21, 2010 10:48 AM