CODEWITHSUNDEEP
sql-serverparametersssrs-2008reporting-services
JsonStatham
JsonStatham

Reputation: 10384

SSRS Parameters

I am creating a parameter for my report where the Label is the employee name, and the value is their Staff No. This is fine if everyone only has one staff number, however I have a number of people who have multiple, and I need all the data to be pulled through when I select their name, so that means multiple values.

Is this possible and if so how, i have tried separating the numbers with commors but this makes the report fall over.

enter image description here

Upvotes: 0

Views: 955

Answers (3)

hemanth
hemanth

Reputation: 587

Apart from your main query, create another dataset in which get all the "Employee Names". Go to parameter properties that have used in main query as "Employee Name" go to available values give the dataset name 2 and give value field and label fields..

Upvotes: 0

Paul Alan Taylor
Paul Alan Taylor

Reputation: 10680

Presumably you have a one-to-many link between your Employee record and the staff numbers. Why don't you filter using the parent item?

Upvotes: 0

Yuck
Yuck

Reputation: 50865

You should handle this in your query. Set up the parameter to accept an employee's name, and then filter the data in the report based on a look-up of that employee:

SELECT *
FROM YourReportView
WHERE EmployeeId IN (
  SELECT Id
  FROM SomeEmployeeTable
  WHERE EmployeeName = @EmployeeName
);

The @EmployeeName parameter would be mapped to the SSRS parameter.

Upvotes: 1

Related Questions