Solved

Unable to export SSRS report to excel with Excel formulas.

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

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

A recent question popped up and the discussion heated up regarding updating a COMMENTS (TXT) field in a table using SSRS. http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/MS-SQL_Reporting/Q_27475269.html?cid=1572#a37227028 (htt…
This code takes an Excel list of URL’s and adds a header titled “URL List”. It then searches through all URL’s in column “A”, looking for duplicates. When a duplicate is found, it is moved to the top of the list. The duplicate URL’s are then highlig…
This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

707 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now