Friday, March 2, 2012

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.



----------------------------------------------------End of article---------------------------------------------------



19 comments:

  1. What about the case if the two DataSet point in different dataBase ?

    ReplyDelete
  2. You have some really good stuff. Please do as many SSRS stuff as you can. Here are some things I would recommend doing to help others.

    1) It's next to impossible to find any resource on how the set the parameters from an aspx page to a reportviewer. It took me a long time but I figured it out.
    2) Why Switch is better the using the IIF.
    3) Formatting formatting formatting. What is the best procedure so your report will print out correctly on a certain paper size like 8.5X11 for U.S.

    ReplyDelete
  3. I am getting Error Scope parameter is not valid.
    I am using SSRS2012 Evaluation Edition.
    Checked with the datatype and that too was same.

    This is the code:
    Dataset Names:
    1)dsABC(NAME,AGE)
    2)dsDEF(NAME,SALARY)
    Tablix is Binded to dsABC
    =Lookup( Fields!NAME.Value,
    Fields!NAME.Value,
    Fields!SALARY.Value,
    "dsDEF"
    )

    ReplyDelete
    Replies
    1. Hi,
      I done already group by userid and sequence with details in Tablix
      I want to group based on column sequence with details in same Tablix in bottom
      can You provide solution please.

      Delete
  4. Good explanation for a easy how-to-do. Thanks

    ReplyDelete
  5. I don't believe this works if each dataset is pointed at a different database. If I'm wrong about this, I would love to see how it is done.

    ReplyDelete
  6. Kool, Today I had such a need, your article was helpful,
    Thank you

    ReplyDelete
  7. Could you help me here please?
    http://sharepoint.stackexchange.com/questions/64413/ssrs-get-lookupset-results-on-different-cells
    I got a problem with multiple values returned by LookupSet expr..

    ReplyDelete
  8. thankx a ton, it has saved my lot of time.

    ReplyDelete
  9. thankx a ton, it has saved my lot of time.

    ReplyDelete
  10. you can just join those two tables in your query right...why do we need to use lookup at the report level. it looks useless

    ReplyDelete
    Replies
    1. Its useful when your dataset is not based on an SQL query, for example when using Sharepoint List as data source.

      Delete
  11. Hi I have two datasets in my report and one dataset named DataSet1(contains PromId as a identity) is assigned to list control and in that list control i want to add table which will use DataSet2(contains PromLaID as a identity and PromId is a reference).
    I want to create group on outer list by PromId in DataSet1 and group on inner table by PromLaID in DataSet2(Which filters the data by PromId in DataSet1). Please suggest

    ReplyDelete
  12. This comment has been removed by the author.

    ReplyDelete
  13. Hi thanks it works.
    But what is to do when i have a join from a join ->
    table1 -> table2 -> table3
    Tablix has t1.pk_cust joined to t2.fk_cust / t2.fk_address and this is joined to t3.pk_address

    ReplyDelete
    Replies
    1. Can you nest Lookups? I've never tried it...

      Delete
  14. good work, simple but superb to understand at single glance.

    ReplyDelete
  15. What if lets say programmerskill was a number, and you would like to sum it in the end, how do you do that?

    ReplyDelete