CODEWITHSUNDEEP
ssrs-2008reportreportingreporting-services
Zi0n1
Zi0n1

Reputation: 594

Lookup function for reporting services (visual studios 2008)

I am currently building a report that requires two datasets and two data sources. I am trying to combine the two data sets together with a common field.

My two data sets are: DSSES (tied to SESSQL source) and DSQQST (tied to QQST source)

My DSSES has employee ID field and my DSQQST has Employee_ID field as well. I want to use these two fields too compare the two data sets. Basically the purpose of this report is too find if an employee has changed his or her name. I want to use the lookup function to compare the two employee ID fields and then produce a list of the first name and last name in each data set to produce an answer.

Here are all of the fields of my two datasets:

DSSES: EmployeeID EmploymentStatusCode LastName FirstName PreferredName EmployeeType

DSQQST: Employee_id company_id employeeid firstname middlename lastname supervisor_id active_yn department_id hire_dt sup_dept_id term terminationDate

Upvotes: 0

Views: 3821

Answers (1)

Chris Latta
Chris Latta

Reputation: 20560

Make a table with five columns linked to the DSSES dataset. Show the EmployeeID, FirstName and LastName fields. In the fourth column, put the following formula:

=Lookup(Fields!EmployeeID.Value, Fields!Employee_id.Value, Fields!firstname.Value, "DSQQST")

and similar for the LastName in the fifth column.

Now change the Visibility-Hidden expression to be:

=IIF((Fields!LastName.Value = Lookup(Fields!EmployeeID.Value, Fields!Employee_id.Value, Fields!lastname.Value, "DSQQST")) AND (Fields!FirstName.Value = Lookup(Fields!EmployeeID.Value, Fields!Employee_id.Value, Fields!firstname.Value, "DSQQST")), True, False)

So basically show matching results from each dataset and hide the ones where the first names and last names match, thereby only displaying those that are different.

Upvotes: 1

Related Questions