SSRS - How to render child datasets

Question

I'm designing a SSRS report in Visual Studio for use as a local report (so a SQL Server is not involved).

I have a table with customers/addresses that has the following columns:
AddressID
Firstname
Lastname
Street

Another table keeps orders and looks like this
OrderID
CustomerAddressID
ShopAddressID

So two columns from my order table link to datasets in the address table. I want to display both addresses in my report. The datasource for the report is a xsd dataset.

What's the best way to do this in SSRS? I'm pretty new to SSRS and kind of lost with the dataregions, lists, etc.

Answer 1

You should edit the source for the second dataset to include a parameterized query based on the first one. Something like:

SELECT * FROM other_table 
WHERE CustomerAddressID = @adress 
   OR ShopAddressID = @address

Of course you should create the address parameter as report parameter(pointing to the first dataset)

Hope this makes sense.

Answer 2

Look at subreports.

To be a bit more specific, you need to define multiple data sources in your report that these other regions, tables, or whatever, obtain their data from. You then need callback handlers in your app that can provide that data for each datasource (quoting: "your application must handle the SubreportProcessing event of the LocalReport object.". The article explains this in detail.

Just curious (because I am going to through the same thing right now) - are you really designing SSRS reports (2008), or VS reports (2005)? Because the 2008 ReportViewer control cannot render SSRS 2008 reports ....