SSRS Report aggregate and collate pages by items

• Mike.Hahn

  SSCarpal Tunnel

  Points: 4053

  More actions

  December 27, 2012 at 11:31 am

  #393847

  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 SSRS:(my 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.

  P.S.

  SSRS 2012, SSRS 2008 R2

  ---------------------------------------------------------------
  Mike Hahn - MCSomething someday:-)
  Right way to ask for help!!
  http://www.sqlservercentral.com/articles/Best+Practices/61537/
  I post so I can see my avatar :hehe:
  I want a personal webpage 😎
  I want to win the lotto 😀
  I want a gf like Tiffa :w00t: Oh wait I'm married!:-D