SSRS 2008 R2 Lookup - How to use multiple datasets in a tablix

SSRS 2008 R2 Lookup - How to use multiple datasets in a tablix

1.     Background

The purpose of this article is to provide a way of using multiple datasets in a tablix within SQL Server Reporting services 2008 R2. In all the previous versions of SQL Servers (SQL Server 2005/2008), we can use only single dataset for a table/matrix/chart.

2.     How we can use multiple datasets in a tablix?         

SQL Server Reporting Services 2008 R2 has introduced a new function called “LOOKUP”. LOOKUP function is used to retrieve the value from multiple datasets based on 1 to 1 mapping. For example if we have two datasets and both the datasets have EmpID so based on the EmpID mapping, we can retrieve the data from both the datasets.

Syntax: LOOKUP (Key value from already mapped dataset, Key value from new dataset, new value from new dataset)

3.     Create data source and datasets for the report

                    I.            Create a reporting solution in SQL Server Business Intelligence Development Studio.

                  II.            DataSource Creation:

·         For creating a data source for Report, go to Shared data sourceèright clickè Add New data source

·         Click on Editè Provide the server name and database and click ok.

               

·         Now we can see the connection string for our source server. Click ok.

                         

               III.            Dataset Creation:

·         For creating a dataset, go to Shared datasetsèAdd new dataset

·         Choose the DataSource for the dataset and provide the query:

                       

·         Click ok. Below we can see MyDataset1 has been created:

Follow the same steps for creating MyDataset2.

Query for MyDataset1:

SELECT ProgrammerName,SALARY FROM dbo.Programmer

It returns the programmer name and their salary

Query for MyDataset2:

SELECT ProgrammerName, Skill FROM dbo.Programmer

It returns the programmer name and their skills.

Here we can see the two datasets in the solution explorer:

4.     Create a tablix report with two datasets

                    I.            Create a tablix report : For creating a tablix report, Right click on Reports folderèAddèNew ItemsèReportèGive the name of the report e.g. ProgrammerDetailReport.

                  II.            Insert a tablix in the report: Right click on design surfaceèInsertèTable

 

                III.            Set the dataset for the tablix:

Let’s select MyDataset1 and click ok.

                IV.            Now we can map the columns from MyDataset1 to the tablix:

                  V.            Below is the report after formatting:

                VI.            Now if you see the property of the report for dataset. It will show two options either MyDataset1 or none. Even there is no option for writing the expression. Moreover, in all the previous version of the SQL Server Reporting services, there is no option for mapping multiple datasets to a tablix.

5.     Step by step procedure to implement multiple datasets in a single tablix

                    I.            Add one more column to the report by right click on the last columnèInsert columnèRight

                  II.            Now we can see another column in the report:

                III.            Now right click on the text box of new columnèchoose Expression:

                IV.            Use the LOOKUP function in the expression editor to map both the datasets and click Ok.

Expression:=Lookup(Fields!ProgrammerName.Value,Fields!ProgrammerName.Value,Fields!Skill.Value,"MyDataSet2")

             

                  V.            We can give the column header text as Skill. Now report will looks like below one:

6.     Conclusion

By using the LOOKUP function introduced in SSRS 2008 R2, we can map multiple datasets to a tablix.