Solved

How to Setup Optional Parameters for Report Server Report

Posted on 2007-12-03
2
1,035 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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Change variables in SQL table 6 87
How to query LOCK_ESCALATION 4 41
how to eliminate duplicates in a string variable in t-sql? 30 67
Need help debbuging stored procedure 21 41
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…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

776 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