Solved

How to Setup Optional Parameters for Report Server Report

Posted on 2007-12-03
2
1,034 Views
Last Modified: 2008-04-11
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
Comment
Question by:eecpamerica
2 Comments
 
LVL 3

Expert Comment

by:randomjames
ID: 20401081
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
 

Accepted Solution

by:
eecpamerica earned 0 total points
ID: 20817946
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Create a linked  server to connect ot an Access Database. 26 46
CROSS APPLY 4 44
Unable to save view in SSMS 21 59
Sql query 107 27
by Mark Wills PIVOT is a great facility and solves many an EAV (Entity - Attribute - Value) type transformation where we need the information held as data within a column to become columns in their own right. Now, in some cases that is relatively…
In SQL Server, when rows are selected from a table, does it retrieve data in the order in which it is inserted?  Many believe this is the case. Let us try to examine for ourselves with an example. To get started, use the following script, wh…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

910 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now