SSRS dropdown should allow null value

Question

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?

Answer 1

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 :)

Answer 2

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?

Answer 3

set default value for parameter then it will not show <select a value>

---

mark it as answer if it answered your question :)

Answer 4

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

 

Answer 5

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

Answer 6

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')

Answer 7

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?

Answer 8

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?

 

 

 

Answer 9

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

Answer 10

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

Answer 11

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 :)

Answer 12

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

Answer 13

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.

Answer 14

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

Answer 15

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

Answer 16

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.