Almost a year ago I wrote a small blog post to ask everyone to vote on a certain Connect item related to the linking of two datasets in a Reporting Services report. At this moment there have been 87 positive votes on the request.
Now that SQL Server 2008 R2 has been RTM’ed, it’s time to demonstrate new functionality in SSRS that allows the retrieval of data from another dataset, a dataset not linked to your data region. It’s not completely the same as actually joining two datasets, but it’s better than nothing – and it works!
In this article I’ll show you how exactly this works by using the new Lookup, LookupSet and MultiLookup SSRS functions.
I’m using the AdventureWorks 2008R2 relational database and the AdventureWorksDW 2008R2 data warehouse, available from CodePlex. The main data is coming from the data warehouse while all the lookups are done on the relational database.
