SSRS two datasets in one report

Question

I have a SSRS report with two datasets. My report has one table with all fields from dataset 1. Is it possible to show the field from dataset 2 if the id field from dataset 1 = the id field from dataset 2?

I've tried using an expression on the field in the table to point to the field in dataset 2 but there is no link between the 2 datasets which I think I need. are you able to do a select clause within the expression on the report?

any guidance would be appreciated

I have tried the below as the expression but it doesn't work :

=iif Sum(Fields!ID.Value, "DataSet2")=Sum(Fields!ID.Value, "DataSet3") then First(Fields!total.Value, "DataSet3")

Answer 1

Your question is very classic case of SSRS.

You have 2 ways to achieve this

1. SubReports (Which I personally like)
2. Lookup functions (which I sometimes find difficult dealing with)

Method1: Use subreport to display one report in another report

• .Create another report as the subreport and insert the child row data.
• Create a parameter named ID in the subreport.
• In the main report, right-click to insert a subreport in the child row.
• Right-click the subpeort to open the Subreport Properties, and select the subreport name in the drop-down list.
• In the left panel of the Subreport Properties dialog box, click Parameters.
• Select Name in the drop-down list of ID, and select [ID] in the drop-down list of Value.

Method2: Use lookupset function in SSRS

In the Expression dialog box, modify the expression to look like this:

=join(Lookupset(Fields!ID.Value,Fields!ID.Value,Fields!Subject.Value,"DataSet2"),",")

The following screenshot is for your reference(The tablix is to use DataSet1):

Refrences:

1. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/f30ff0d2-ba37-4bb8-b0a0-a6e06b3273fd/two-datasets-inside-a-same-tablix?forum=sqlreportingservices
2. https://social.msdn.microsoft.com/Forums/sqlserver/en-US/2e6dfb5a-2de2-4d2f-96e5-4cb94543c6ec/asigning-two-data-sets-to-one-table-in-ssrs