Solved

SQL Report builder direct to Excel

Posted on 2013-06-10
3
1,531 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
[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
  • 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

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how the fundamental information of how to create a table.

726 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