locked
passing Null to an SP RRS feed

  • Question

  • User-963208184 posted

    Hi All,

    I have a report that uses an underlying SP the Sp parameters are declared:

    @StartDate smalldatetime,    
     @EndDate smalldatetime,    
     @ClientID bapsID,     
     @UnitID bapsID=NULL,    
     @GradeID char(4)=NULL,    
     @SkillID char(4)=NULL,  
     @ReasonID char(4)=NULL   

    In the report designer itself If we take UnitID as an example. I have set allow blank and allow null to true and also in the parameter for UnitID I have set the default to be null. When I run the report. UnitId is represented with a dropdown, If i dont select anything I receive an error stating that I must select something for UnitId. It does this for all of them.

    Friday, April 12, 2013 11:21 AM

Answers

  • User-917364509 posted

    The SP that populates the dropdownlist must have a NULL value meaning the select statement for this would look something like:

    Select UnitId FROM Units
    Union
    Select 'NULL' FROM Units

    SSRS does not provide a NULL value even if you select the "Allow NULL value" option.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 16, 2013 5:35 AM

All replies

  • User477186420 posted

    Hi,

    Which report you are using ex SDLC, Cristal report or etc.? and share your code to get more idea

    let me know if any query

    Tuesday, April 16, 2013 2:22 AM
  • User-963208184 posted

    It is an SSRS Report built in Business Inteligent Studio 2008.

    It is a Parameter field that maps to a value on the main SP as mentioned. On the Report Parameter Properties tab, on the General section, I have ticked allow null value and allow blank value.  This column get's populated from another sp that causes this to show as a drop down on the report parameters section when you attempt to run the report. The first option int the dropdown is <select value>. All looks fine to this point. THen when you press the button to run the report, that's when I get the error that something must be selected ?????

    Tuesday, April 16, 2013 5:24 AM
  • User-917364509 posted

    The SP that populates the dropdownlist must have a NULL value meaning the select statement for this would look something like:

    Select UnitId FROM Units
    Union
    Select 'NULL' FROM Units

    SSRS does not provide a NULL value even if you select the "Allow NULL value" option.

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Tuesday, April 16, 2013 5:35 AM