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?
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)

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)
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

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], '')
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?
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!
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.