Generating 1000's of report from SSRS in batch format

Question

Generating 1000's of report from SSRS in batch format

kdorsett

SSC Enthusiast

Points: 134
More actions
October 11, 2012 at 1:47 pm

#393368

I have been searching the web, with no clear direction on this issue. The company I work for had me create a report generation application for the users. When a report is selected and the required parameters are chosen, the user can click generate and save and dump the reports in PDF format to the local drive.
Now the problem. We started with generating 25 - 30 reports, but the system has increased to over 2000 reports that can be generated at one time. Now when the user clicks generate, it can take 8 - 10 hrs to create the reports.
Is there anyway this process can be sped up? The number of reports are quickly increasing and the users are becoming restless with the time it takes to create the reports. Is there some batch process or something that I can create to fix this issue? Please someone help...

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323546 More actions · Answer 1

have you looked into SSRS Data Driven Subscriptions, I'm pretty sure that's exactly what you are after, so that there is no user interaction required to create teh reports...so you can start the job at midnight, and have them ready 8-10 hours later, or maybe less.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

kdorsett SSC Enthusiast Points: 134 More actions · Answer 2

I just started reviewing Data Driven Subscriptions, but that still seems to be a rather slow process. The users of our system will usually start generating the reports during the day and need them quicker that 8 - 10 hrs later. With our report user growth, in the coming months, the report creation with increase to 4k - 5k in number. That will take more than 12hrs at this rate and that is not an acceptable practice. Submitting a request today for a completion the next day will cause me my job.

ktmanasi3 Newbie Points: 3 More actions · Answer 3

kdorsett - Thursday, October 11, 2012 1:47 PM
I have been searching the web, with no clear direction on this issue. The company I work for had me create a report generation application for the users. When a report is selected and the required parameters are chosen, the user can click generate and save and dump the reports in PDF format to the local drive.Now the problem. We started with generating 25 - 30 reports, but the system has increased to over 2000 reports that can be generated at one time. Now when the user clicks generate, it can take 8 - 10 hrs to create the reports.Is there anyway this process can be sped up? The number of reports are quickly increasing and the users are becoming restless with the time it takes to create the reports. Is there some batch process or something that I can create to fix this issue? Please someone help...

I have a similar situation. I have to generate report in a batch. Let me know how was the approach for it.

sgmunson SSC Guru Points: 110651 More actions · Answer 4

kdorsett - Thursday, October 11, 2012 2:12 PM
I just started reviewing Data Driven Subscriptions, but that still seems to be a rather slow process. The users of our system will usually start generating the reports during the day and need them quicker that 8 - 10 hrs later. With our report user growth, in the coming months, the report creation with increase to 4k - 5k in number. That will take more than 12hrs at this rate and that is not an acceptable practice. Submitting a request today for a completion the next day will cause me my job.

This is rather a late response, but if you spread out those subscriptions across the entire window of time between users leaving for the day, and then returning the next morning, the report they need is likely going to be sitting in their e-mail inbox every morning before they arrive to work. The other thing you should be doing is tuning the report queries, including adding good indexes where needed, so that the total load from the reports isn't a problem. And one can also consider creating a separate server for reporting, so that it can have ALL the indexes it needs without clogging up production with that stuff. Set up a good ETL process to keep that data current and you could be in much better shape.

Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)