Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Unable to export SSRS report to excel with Excel formulas.

Posted on 2016-10-04
5
Medium Priority
?
74 Views
Last Modified: 2016-11-11
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.
0
Comment
Question by:CBRE_GSC2_122001
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 66

Accepted Solution

by:
Jim Horn earned 1336 total points
ID: 41827912
>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
 

Author Comment

by:CBRE_GSC2_122001
ID: 41829301
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
 
LVL 66

Assisted Solution

by:Jim Horn
Jim Horn earned 1336 total points
ID: 41830212
>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
 
LVL 93

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 664 total points
ID: 41830237
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
 
LVL 14

Expert Comment

by:frankhelk
ID: 41883535
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question