Joining DataSets in ReportingServices

Question

I have a SSRS report. This report has two data sets from two different data sources. I need to do a conceptual join on these two data sets. For example, assume I have the following datasets A and B.

DataSet A
Data Source is SqlServer P
SELECT user_id, user_name from users

DataSet B
Data Source is SqlServer Q
SELECT change_id, user_id, change_details from changes

Eventually, I just want a SSRS table with user_name and change_details. After a little online research, I've found three options:

1. Join the queries in the dataset using server linking from SQL Server.
2. Join the queries in the dataset using openrowset() from SQL Server.
3. Use an external assembly to make a web service call and cut out the 'users' datasource.
4. Conceptually 'join' the datasets using a report/sub-report paradigm in which the report's grid has a sub-report that just takes the user_id for a given row and populates a single text box with the user name.

They all feel like they have some headaches. Of these, I think I prefer option 4 because it forces this 'join'ing logic into the report and leaves the queries simple. In addition, it allows me to skip alot of overhead setting up linked servers whenever a report is deployed to a different environment. Option 1 looks doable as well, and all of the querying would be encapsulated in the dataset. Option 2 doesn't look strong because all of the documentation for openrowset seems to say to use linked servers instead. Option 3 is currently not feasible with all of the required setup to use external assemblies, but I think this would be a viable alternative otherwise. With this said, are there any other reasons for choosing one of these solutions over another?

I'm working in SSRS 2008 if that is relevant.

Answer 1

You can do 1, 2 or 3. Not 4: you simply can't join or merge in SSRS itself.

I'd start with option 1: your logic is good about why I'd choose it. Clean, simple, handled by the database engine.

Answer 2

I ended up going with option 4, which actually is possible. As I said in the question, it is only a 'conceptual' join, as there is no real joining of datasets that occurs. Rather, if I create a main report with a table representing 'change's, then in one of the columns I can reference a sub-report with a data source set as the 'user's. The parameter to the sub-report is 'user_id', which I can grab from the specific row of the table.

Initially I was a little worried that if I had 1000 'change' rows for 10 distinct 'user's, then I'd have 1000 calls to the 'user' table. Not so. I watched the profile for the SQL Server and only saw one call per 'user' returned from the db. Apparently SSRS caches this result and re-uses it.

Answer 3

In 2008 R2 this is easily accomplished using the Lookup()-function, as described here:

http://prologika.com/CS/blogs/blog/archive/2009/08/12/reporting-services-lookup-functions.aspx