There are some posts on the duplicate thread found here. I'll be adding new posts to this thread.
Is the employee number parameter a text box or a drop-down list? IF you convert it to a dropdown list you populate via a dataset. In the dataset you do something like:
DECLARE @login VARCHAR(256)
/*
This first value is a manager
*/
SET @login = 'adventure-works\ken0'
/*
this is not a manager
Set @login = 'adventure-works\rob0'
*/
;WITH cteEmpIds AS
(
SELECT
EmployeeID
FROM
HumanResources.Employee
WHERE
LoginID = @login
UNION ALL
SELECT
E.EmployeeID
FROM
HumanResources.Employee E JOIN
cteEmpIds CE ON
E.managerId = CE.EmployeeId
)
SELECT
EmployeeID
FROM
cteEmpIds
You wouldn't Declare the variable that was just so I could make sure it works. You could use the UserID function in SSRS to populate the query parameter. Then your dropdown list would only show the valid values for that user.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question