Solved

Unable to export SSRS report to excel with Excel formulas.

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

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

Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

Question has a verified solution.

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

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Do you use a spreadsheet like Microsoft's Excel?  Have you ever wanted to link out to a non excel file on your computer or network drive?  This is the way I found to do it!
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

617 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