Share via

SQL Reporting - Optional Parameter with Available values set

Dominic Gagné 1 Reputation point
2022-10-18T19:19:29.737+00:00

Hello,
I want to have an optional parameter with "Available values" taken from a dataset query, with NULL as default. I have set "Allow blank" and "Allow null value" and set the default value to NULL, but the report trig an error if I don't select anything for this parameter: "Please select a value for the parameter XYZ".

Report calls a StoredProc to fetch data. Parameter is optional in the SP.
I can't find a way to do it. Anyone can help me?

Also, I have another parameter, which is a date. Allow null is set. When running the report from Report Builder, the NULL checkbox is set. But when deployed and the report is run from browser, the NULL checkbox isn't set and I have to manually set it, which is annoying for the user. Any advice for this?

thanks for your time and hep

SQL Server Reporting Services
SQL Server Reporting Services
A SQL Server technology that supports the creation, management, and delivery of both traditional, paper-oriented reports and interactive, web-based reports.
3,066 questions

1 answer

Sort by: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2022-10-19T06:20:40.663+00:00

    Hi @Dominic Gagné
    I did a simple test, passing parameters through a stored procedure. But I didn't encounter the error "Please select a value for the parameter" you said, it works fine.

    1. Create a stored procedure like this:
      251818-1.png
    2. Set the parameter
      251730-2.png
      251819-3.png
      251799-4.png
      Preview:
      251827-5.png
      I don't know your report situation very well, you can refer to this link for more details: report-parameters-report-builder-and-report-designer.
      As for your third question, I didn't find a way to change it, it seems to be by design.
      251800-6.png
      Hope these can help you.
      Best regards,
      Aniya
    1 person found this answer helpful.
    0 comments

Your answer

Answers can be marked as Accepted Answers by the question author, which helps users to know the answer solved the author's problem.