Hi,

I have to create tablix report using data from 2 datasets. The data source for one of the dataset, DataSet1 is Microsoft SQL Server and the data source for the other dataset, DataSet2 is Excel.

I have to pic 3 columns from DataSet1 and one column from DataSet2.

I tried using a lookup function

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

The above expression is throwing #Error on that column in the output

Any suggestion what should I do?

Thanks

Hi Ravi,

Can you state the error thrown by the expression. Remember look up is supported in SSSRS 2008 R2 and above.

Below are the steps to retrieve data from multiple dataset 

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 sourceright click Add New data source <stroke joinstyle="miter"></stroke><formulas><f eqn="if lineDrawn pixelLineWidth 0"></f><f eqn="sum @0 1 0"></f><f eqn="sum 0 0 @1"></f><f eqn="prod @2 1 2"></f><f eqn="prod @3 21600 pixelWidth"></f><f eqn="prod @3 21600 pixelHeight"></f><f eqn="sum @0 0 1"></f><f eqn="prod @6 1 2"></f><f eqn="prod @7 21600 pixelWidth"></f><f eqn="sum @8 21600 0"></f><f eqn="prod @7 21600 pixelHeight"></f><f eqn="sum @10 21600 0"></f></formulas><path gradientshapeok="t" o:connecttype="rect" o:extrusionok="f"></path><lock aspectratio="t" v:ext="edit"></lock>          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 datasetsAdd 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 folderAddNew ItemsReportGive the name of the report e.g. ProgrammerDetailReport.                   II.            Insert a tablix in the report: Right click on design surfaceInsertTable                   III.            Set the dataset for the tablix: Lets 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 columnInsert columnRight                   II.            Now we can see another column in the report:                 III.            Now right click on the text box of new columnchoose 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:

Hi Ravi, 

According to your description, when using Lookup() function in the expression, you get an error, right?

In Reporting Services, we can use Lookup() function to retrieve the value from the specified dataset to return the required data.  In you scenario, you need to make sure that the expression has no issue and the fields name are correct in the two datasets.  In our local environment, we have tested this scenario and the report works properly.

Expression: =Lookup(Fields!Type.Value,Fields!Type.Value,Fields!Value.Value,"DataSet7")

The result is like below: 

Reference: 

Lookup Function (Report Builder and SSRS)

If you have any other question, please feel free to ask. 

Regards,
Shrek Li

If you have any feedback on our support, please click here. 

• Edited by Shrek LiMicrosoft contingent staff 5 minutes ago