CODEWITHSUNDEEP
sql-serverreporting-servicesdatasetssrs-2012
sql2015
sql2015

Reputation: 611

SSRS two datasets in one report

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")

Upvotes: 1

Views: 3993

Answers (1)

AnkUser
AnkUser

Reputation: 5541

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):

enter image description here

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

Upvotes: 3

Related Questions