How to apply parent group for multiple datasets in SSRS VS2008

Question

I have been battling this issue for days without success. I have a very tricky format of a report i need to achieve but the main thing is that all the datasets will need to be grouped by 1 parent. I'll attempt to explain...

Say we have dataset1, dataset2. Both have AccountNumber as common field(parent).

I need both datasets to be used in the format/layout of the report but grouped together by AccountNumber, something like this.

[Report Header Data]

[AccountNumber Group]

Dataset1
Dataset2

[end AccountNumber Group]

What is the best way to achieve this? The format of the report has been a major road block on grouping thus making me split the data into multiple datasets, group all them together by accountnumber and then create a custom format per dataset in the report. The flow of the report may be something like this

[Report Header Data]

[AccountNumber Group]
[tablix1]
Dataset1
[tablix1]

[tablix2]
Dataset2
[tablix2]

[end AccountNumber Group]

Looking forward to the discussion on this!

Answer 1

There are multiple ways to achieve this effect, and the best for your situation depends on the details of your report. So I'll just give some of the techniques I've used in the past:

1. Join the two datasets into one

   Joining the datasets into one in your query is one of the simplest answers, and works across all versions of SSRS. It can make the SQL queries large, but it makes report layout simple.

2. Use the Lookup(...) function

   SSRS 2008R2 added the Lookup(...) function, which can be used to access items in a second dataset. It's a little bit awkward to use, and requires a separate formula for every field to be accessed, but it is very powerful for retrieving a few fields from a different dataset.

3. Sub reports

   Similar to the approach descibed in the original question, This lets you create a parent project with one tablix, and then place a subreport within. The subreport will be called multiple times, with the Grouping item as a parameter. Each run of the report should only return the report for that instance of the group. This can be very powerful, but maintenance is difficult: you have two places to change some thingss, and it can require manual tweaking to make sure columns line up correctly. The subreport will often be the fastest report to run, since it is getting called many times.

[NB: StackOverflow.com isn't the best place for discussions. The design of the site is set up to avoid discussion and aim towards question & answers, not discussion.]