Post reply

how to assign NULL as DEFAULT on rdl

  • 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.

  • August 4, 2009 at 8:40 am

    #1034304

    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

  • August 10, 2009 at 4:01 pm

    #1037119

    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?

  • August 11, 2009 at 8:06 am

    #1037437

    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

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

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