SSRS: Optional Parameter

Hello:

I have read articles on making a parameter in SQL Reporting Services (SSRS) be optional.  But, no matter what I do, the attached report will not run unless I fill in information for both of the parameters.

Long story short, I want the second ContractNo parameter to be optional.  Some of the vendors in the other parameter (Vendor) do not have contract numbers.

So, I want to be able to either have the parameter be null or simply not have to make a selection in that field.

Please let me know how I can accomplish this.

Thanks!

TBSupport
POOR-Subreport.rdl
LVL 1
TBSupportAsked:
Who is Participating?
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.

Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
In the SSRS designer, click on the parameter, and with the General tab selected check the 'Allow Null Values' checkbox.

In whatever SQL object accepts this parameter, define it with an =NULL, like...
CREATE PROC YourProc (@CustomerID int, @dt date = NULL) 

Open in new window

Then somewhere in your SP handle it...
IF @dt IS NULL
  begin
  -- do something here
  end

Open in new window

Or handle it in the WHERE clause of a select, either filtering on it if it's a non-null value, or ignoring it if it is NULL...
SELECT goo, foo, boo
FROM YourTable
WHERE (dt = @dt OR @dt IS NULL) 

Open in new window

0
TBSupportAuthor Commented:
Hi Jim:

I'm afraid that that did not work.  I'm still getting the error telling me that I have to choose a value for ContractNo.

TBSupport
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
<wild guess>

Parameter ContractNo uses dataset ContractNo as its source, so it's probably enforcing that the user selected value is in that dataset before allowing the NULL.
SELECT     COALESCE (SY03900.TXTFIELD, '') AS ContractNo
FROM         SY03900 INNER JOIN
                      POP10110 ON SY03900.NOTEINDX = POP10110.POLNEARY_5

Open in new window

Try adding the NULL to your dataset SQL and see what happens.
SELECT a.ContractNO
FROM (
SELECT NULL as ContractNo, 0 as sort_order
UNION ALL
SELECT     COALESCE (SY03900.TXTFIELD, '') AS ContractNo, 1 as sort_order
FROM SY03900 
    INNER JOIN  POP10110 ON SY03900.NOTEINDX = POP10110.POLNEARY_5) a
ORDER BY sort_order, ContractNo

Open in new window

0

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
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

TBSupportAuthor Commented:
Thanks, Jim!  I'm afraid that SSSR is displaying this error when I try to save that script in the dataset:

The text, ntext, and image data types cannot be compared or sorted, except when using IS NULL or LIKE operator.

TBSupport
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
In that case, just ORDER BY sort_order, and delete the , ContractNo.
0
TBSupportAuthor Commented:
That worked perfectly!  Thanks, Jim!

TBSupport
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Thanks for the grade.  Good luck on your reports.  -Jim
0
Jim HornMicrosoft SQL Server Developer, Architect, and AuthorCommented:
Just verified in my own project that SSRS behaves this way.
Learn something new every day...
0
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
Microsoft SQL Server 2008

From novice to tech pro — start learning today.