Solved

How to Setup Optional Parameters for Report Server Report

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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

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…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

749 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