SQL Clone
SQLServerCentral is supported by Redgate
Log in  ::  Register  ::  Not logged in

SSRS Report aggregate and collate pages by items

SSRS Report aggregate and collate pages by items

SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)SSCrazy (2.3K reputation)

Group: General Forum Members
Points: 2307 Visits: 716
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 SSRSSadmy 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 Hehe
I want a personal webpage Cool
I want to win the lotto :-D
I want a gf like Tiffa w00t Oh wait I'm married!:-D
Report1.txt (14 views, 13.00 KB)
Report2.txt (15 views, 13.00 KB)
Report_Aggregate.txt (12 views, 3.00 KB)
Report_Collate.txt (10 views, 3.00 KB)


You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum