Current Position:Home > Best approach for batch mode generation of large report via SSRS

Best approach for batch mode generation of large report via SSRS

Update:10-11Source: network consolidation
Advertisement
I have revised this question now that I've determined that we *will* be using the Enterprise edition of SQL Server 2012.
I have a report (that I will deploy to an SSRS 2012 server) which can vary greatly in size. When the report is expected to be small (which we can determine ahead of time by means that are not relevant to this post), it will be rendered by a .NET 4.5 web
application using a ReportViewer control (in remote mode).
When the report will be large, we want the web app to "trigger" the report server to generate the report and deposit it as a PDF file or Excel file in a folder on a file share, and then send an e-mail notification to a configured user. Because
the reports can be very large, I would prefer not to stream them to the web app and from there to the file share. We have experienced browser timeouts in the past, and so I would prefer to have the SSRS server deposit the generated report directly to the file
share.
About 8 parameters have to be passed to the stored procedure which serves as the report’s data source. 
The database where the stored procedure resides will soon be upgraded to SQL Server 2012.
I am fairly new to reporting services and wanted to ask what approach would be best suited for accomplishing the above-stated goals.
From what I've read so far, I know that one possible approach would be to use data-driven subscriptions but the question then becomes:  after inserting report parameters into the subscription table, how does my web app trigger report generation?
Also, I want to deposit the report in a file share and send an e-mail notification to the user. Do I have to create two subscriptions? There is a configuration option when setting up e-mail notification to embed a hyperlink in the e-mail rather than actually
include the report in the message. If I select this option, do I give up the ability to specify the destination folder?

The Best Answer

Advertisement
@JoeSchmoe115:  Thanks for your suggestion.  Using a web service or WCF service would still mean dealing with potential timeouts for very large reports (granted you can adjust those timeouts).    
I think an approach using a console app could work, but rather than having the web app spawn it, an alternative would be to do the following:  (1) have the web app insert report generation requests (including all necessary report parameters) into
a database table, and (2) have a console app, periodically invoked by Windows Task Scheduler, which reads that database table and then uses the SSRS API as you describe to generate the report, save the byte stream to a file, and finally send
the e-mail notification to the user.  
mkedwards
What you've described will work but saving report generation requests in separate table may not be necessary. I don't have much experience with web services but can they just spawn C# console app with needed parameters and exit without waiting for console
app to complete?
The positive side of using database to store report generation requests is that you would have complete log of all requests. Your console app can then update database with completion status.
  • Best approach for batch mode generation of large report via SSRS Update:10-11

    I have revised this question now that I've determined that we *will* be using the Enterprise edition of SQL Server 2012. I have a report (that I will deploy to an SSRS 2012 server) which can vary greatly in size. When the report is expected to be sma

  • Batch PDF Generation Update:10-11

    Hello, Currently in the process of migrating to Oracle 10g with HTML DB 1.6. Looking for possible solutions for batch generation of PDF invoices with line item detail, graphs and tabular data display. Batch sizes average around 10,000 invoices so spe

  • Process Order Batch number generation at the time of order creation Update:10-11

    Dear All, I m facing some issue at the time process order creation: Current Settings: 1. Batch Number Creation at the time of order create. Reason for this setting: Release of order is done  by releasing operations one by one, therfore batch number w

  • Automatic Batch Number Generation in tcode MIGO... Update:11-30

    Hi Experts,                  There is automatic Batch Number is generated  after clicking on Pushbutton  "Check" in transaction code MIGO. Instead of that user want Batch Number Generation after clicking on Pushbutton "Post" . Can anyb

  • Data Quality Manager component Crashing during Batch Key Generation? Update:11-30

    Hi All, I have setup batch key generation for "FINS Members" buscomp and when the job is executed the task crashes always. I have observed the crash is not consistent on a particular data. Most of the times we get FDR file generated and sometime

  • Very large report Update:11-30

    Post Author: ny3ranger CA Forum: Crystal Reports Can anyone tell me if the crystal report xi developer supports the generation of very large reports. Like a report that is 30 inches wide lets say. What we are trying to to do is either 1. Generate ver

  • Crystal Reports 10 Enterprise Server Issue - Exporting Large Reports to PDF Update:10-11

    Weu2019re implementing a Crystal Reports 10 Server for a customer but weu2019re having an issue with exporting large reports to PDF.  The report size that starts to show this issue is roughly 300 pages with 20 records per page.  We use the default Cr

  • Generation of access report CNRCRMPRCUST999 failed. Update:10-11

    Dear Gurus,          I’ve createe one pricing condition table in Dev environment and now I’m trying to transport it in Quality environment. This table is for CRM only. In development every thing working fine but while transporting in Quality I got th

  • Apex+Cocoon PDF Printing issue  on printing Large Reports Update:10-11

    Hello All, I m in a real hard situation with PDF printing in apex.. The application manages to push the report into PDF with lesser record reports but for Large reports, it says File corrupted when i open the downloaded PDF file.. I use Apex+Coocoon

  • Large Report in XML Format issue Update:10-11

    I am running an Oracle report with an output to XML but I am not getting all the the data. Is there a limit on how much data can be shown in an XML output? Is there a way to get large reports to show in XML Format? We are running Oracle ERS 12 on dat