Specify One or All records in Dropdown

BobRosas
BobRosas used Ask the Experts™
on
I want to either select “Null” from the dropdown for all Supervisor records or select a specific Supervisor from the dropdown.  If I fill in Supervisor it works but if I leave it null I get nothing…instead of all records.  What do I need to change so that one of the dropdown options allows the user to select all Supervisors?

My dataset code is...
SELECT        SupervisorID, Supervisor
FROM            vw_HrLkUpSupervisor
WHERE        (SupervisorID IN (@Supervisor)) OR
                         (@Supervisor IS NULL)
ORDER BY Supervisor

My Supervisor parameter code is...
SELECT DISTINCT SupervisorID, Supervisor
FROM            vw_HrLkUpSupervisor
UNION
SELECT        ' ALL' AS Supervisor, NULL AS SupervisorID
ORDER BY Supervisor

Sample data is…
NULL
ATKL3456
SOFT8765
BNAB9123

I have tried setting the Manager parameter to "Allow null value" and “Allow blank value”.
I have value field as SupervisorID and label as Supervisor.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim HornSQL Server Data Dude
Most Valuable Expert 2013
Author of the Year 2015

Commented:
My usual method is something like this...

SELECT   -1 as SupervisorID, ' < ALL > ' as Supervisor
FROM MakeUpATableNameHere
UNIONvw_HrLkUpSupervisor
SELECT     SupervisorID, Supervisor
FROM            vw_HrLkUpSupervisor
WHERE        (SupervisorID IN (@Supervisor)) OR (@Supervisor IS NULL)   -- ?
ORDER BY Supervisor

Then whatever SQL/VB/? process this test for SupervisorID = -1.  If yes return all records, if it's a value then add that to the WHERE

Author

Commented:
Thank you for your help but I do not understand the code.  You are saying I need to create a temporary table to do this?  I tried just making up a table name tblTEMP and of course got the error that the table wasn't found.  
I don't understand the extra complexity.  I have a form where the user can either select one user or all.  The only difference I can see is that the query has a Where clause baasd on the supervisor…

This works for selecting all or one employee…
SELECT        LastName + ', ' + FirstName AS Name, IndID
FROM            vw_HrIndividuals
WHERE        (Manager IN (@Supervisor))
UNION
SELECT        ' All' AS Name, NULL AS IndId
ORDER BY Name

 So I tried adding your WHERE clause to my existing code without the temp table…

SELECT DISTINCT SupervisorID, Supervisor
FROM            vw_HrLkUpSupervisor
WHERE        (SupervisorID IN (@Supervisor)) OR
                         (@Supervisor IS NULL)
UNION
SELECT        ' ALL' AS Supervisor, NULL AS SupervisorID
ORDER BY Supervisor

It works in query design but when I run the report I get the error…
      Parameter ‘Supervisor’ has a DefaultValue or a ValidValue the depends on the report
      parameter ‘Supervisor”.  
Isn’t that because the code is referring to itself?

I thought the WHERE clause in the query that works just narrowed the scope of employees.    Since I don’t want to narrow the scope I just left it out and I don’t understand why it doesn’t work.

I do appreciate your help.  I’m sorry I’m not getting this.  Since I have a very similar example that works I’m trying to keep this just as simple.
May be you can manage your @SupervisorID to handle an ID or a "ALL" string value (instead of NULL)
The SQL is slightly different
...
WHERE        (tblHRIndReview.ReviewDate BETWEEN @From AND @To) AND (@SupervisorID ='ALL') OR
                         (tblHRIndReview.ReviewDate BETWEEN @From AND @To) AND (vw_HrLkUpSupervisor.SupervisorID = @SupervisorID)
...

Author

Commented:
That works!  Thank you so much!  I've maxd out points because I've been working on this for awhile and I'm so glad you found a resolution for me!

Author

Commented:
Thanks again for all your help!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial