[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1631
  • Last Modified:

SQL Report builder direct to Excel

I would like to generate a report in SQL server reporting services that allows the user to select either a drop down or tick box so they make make the decision to export the report to CSV format prior to running the report and not rendering it to the screen.

Presently they make date selections then run the report then perform the export, my goal is to make these selection then make a choice to either show to screen or direct to excel either by it automaticaly opening excel and placing the data or if this is not possible saving it to a shared area on a server as a CSV file.
0
SimonJohnG
Asked:
SimonJohnG
  • 2
1 Solution
 
ValentinoVBI ConsultantCommented:
You'd of course be able to achieve that using a custom .NET application. But in case that's a bit too far, there may be another option.

The Report Server URL allows you to specify the output format.  To export to CSV, add the following to a URL that would normally open a report:

&rs:Format=CSV

For Excel, replace CSV with EXCEL. See here for a full list: Hide/Show Items Dependant On Export Format (SSRS)

Using that knowledge, you could build a main report that serves the date parameters to the user. This report would then contain a URL as explained earlier. That URL would be located in the Action > Go To URL setting on a textbox.  Put some text in the textbox so that the users know they're clicking a link or a button (use some coloring to make it look like a link or button).

I hope the above is clear, if not let me know...

See also: Export a Report Using URL Access
0
 
SimonJohnGAuthor Commented:
Would your solution bring up the textbox after the report had originally been executed? I had hoped the user would enter the required date then click on the 'text' button and this would generate the CSV without if first appearing on screen. If this is the case the user could just click on the export menu item?

Maybe I read you wrong?
0
 
ValentinoVBI ConsultantCommented:
The parameter part would work as a regular report, so the user selects the dates, activates the "Export to CSV" option (which would be another parameter) and then clicks the View Report button.  But he wouldn't see any data, just the textbox that looks like a button (or link).  When that textbox is clicked, the normal IE Open/Save popup appears.

Just to be clear: my solution needs an additional report next to your existing one.  In the case the user wants the CSV export, the Go To URL action contains the URL that will export the report.  In case he just wants to see the report the Go To URL action would contain the regular link to your report without the additional Format parameter.  That's something you'd need to implement using an expression based on that "Export to CSV" parameter.


If you want to implement this method inside your existing report it would involve hiding everything that's inside your report in the case of "export to CSV" and only showing the "click to export" textbox.

Depending on the complexity of your report, perhaps a subreport is a good idea?  In that case you'd of course have two reports as well but instead of changing the behavior of the "click to export" textbox you'd have some logic in the Hidden property of the subreport.

I hope you're still with me here? It's not really the easiest subject to explain remotely...
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now