Staggering SSRS Reports

When I first got to the School Board as SSRS Supervisor, my first task was to tame the SSRS Transcript Reports.

Reason being, there were transcripts for 9th and 10th grade times 16 high schools.

And there were 11th grade times 16 schools and 12th grade times 16 high schools.

That's a lot of reports.

And the 11th and 12th grade reports took between 3 and 4 hours to run a single report.

So you can imagine the problem, the report would take too long to run and fail.

Causing the next report to fail.

Causing the next report to fail.

Hence a ripple effect.

Meanwhile, people in the Schools were opening tickets left and right raising holy ruckus, which was justified.

And my task was to fix everything, whatever it took.

So I started with the query, the stored procedure, I streamlined it best I could, cut the run time in half.

Then starting investigating on the web for ways to speed up the report.  It turns out the query ran sort of fast, for every student a specific grade at a specific high school.  Except it rendered every student in a single PDF, and the conversion to PDF was this issue.

So I found a few tricks to speed it up, remove the GIFs, the CanGrow property settings and tightened up the report.

And soon it ran in acceptable runtime.

However, there was one more thing that needed to be done.

Stagger the reports.  Instead of running in batches once a day, I'd run them 24 x 7 every day, every other hour for 11th and 12th grade and every hour for 9th and 10th as they ran faster.

Happy guidance counselors, no tickets and my boss was pleased.

Problem solved.