[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1045
  • Last Modified:

How to Setup Optional Parameters for Report Server Report

I'm designing a SQL Server Report Server 2005 report, which calls a stored procedure with several parameters. I have four multi -value parameters and two single valued parameter, and the report works right now. The only problem is the report requires the user to select a value for each parameter, but I need the report to work when at least one or more of the four multi-value parameter have value(s) selected, not all of them. Is it possible to do? if yes, how?

Thanks much in advance for your help.
0
eecpamerica
Asked:
eecpamerica
1 Solution
 
randomjamesCommented:
You could set the default for the parameter in the stored procedure.

Books Online:

"If a default value is defined, the procedure can be executed without specifying a value for that parameter. The default must be a constant or it can be NULL. If the procedure uses the parameter with the LIKE keyword, it can include the following wildcard characters: % _ [] and [^]."

Set the default like this;
CREATE PROCEDURE blahblah 
(
     @SomeDateParameter = '1/1/2007',
     @SomeStateParameter = 'AZ'
)
 
AS
 
SELECT blah blah FROM blah blah

Open in new window

0
 
eecpamericaAuthor Commented:
Thanks for the reply.

it appears It's the report server who's checking the parameters, so even when a default is provided for the sp parameters, the report parameter is still null and it still throws the error out.

I used a work around to add a value to the list which basically is a non exist value and set the report parameter's default to that to trick the report server to believe a value is selected for the report parameter.
0

Featured Post

Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now