Sunday, December 25, 2011

Generating PDF pack reports in SSRS

One of my recent project requirements has prompted me to write this blog. The project requires generating various reports for client using SQL Reporting services tool in PDF format. We created the required reports in PDF format using SSRS and added subscriptions to deliver them to client list through email. Now came the tricky portion as we thought. They wanted to merge all the reports into a single PDF with each report coming in different page so that they get a single mail rather than a group of mails. Thought it felt like its a tricky one, it was not as tricky as we thought. The solution is rather simple. 
We created a new blank report and added sets of rectangle containers inside it. We then placed subreport items inside the rectangle containers and set add pagebreak after property to true for the rectangles and thats it. Upon rendering, you'll get all the reports combined in a single report template. This template can be deployed to server and then rendered in PDF format to get all the reports in the same PDF. Thus it enables you to create PDF pack report including all your required reports.
Two things to note here is if any of included reports are parameter driven we need to make sure we add those parameters to master report template and pass them onto the relevant subreports.Also we need to make sure components reports (subreports) doesn't take too much time to load to avoid timeout issues. This can be done by fine tuning the query or procedure that runs behind and fetching only the required data.
You can even extend the report to contain charts and images to generate dashboard type of reports