[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

SQL Report builder direct to Excel

Posted on 2013-06-10
3
Medium Priority
?
1,604 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 2000 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

650 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