I have a dropdown in SSRS reports that is databound. I want this to be nullable\optional. Even if I select allow null it raises alert. I searched on lot of articles on the net which says change SP to return null as one of the item. However i dont think this is the better solution. Is there any other way?

hmmm, not exactly. as you mentioned, you make your dataset to add a row to return null value mark it as answer if it answered your question :)

I did that and it works fine however whenever i make Allow null <Select a Value> is added to dropdown... how can I avoid this?

set default value for parameter then it will not show <select a value>mark it as answer if it answered your question :)

You could make a parameter optional by 1.) Setting the "Allow Null Value" checkbox to TRUE 2.) Setting NULL as the default value for the parameter 3.) In the main query /Dataset add a where clause which accepts NULL value for the parameter. For instance, if you have an Employee dropdown parameter @EmployeeName The Dataset should go something like; Select * From Employee_Information WHERE (EmployeeName = @EmployeeName or @EmployeeName IS NULL) This way the dataset would allow even NULL values making the parameter optional. Hope this helps, Karteek Peri

Hi Kartheek, I did exactly as you said and its working fine. However as I said '<select a value>' appears in dropdown. I am showing blank as null value and set it as default value. So when reoprt loads blank is selected which is fine. In SSRS it doesnt show <select a value> however when i load this report on sharepoint it appears...

You could add a custom String to your dataset using the Union operation and set that as default value for the dropdown. The query for the main dataset should look like ; Select * From Employee_Information WHERE (EmployeeName = @EmployeeName or @EmployeeName = 'Custom String')

No kartheek... You missing my point. I want null to be selected as one of the value however dont wont '<Select a Value>' to appear. I dont understand what is the above query for and where is the UNION clause?

The dataset that you choose to populate the dropdown does not have a value called NULL. And SSRS does not give this value either when you select "Allow Null Value". So the only way that you can add this value to the dropdown is to edit the dataset query and add a string value named "NULL" to it. The query for the dataset that populates your dropdown should look something like; Select Distinct EmployeeNames FROM Employee_Info Union Select 'NULL' FROM Employee_Info This dataset should be used to populate a dropdown with a parameter say @EmployeeName Now for the dataset parameter set the value "NULL" as the default value. Finally the main dataset query should look like; Select * From Employee_Information WHERE (EmployeeName = @EmployeeName or @EmployeeName = 'NULL') The outcome of this approach is that, the value "NULL" would be selected when you render the form by default instead of "Select a Value" Does that make sense?

Hey Kartheek, have you tried this by yourself? Did it work when you uploaded reports in sharepoint? I know it works fine in Report designer....

yes I did...It did work when I uploaded to Sharepoint.

i think there is tiny issue in the query Select * From Employee_Information WHERE (@EmployeeName is null or EmployeeName = @EmployeeName )mark it as answer if it answered your question :)

@Parry...I am not referring to the NULL value but I am referring to a custom string called "NULL".....It could also be a string like "This is a NULL Value".

Peri... This is what I did... In the data set added .. Select Null as ColumnValue, ' ' as ColumnText UNION Select Column as ColumnValue, Column as ColumnText ------------------------------------------------------------------------------------------------------------------------------- Then in Drop down - Display Text = Column & Display Value = ColumnValue Default Value = NULL --------------------------------------------------------------------------------------------------------------------------------- This way when report loads it selects Empty as default value which is what i want. However It also adds '<Select a Value>' in the dropdown which i dont want. Note that this behavior occurs only after loading report to sharepoint and not in the report designer. I dont want NULL as text as i want to send Null value to SP if the field is optional. Hope I am clear with my requirement.

Hi prathalye, Even I am facing the same problem as you. There is no <Select a Value> option in the SSRS 2008 Report designer. But, when the report is deployed to MOSS 2007, it adds '<Select a Value>' in the drop down which i dont want. Can anyone help me on this part??

I have the same problem and I can't seem to get a concise answer to this very simple question anywhere. Thank you all for the insight, but most of you keep missing the point. The problem occurs POST DEPLOYMENT, not in BIDS. I (we) do everything the way we are supposed to, assigning default values, etc.... It looks fine in BIDS . However, when you run the report through Sharepoint from the server, that damn <Select a Value> creeps back again. Does anyone have a better answer than "It's your query that doesn't return any NULL's"? Thank you, Riegardt