Click here to monitor SSC
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in
Home       Members    Calendar    Who's On

Add to briefcase

SSRS Report aggregate and collate pages by items Expand / Collapse
Posted Thursday, December 27, 2012 11:31 AM

Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Friday, December 2, 2016 3:27 PM
Points: 312, Visits: 705
Here's the scenario:
Old system has several documents which are generated in Word and can be viewed individually.
If user selects multiple documents then each document is combined and generated as a single Word document. (doc1_item1, doc1_item2, doc2_item1, doc2_item2)
If they select multiple documents and selects the collate option each document is combined into a single Word document but they're collated by the items (i.e. doc1_item1, doc2_item1, doc1_item2, doc2_item2 etc)

In SSRSmy solution)
I've created the SSRS equivalent for each document.
I've created a generic container report that houses each SSRS report as a subreport passing the same parameters to the underlying report, hence generating a single document with all the report aggregated. (d1_i1, d1_i2, d2_i1,d2_i2)
Then I have another generic container report that has the aggregated report as a subreport for each item(generated dynamically in application for total number of items--i.e. 5 items = 5 subreport)

My question now is:
The aggregate runs fine but the collate takes about 50 minutes for 672 items since it's going to query the database 672 times for each item. Is there a better way somehow????

I'm attaching sample RDLs(renamed txt) (2 Report representing individual different reports, 1 Aggregated container report, 1 Collated container report). These are only samples and simplified but get what I want.
Only Report1 and Report2 have embedded datasets pointing to a localhost\sqlexpress tempdb with a generic CTE that generates some data.

This setup works in getting what I want but the performance is severely lacking. It's better then the original Word version since that too about an hour for 100 items and have failed to complete the 672 test items but I'd like to know if there's an easier way. Faster way. Thanks.

SSRS 2012, SSRS 2008 R2

Mike Hahn - MCSomething someday
Right way to ask for help!!
I post so I can see my avatar
I want a personal webpage
I want to win the lotto
I want a gf like Tiffa Oh wait I'm married!

  Post Attachments 
Report1.txt (2 views, 13.40 KB)
Report2.txt (1 view, 13.41 KB)
Report_Aggregate.txt (1 view, 3.26 KB)
Report_Collate.txt (0 views, 3.40 KB)
Post #1400716
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse