Solved

Unable to export SSRS report to excel with Excel formulas.

Posted on 2016-10-04
5
44 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 65

Accepted Solution

by:
Jim Horn earned 334 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 65

Assisted Solution

by:Jim Horn
Jim Horn earned 334 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 92

Assisted Solution

by:Patrick Matthews
Patrick Matthews earned 166 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.

756 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