CODEWITHSUNDEEP
reporting-services
J. Furr
J. Furr

Reputation: 3

Lookup Function SSRS 3 datasets

I have 3 datasets in a report where I need to run a lookup to bridge common data between. I can look up the owner's IDs when it's kept in 2 datasets with:

=Lookup(Fields!DatabaseName.Value, Fields!Name.Value, Fields!OwnerID.Value, "DataSet2")

What I've tried so far without success for connecting that 3rd dataset and returning owner names is the following:

=Lookup(Fields!DatabaseName.Value & Fields!Source_Worker_ID.Value, Fields!Name.Value & Fields!OwnerID.Value, Fields!Full_Name.Value, "DataSet3")

Dataset 1 contains DatabaseName (It's all the DBs I need to patch).

Dataset 2 contains Name (Same as DatabaseName) and OwnerID (Owners I'll need to contact for scheduling).

Dataset 3 contains Source_Worker_ID (Same as OwnerID) and Full_Name (Employee Names).

My end goal is to return the owner IDs from DS 2 related to all DBs in DS 1 with a lookup(Or other function), then return all the names from DS 3 for those IDs found during the first lookup.

Does anyone have any ideas on making this lookup work?

Thanks

Upvotes: 0

Views: 3534

Answers (1)

Crazy Cucumber
Crazy Cucumber

Reputation: 479

Edited answer:

I apologize. You're right. I was unaware of the fact that Nested lookups is not possible. BUT, there is another way you could do it.

  1. Add a textbox to your report.
  2. Give the textbox the following expression:

LOOKUP(Fields!DatabaseName.Value, Fields!DatabaseName.Value, Fields!OwnerID.Value, "Dataset2")

  1. Right click on the textbox -> Textbox Properties -> Visibility -> Select Hide
  2. Go to the text box in question and add this expression:

LOOKUP(ReportItems!Textbox1.Value, Fields!Source_Worker_ID.Value, Fields!Full_Name.Value, "Dataset3")

Tell me if this works.

Explanation:

A LOOKUP function only takes 4 arguments.

  1. Source Expression: This is the field in the first and primary dataset that you are going to use to find values of other fields in other dataset. In your case, DatabaseName.
  2. Destination Expression: This is the name of the field you are going to match your first field with. In your case, since Dataset2 has the same column name as Dataset1 (DatabaseName), it will be DatabaseName again.
  3. Destination Value: This is the value corresponding to the given destination expression you are hoping to get. Since you want the OwnerID for any given DatabaseName, you need the OwnerID as the third parameter.
  4. Data set: This is the dataset you are trying to look for this value at. In your case, its Dataset2 (or dataset3).

You can play with only these 4 arguments.

Explanation 2

It is important to remember that you can ONLY connect from your primary dataset to other datasets. If your report is being built from Dataset1, you can lookup values in ALL OTHER datasets USING Dataset1. You cannot lookup value in Dataset3 using a value from Dataset2. That is why you need a lookup to find the value from Dataset2 and use it to find a value in Dataset3.

DEMO:

In this image:

enter image description here

the first column that has a 5 digit number is from DataSet1. The second column with a 2 digit number is the field Location from DataSet2. The third column with a name is the field LocationName from DataSet3.

Here is how these three datasets connect to each other:

enter image description here

Here are the lookup queries I used:

=LOOKUP(Fields!SORD.Value, Fields!ShopOrder.Value, Fields!Location.Value, "DataSet2")

=LOOKUP(ReportItems!Textbox4.Value, Fields!Location_ID.Value, Fields!Location_Name.Value, "DataSet3")

This seems to work fine for me. Here is what the table looks like:

enter image description here

Here is a screenshot of my report data sources and shared data sources:

enter image description here

Are you doing something like this? I know this is a very simplified report and your situation might be much more complex, but your report needs to look something like this.

If none of this helps, I apologize. This is the only way I know how to do.

Upvotes: 1

Related Questions