Solved

How to Setup Optional Parameters for Report Server Report

Posted on 2007-12-03
2
1,038 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

Question has a verified solution.

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

Suggested Solutions

Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

739 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