Avatar of BobRosas
BobRosas
 asked on

Specify One or All records in Dropdown

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.
SSRSMicrosoft SQL Server 2008Microsoft SQL Server 2005

Avatar of undefined
Last Comment
BobRosas

8/22/2022 - Mon
Jim Horn

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
BobRosas

ASKER
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.
ASKER CERTIFIED SOLUTION
BigSchmuh

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
BobRosas

ASKER
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!
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
BobRosas

ASKER
Thanks again for all your help!