Report services, select one parameter or another

I want to create a report services form that allows the user to select...
Fom Date AND To Date  AND
(Name  OR  SSN)
I created the dataset and it will return data with either SSN OR Name.  But when I run the form with all 4 parameters displayed (which is what I would like) it requires they all be filled out.  How is this usually handled?
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Christopher GordonSenior Developer AnalystCommented:
I usually handle this the following way:

1. Right Click Parameter in SSRS designer.
2. Select "Allow null value" checkbox.
3. Handle the potential NULL value in your stored procedure (I'm assuming this is your datasource) like this:

where SSN = isnull(@SSN,SSN)

BobRosasAuthor Commented:
Thank you for your fast response.  I have the "Allow null value" box checked.  Now I'm trying to figure out how to use the code.  I tired the following but it still wants me to fill out SSN.  My field name is [SSN/SIN] and I use SSN as the "Value field" for Name Property also where I use "Get values from a qurey".  Am I still missing something?

WHERE        ([Check Date] BETWEEN @From AND @To) AND ([SSN/SIN] = ISNULL([SSN/SIN],@SSN) OR [SSN/SIN] = @Name)
Christopher GordonSenior Developer AnalystCommented:
When you say "it" still wants me to fill out the SSN, are you getting an error thrown by SSRS?  By default, when you select that check box on the parameter screen, you should see a NULL checkbox appear to the right of the textbox you've specified to allow null values.

Your where clause might look like this if the parameters are all optional.  Test out in management studio to verify


      [check date] >= isnull(@from, '1/1/0001')      --or min date

and      [check date] <= isnull(@to, '12/31/9999')      --or max date

and isnull([SSN/SIN],'') = coalesce(@SSN, [SSN/SIN], '')
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

BobRosasAuthor Commented:
Thanks again.  I'm using your code and I went ahead and made all fields optional.  When I did a check box showed up for the dates but not for Name and SSN.  So I changed "Available values" from "Get values from a query" to "None" and now the check box shows but I'd still like the dropdown to be filled with a query.  Can I have both?  If so, how?
Christopher GordonSenior Developer AnalystCommented:
You'll have to create a "NULL" type of option in your source.  I usually handle like this in the SQL object returning data to your dropdownlist (you can also do this in SSRS):

select 0, 'All/Ignore' as Name

union all

select Id, Name
from myDataSource

Now your dropdownlist has a new "selectable" value called 'All/Ignore'.  When this is selected, it will pass "0" or "" (however you want to do it) to your procedure.  Your procedure can then process like this:

--if '0'
where isnull(myField,'') = coalesce(nullif(@myfield,0), myField, '')

--if ''
where isnull(myField,'') = isnull(@myField, myField)

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BobRosasAuthor Commented:
Thank you so much!  You've been so helpful.  
I'll award points and post a related question if needed.
BobRosasAuthor Commented:
Thanks again!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.