Answered by:
passing Null to an SP

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 UnitsSSRS 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 UnitsSSRS 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