Solved

SQL Report builder direct to Excel

Posted on 2013-06-10
3
1,472 Views
Last Modified: 2013-07-18
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
Comment
Question by:SimonJohnG
  • 2
3 Comments
 
LVL 37

Expert Comment

by:ValentinoV
ID: 39240138
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
 

Author Comment

by:SimonJohnG
ID: 39240326
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
 
LVL 37

Accepted Solution

by:
ValentinoV earned 500 total points
ID: 39240352
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

708 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

13 Experts available now in Live!

Get 1:1 Help Now