asked on

Expression to lookup a value of one field based on another in SSRS

I'm relatively new to SSRS.

I have a dataset (HouseholdMembers) that returns the names and positions of everyone in a household, and use that for one table in the report.

But in another table, I need to extract the values of the FullName field for the individuals where the Positions are 'CG1', 'CG2', and 'Child'.

What expression should I use to perform this lookup?

Arifhusen Ansari

Hello Dale.

there is feature in SSRS to look up other data set based on values in one data set.

Below is the function:

=lookup(Filedfrom first datasource(your case its position) , Filed of second dataset ( Position from second set), Filed to retrive ( name ), "second data set name")

Open in new window

I have attache sample rdl as well.
LoopUP.rdl

Dale Fye

ASKER

Arihhusen,

I tried:

=Lookup(Fields!FullName.Value, Fields!Position.value = "CG1", "Householdmembers")

But that obviously fails as it doesn't contain the correct number of arguments. The goal is to select the value in the [FullName] column of [HouseholdMembers] table where the [Position] value = 'CG1'

Arifhusen Ansari

Please download the rdl file. there is a code. so you can understand

Dale Fye

ASKER

what rdl file? Are you talking about mine, or did you think you posted one?

ASKER CERTIFIED SOLUTION

Arifhusen Ansari

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dale Fye

ASKER

Thanks,