1

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")
sql2015
  • 591
  • 3
  • 13
  • 34
  • see https://stackoverflow.com/questions/17954642/use-two-different-datasets-in-single-report – samuel gast Aug 28 '19 at 09:31
  • @testtoo I saw this and have referenced my field from dataset 2 in the expression but it just repeats the same value through the report there is no link to the first dataset – sql2015 Aug 28 '19 at 09:37
  • You can use the Lookup Function to get data from a second dataset. – Amira Bedhiafi Aug 28 '19 at 10:08

1 Answers1

3

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
AnkUser
  • 5,421
  • 2
  • 9
  • 25
  • thanks for your answer this does what I need it to do however if there is no match in the two datasets I want to use a field from the original dataset is this possible? it would be like adding an else to the lookupset? – sql2015 Oct 17 '19 at 11:11
  • `=IIF(Isnothing(Lookupset(Fields!ID.Value,Fields!ID.Value,Fields!Subject.Value,"DataSet2")),Fields!Subject.Value,join(Lookupset(Fields!ID.Value,Fields!ID.Value,Fields!Subject.Value,"DataSet2"),","))` Note: Fields!Subject.Value is the filed of your context table i.e if you have tablix using table 1 then it is table 1, if you have tablix using table 2 then Fields!Subject.Value is from table 2 – AnkUser Oct 17 '19 at 11:34
  • is it possible to sum the field of this lookup? currently returning value like 1,2,3,4 i would like it to be the sum so just the value 10 – sql2015 Feb 19 '20 at 14:21