January 7, 2015 at 3:46 am
Morning All,
I have created reports and also shared datasets within SSRS. Unfortunately staff still want the opportunity to see the data directly in Excel (exporting the report is Excel is too time consuming apparently :/)
If I have a shared dataset uploaded to Reporting Services, is there any way to connect Excel directly to that shared dataset? They don't have the skills to use the dataset in Report Builder.
Just want to see if it's possible before I go back to them and say they have to continue to export it from Reporting Services.
January 7, 2015 at 10:25 am
I'm not sure if Excel would be aware of the guts of SSRS.
If I had to do something like this, I'd have Excel pointing at the stored procedure that is generating your SSRS report.
In Excel, you go to Data --> From Other Sources --> From Microsoft Query
Then you're free to run a stored procedure or your own SQL to generate and bring down a dataset to Excel.
January 7, 2015 at 11:29 am
TSQL Tryer (1/7/2015)
Morning All,I have created reports and also shared datasets within SSRS. Unfortunately staff still want the opportunity to see the data directly in Excel (exporting the report is Excel is too time consuming apparently :/)
If I have a shared dataset uploaded to Reporting Services, is there any way to connect Excel directly to that shared dataset? They don't have the skills to use the dataset in Report Builder.
Just want to see if it's possible before I go back to them and say they have to continue to export it from Reporting Services.
A compromise might be to have the report in SSRS render directly into Excel; for example if you have a report on server yourServerName in folder ReportPath called ReportName; you'd give your users the following URL:
http://yourServerName/ReportServer/Pages/ReportViewer.aspx?%2fReportPath%2fReportName&rs:Format=Excel&rs:Command=Render
The key is the &rs:Format=EXCEL - When you click on the link, the report will render directly into Excel. Instead of appearing on the screen, IE will ask you for permission to open an Excel file. I use it all the time and it works well.
You can also render directly into PDF, Word, or any of the other formats you can export to.
The main drawback is if you have to pass parameters; they'd need to be passed in the URL. There are ways to work around that - building a web form that calls the report, for example - but that may be more trouble than it's worth for you. I have some reports, for example, that I run from a VB program... the user picks a few parameters and the program calls several reports and dumps the results directly into a network folder in PDF format. But again, this takes development time so you'd have to decide if it's worth it in your case.
Alternatively, you could build subscriptions for the reports and either email them to the users, or drop them in a network folder of your choosing, in whatever format you want.
January 8, 2015 at 4:49 am
I don't want to get in the habit of creating a dataset in SSRS but then create a separate view/s.procedure that I attach to Excel, as that's two places I would need to remember to change this if there were any amendments.
The url works ok actually, just a shame that you can't tell Excel to look at that dataset stored on SSRS.
Nevermind.
Thanks
January 8, 2015 at 2:57 pm
If you have the SSRS report pointing to your stored procedure, then that's only one place you need to make changes.
January 9, 2015 at 7:43 am
How would you get the stored procedure to look at the dataset in SSRS though?
January 9, 2015 at 1:57 pm
You do it the other way around... You have SSRS execute the stored procedure.
For the dataset, you can have it call a stored procedure on your server.
I usually have the command type set to text and for the command enter EXEC sp_proc @Param1, @Param2
It runs the stored procedure and sends the parameters very nicely.
January 13, 2015 at 8:41 am
Another alternative is that Excel can subscribe to a report as a datafeed. In the report manager, when viewing a report, if you click the little orange square that tooltips "Export to Data Feed" it will download a .atomsvc file.
If you then open an Excel workbook and go to Data -> From other sources -> From OData Data Feed you can then find and open the .atomsvc file. This creates a Connection in the workbook and the data can be imported into a table / pivot table.
The plus sides to this is that they can then just use Refresh to rerun / reimport the data and it will also bring through any calculations / aggregations that are done within the report. It also means you can use Report Manager permissions rather then having to grant user access to the database.
The down sides are that you need to format the report like you would for a csv export and also the connection uses the parameter values set when you exported the atom file.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply