Unable to export SSRS report to excel with Excel formulas.

I want to generate a report through SSRS with excel formulas ,I was not able to do so.

So, I found an alternative where I can export the report with formulas as text.


 
Then in the exported excel sheet. I placed the cursor on each cell and presses F2 this turned the exported text to excel formula.



But this method is not feasible in my case since have lots of cells with excel formula.

Please suggest how can this be achieved.

I am using SQL Server (BIDS) 2008R2.

Thanks, for the help in advance.
CBRE_GSC2_122001Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>I want to generate a report through SSRS with excel formulas ,I was not able to do so.
Correct.  SSRS will export only the data to Excel, not formulas.

>Please suggest how can this be achieved.
One method I've had success with is to create an Excel 'template file' that has the cosmetics and formulas that you want.  Then Copy that file, paste it somewhere, and export the source data set from SQL Server into a named range into the spreadsheet.  Then once the spreadsheet is opened formulas will recalculate.

The article Microsoft Excel & SQL Server:  Self service BI to give users the data they want shows how to set a connection to SQL Server and create a button such that users can simply open the spreadsheet, hit the button, and it will automatlically load with data.

Good luck.
Jim
0
 
CBRE_GSC2_122001Author Commented:
Hi Jim,

Thanks for your help.

I have one more question, before I can convey to my client that this type of report is not possible through SSRS.

Is there any SSRS Version or any Utility/Plug In (free or paid) through which report can be exported with excel formulas?
0
 
Jim HornConnect With a Mentor Microsoft SQL Server Developer, Architect, and AuthorCommented:
>Is there any SSRS Version or any Utility/Plug In (free or paid) through which report can be exported with excel formulas?
None that I'm aware of, but I'm predominantly a SQL Server expert and not an Excel expert, so I'll ping a couple of Excel VBA gods I know and see what they have to say.

You client may be hung up on the 'SSRS' part.  It's also possible to create an SSIS packages that does all of what I described above, then when the client wants their Excel fix to just fire the SSIS package.

That is, if the methodology in the article doesn't work for you.  Like cheetahs with ADHD on Meth, I've had many an executive hooked on this style of reporting.
0
 
Patrick MatthewsConnect With a Mentor Commented:
Jim asked me to take a look at this.

I'm not aware of any plug in that would automatically bring over the formulas.  However, if the formulas are already in the worksheet as text entries, then it seems to me that you should be able to use some VBA to convert those text entries into true formulas.  You could deploy that code either as an Excel add-in, or in the desktop user's Personal.xlsb file (i.e., the "personal macro workbook").
0
 
frankhelkCommented:
No comment has been added to this question in more than 21 days, so it is now classified as abandoned.

I have recommended this question be closed as follows:

Split:
-- Jim Horn (https:#a41827912)
-- Jim Horn (https:#a41830212)
-- Patrick Matthews (https:#a41830237)


If you feel this question should be closed differently, post an objection and the moderators will review all objections and close it as they feel fit. If no one objects, this question will be closed automatically the way described above.

frankhelk
Experts-Exchange Cleanup Volunteer
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.