February 20, 2006 at 12:00 pm
I created a report that has an @Employee Parameter so the data can be filitered for that paticular employee. However I would like to have the users have the option to select "All Employees" in the same drop down as the Employee list. Not quite sure how to do this. I created a view dataset that just calls our employee list but how can I incorporate all of them? TIA.
February 20, 2006 at 2:01 pm
Why do you need to incorporate all of them in the list ? If they select a particular employee, you filter by it, if they select the static list of "--All Employees--", then you do not apply any filter criteria to the SQL code which will be the same as applying the remaining criteria on all the employees. Pass null to that parameter in the second case and formulate the SQL accordingly in the SQL code.
February 21, 2006 at 7:02 am
Use something like the following to generate the list of employee names:
SELECT employeeName, 1 orderby FROM employees
UNION
SELECT '-- All Employees --' employeeName, 999 orderby
ORDER BY orderby DESC, employeeName
--Peter
February 21, 2006 at 7:48 am
I have 2 ways I do this...
WHERE ISNULL(@EmployeeID, tbl.EmployeeID) = tbl.EmployeeID
--using null is cleaner in SQL, but a pain to extract from the drop down.
WHERE CASE @EmployeeID
WHEN -1 --I like using -1
THEN tbl.EmployeeID
WHEN 999 --as suggested above
THEN tbl.EmployeeID
ELSE @EmployeeID
END = tbl.EmployeeID
February 22, 2006 at 1:09 pm
Thanks for the suggestions. I ended up writing a long If then Else statment. It works but I'll have to see if I can rework it using your syntax. Thank again.
February 22, 2006 at 2:14 pm
I think all that is required is that the flag is set or the row id matches the one given.
AND (@EMPID = 999 OR EMPID = @EMPID)
--Peter
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply