1

I am using BIDS 2008 to create SSRS reports.

I have two data sets (one is manually created, one from SQL Server) and I'm trying to retrieve a third value from one data set, where it to other fields match.

I've been trying to use a IIF statement but have had no luck.

Example below;

=IIF(Fields!Name.Value, "Dataset1" = Fields!Name.Value, "Dataset2",Fields!Age.Value, "Dataset2,"")

Hopefully someone can help, I'm rather stuck right now...

Razvan Dumitru
  • 11,815
  • 5
  • 34
  • 54
Micha
  • 81
  • 1
  • 8
  • You may find the answer here: http://stackoverflow.com/questions/10342896/using-fields-in-multiple-datasets-in-ssrs. – mordack550 May 22 '14 at 15:26
  • 2
    Is your SSRS instance running 2008 or 2008 R2? If you're using R2, you can use the Lookup() or Lookupset() functions to retrieve data from other datasets based on lookup keys. – kyzen May 22 '14 at 16:29
  • I believe its running R2, what would be the format of using the Lookup() or Lookupset() function? – Micha May 22 '14 at 18:41

1 Answers1

0

You want LookUp() by the looks of things. LookUp() matches data across datasets where there is a 1-1 relationship, like in the case of your Name fields matching...

=LookUp(Fields!Name.Value, Fields!Name.Value, Fields!Age.Value, "Dataset2")

Dan Scally
  • 1,922
  • 1
  • 19
  • 31
  • This looks fantastic, however I am getting an "Unknown Collection Member" on the second Fields!Name.Value. How do I tell BIDS that the second Fields!Name.Value is from a different Dataset than the first? – Micha May 23 '14 at 08:25
  • The 4th Parameter in LookUp() does that. The assumption is the Tablix in the report that you're putting the expression in is linked to "DataSet1", so the first parameter referring to Fields!Name.Value is assumed to be from that dataset. The second Fields!Name.Value and the Fields!Age.Value are specified as being in "Dataset2" as part of the LookUp() function. Make sure that the Tablix is bound to "Dataset1" by going to Tablix Properties and in the Dataset name field selecting "DataSet1" – Dan Scally May 23 '14 at 08:47