Subreport Table data scope issue SSRS

Question

I'm trying to select data from other datasets to include in my table. This is the expression I have so far:

=iif(ReportItems!ID.Value=1
, (First(Fields!NumbBreaker.Value, "sp_Permit11"))
,iif(ReportItems!ID.Value=3
, (First(Fields!MoldProd.Value, "sp_PermitASMoldProd"))
,iif(ReportItems!ID.Value =4
, (First(Fields!MoldProd.Value, "sp_PermitASMoldProd"))
,iif(ReportItems!ID.Value =5
, (First(Fields!Thermal.Value, "sp_PermitThermalSand"))
,iif(ReportItems!ID.Value=6
, ((First(Fields!Steel20T.Value, "sp_Permit11")) + (First(Fields!Steel9T.Value, "sp_Permit11")) + (First(Fields!Ductile.Value, "sp_Permit11")))
,iif(ReportItems!ID.Value=7
, ((First(Fields!Steel20T.Value, "sp_Permit11")) + (First(Fields!Steel9T.Value, "sp_Permit11")))
,iif(ReportItems!ID.Value=8
, (First(Fields!IMF.Value, "sp_Permit11"))
,iif(ReportItems!ID.Value=9
, (First(Fields!Ductile.Value, "sp_Permit11"))
,iif(ReportItems!ID.Value = 10
, (First(Fields!DM1.Value, "sp_PermitDM1"))
,iif(ReportItems!ID.Value = 12
, (First(Fields!Zircon.Value, "sp_PermitZircon"))
,iif(ReportItems!ID.Value = 14
, (First(Fields!CMN.Value, "sp_PermitCMN"))
,iif(ReportItems!ID.Value = 15
, (First(Fields!A270.Value, "sp_Permit270"))
,iif(ReportItems!ID.Value= 16
, (First(Fields!A290.Value, "sp_Permit290"))
,iif(ReportItems!ID.Value = 17
, (First(Fields!CM8.Value, "sp_PermitCM8"))
,iif(ReportItems!ID.Value = 20
, (First(Fields!NT.Value, "sp_PermitNT")),"")))))))))))))))

How can I do this without using the First? The first is only bringing in the first value but without it I get:

Report item expressions can only refer to fields within the current dataset scope or, if inside an aggregate, the specified dataset scope

Why don't you want to use "first"? What are you trying to do? — Tab Alleman
Because there are 3 rows of values that i want to display, not just the first one. — Bob
So depending on the value of ReportItems!ID.Value you want the table to show the 3 rows from one of the various datasets? — Chris Latta
The only way I know to do what you want is to combine all your datasets into one. — Tab Alleman

Chris Latta Chris Latta · Accepted Answer · 2014-09-25T00:43:09

You can't select data from another dataset without specifying which data you want. This is why you need the First function - it is the easiest way to specify what to select from the other dataset.

However, there are other ways to select the data that you want. I'll assume that each dataset has the unique ID field so we can use the Lookup function. Also, let's change from using IIF and use Switch instead as it is a bit more convenient for this sort of statement.

Now your expression will look like this:

=Switch(
  ReportItems!ID.Value = 1, Lookup(Fields!ID.Value, Fields!ID.Value, Fields!NumbBreaker.Value, "sp_Permit11"), 
  ReportItems!ID.Value = 3, Lookup(Fields!ID.Value, Fields!ID.Value, Fields!MoldProd.Value, "sp_PermitASMoldProd"),
  ReportItems!ID.Value = 4, Lookup(Fields!ID.Value, Fields!ID.Value, Fields!MoldProd.Value, "sp_PermitASMoldProd"),
  ReportItems!ID.Value = 5, Lookup(Fields!ID.Value, Fields!ID.Value, Fields!Thermal.Value, "sp_PermitThermalSand"),
  ReportItems!ID.Value = 6, Lookup(Fields!ID.Value, Fields!ID.Value, Fields!Steel20T.Value, "sp_Permit11") + Lookup(Fields!ID.Value, Fields!ID.Value, Fields!Steel9T.Value, "sp_Permit11") + Lookup(Fields!ID.Value, Fields!ID.Value, Fields!Ductile.Value, "sp_Permit11"), 
  ReportItems!ID.Value = 7, Lookup(Fields!ID.Value, Fields!ID.Value, Fields!Steel20T.Value, "sp_Permit11") + Lookup(Fields!ID.Value, Fields!ID.Value, Fields!Steel9T.Value, "sp_Permit11"),
  ReportItems!ID.Value = 8, Lookup(Fields!ID.Value, Fields!ID.Value, Fields!IMF.Value, "sp_Permit11"), 
  ReportItems!ID.Value = 9, Lookup(Fields!ID.Value, Fields!ID.Value, Fields!Ductile.Value, "sp_Permit11"), 
  ReportItems!ID.Value = 10, Lookup(Fields!ID.Value, Fields!ID.Value, Fields!DM1.Value, "sp_PermitDM1"), 
  ReportItems!ID.Value = 12, Lookup(Fields!ID.Value, Fields!ID.Value, Fields!Zircon.Value, "sp_PermitZircon"), 
  ReportItems!ID.Value = 14, Lookup(Fields!ID.Value, Fields!ID.Value, Fields!CMN.Value, "sp_PermitCMN"), 
  ReportItems!ID.Value = 15, Lookup(Fields!ID.Value, Fields!ID.Value, Fields!A270.Value, "sp_Permit270"), 
  ReportItems!ID.Value = 16, Lookup(Fields!ID.Value, Fields!ID.Value, Fields!A290.Value, "sp_Permit290"), 
  ReportItems!ID.Value = 17, Lookup(Fields!ID.Value, Fields!ID.Value, Fields!CM8.Value, "sp_PermitCM8"), 
  ReportItems!ID.Value = 20, Lookup(Fields!ID.Value, Fields!ID.Value, Fields!NT.Value, "sp_PermitNT"), 
  True, "")

So we look up the value we want in the other dataset based on a unique key in the current dataset and return whichever field we want from the other dataset. The True, "" bit at the end is effectively the else condition - it is what is returned if no other condition is met.

Subreport Table data scope issue SSRS

1 Answers