Using two datasets to extract data in one report in SSRS 2008

Question

Hi,

I am creating a report using SSRS 2008,I have two datasets with following schemas;

Dataset1 -Oracle Dataset2-SQL Server

Name  Address  DOB  HireDate Name   EmpID

Final Report should be like;

EmpId  Name  Address  DOB  HireDate

Dataset1 uses two parameters HireDate between StartDate and EndDate.

Question: Unable to perform lookup in two datasets to retrieve EmpID based on Name key column.Is there any work around to achieve this in SSRS 2008?

Answer 1

If you have access to SSIS, just merge the datasets there and dump the results into a table as a single result set (can be Sql Server, Access, etc).

Then base your report off that table.

---

Bonediggler

Answer 2

Hi Nody88,

If I understand correctly, there may be four columns (Name, Address, DOB, HireDate) in the Dataset1, and the Dataset2 has the Name, EmpID columns. While you want to retrieve the EmpID column’s value based on the Name column from the two datasets in SQL Server Reporting Service(SSRS) 2008.

In fact, Lookup Function only can be used in SQL Server 2008 R2 Reporting Service and SQL Server 2012 Reporting Service. As a workaround, I suggest to use the subreport. We can use the fields of Dataset1 in the main tablix, and create the subreport using the EmpID field of the Dataset2. Please refer to the detailed steps below:

1. Create a subreport, create a dataset that uses the same query as the Dataset2 in the main report.
2. Insert a table, delete its first row, the second and third columns, and then drag the EmpID field to the table
3. Create a text type single value parameter @SubName, and add a filter to the dataset in the subreport as follows：

Expression:[Name]
Operator:=
Value:[@SubName]

4.In the main report, drag a subreport to the main tablix, and configure the parameter passed to the subreport as follows:

Name:SubName  Value: =Fields!Name.Value

Through the above steps, we can view the report with combining the two datasets.

If you need more assistance, please feel free to let me know.

Regards,
Heidi Duan