how to assign NULL as DEFAULT on rdl

Question

how to assign NULL as DEFAULT on rdl

light_wt

Ten Centuries

Points: 1023
More actions
August 4, 2009 at 12:01 am

#207534

hi,
i have a report which pull data based on a store procedure. the procedure has two parameters in which both are assigned as NULL as the default value.
i.e. create proc myproc ( i int =null, j int=null) as ...
the dataset using this proc works fine if i specify a value say, 1000 for i or j.
if i want to specify only one value and leave the other not put in, i will get an error.... "the parameter can not be blank"
if i click properties tab and parameter, I don't know how to assign NULL as the Default Value.
Could someone please help? Thanks.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

You have 3 options:

1. Select the Allow Nulls checkbox on the Parameter setup page. This will put a checkbox next to the parameter that the user will have to uncheck to insert a value. I personally don't like that.

2. Let the parameter accept a blank value and then in the dataset use an expression to convert the blank to null (I believe you use the NOTHING keyword).

3. Let the parameter accept a blank value and in the stored procedure handle the blank value like a null.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

light_wt Ten Centuries Points: 1023 More actions · Answer 2

thanks for your idea.

would you have an example of using NOTHING as keyword for tsql in a store proc?

assigning NOTHING in VB or C# is fine, but, how to assign NOTHING in SSRS or the store proc?

Jack Corbett SSC Guru Points: 184394 More actions · Answer 3

You can't use NOTHING in the stored procedure. You would use NOTHING in an expression when passing the report parameter value to the query parameter. Like:

=IIF(String.IsNullOrEmpty(Parameters!Parameter.Value), Nothing, Parameters!Parameter.Value)

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question