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

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>

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

 

• Proposed as answer by Karteek Peri Saturday, April 03, 2010 4:20 PM

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?

 

 

 

• Proposed as answer by Challen FuModerator Wednesday, April 07, 2010 7:18 AM
• Marked as answer by Challen FuModerator Monday, April 19, 2010 10:30 AM

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 )

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

• Proposed as answer by tal869 Thursday, February 27, 2014 11:45 PM

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

Here's what worked for me, please apply your code to this.....

SELECT
  CONVERT(Varchar(20),CONTRACT_DATA.BUDGET_YEAR,103) AS displayDate
 
FROM
  CONTRACT_DATA
 GROUP BY
  CONTRACT_DATA.BUDGET_YEAR

Union
  Select '' FROM CONTRACT_DATA
ORDER By displayDate

Ordering Makes the '' (Blank) appear at the top of the list.

Make the Parameter allow Blanks.

Set the available values to the Dataset above, and then all you need to do is set the default value of the parameter to "Get values from a query"  and set the dataset field to this dataset also. Works for me post development. Give it a try.

CAUTION: There is one more thing you need to make sure while using Karteek's solution: Since you have added the filtering in the WHERE clause of the query of the main dataset as "WHERE (EmployeeName = @EmployeeName or @EmployeeName = 'NULL')" you would not add a filter in the main dataset for the @EmployeeName parameter; otherwise, that filter would take precedence and the results would be returned based on that filter.

Thanks..Nam

Excellent answer, Karteek. I should add that you cannot both provide a multi-select parameter and the null option. This seems to me to be a weakness of the otherwise superb SSRS parameter design.

That was exactly what I needed thanks Prathalye.

Thank you all for this thread. My problem is this, but with Fetchxml instead of sql. Any suggestions? Thank you.

Hi,

If you haven't solve the problem yet, there is one more chanse to solve this problem.

i also had same problem. i opened the managemen url on internet explorer and found the report.

in paramaters section i saw that "Has default" and Null checktbox are not checked. after make them checked problem solved.

i understood that build and deploy is not completly effective to change some things on report server.

i hope this works

Thanks Guven, that was the solution I needed.  The "Has Default" option was not getting deployed properly, so everything looked fine in BIDS but not on the SSRS server.