Search code examples
vbareporting-servicesssrs-tablix

In SSRS, how do you display an expression based on what parameter is selected?

I have a report where the user can select an employee's name to use on the report from a parameter. The available parameter values come from a table that simply has 2 columns, EmployeeName and JobTitle.

When the user selects "Joe Smith" for example, the report displays that name. This part works correctly. But I want to have Joe Smith's job title show up below his name, so this should be based on what parameter (EmployeeName) is chosen and uses the same table to retrieve the job title.

What I tried so far:

=Iif(Parameters!Personnel.Value="", Nothing, First(Fields!Job_Title.Value, "Personnel"))

The result right now is that the first Job Title in the list displays, but it won't display any other job titles in the table.

Please help suggest how I can change the code so it will always display the correct job title based on the employee chosen.

Solution

  • You need to use the LOOKUP() function.

    You'll need something like this...

    =LOOKUP(Parameters!Personnel.Value, 
        Fields!EmployeeName.Value, 
        Fields!Job_Title.Value, 
        "Personnel")

    It's easier to read in reverse order so...

    • In the "Personnel" dataset
    • Return the Job_Title field
    • Where EmployeeName field
    • Matches the Personnel parameter value