SSRS 2008 Assign default value for parameter

I have an SSRS report tied to a SQL stored procedure.  The stored procedure takes a nullable parameter.  So I created an SSRS report parameter to pass to the stored prccedure.  On the Report Parameter Properties General tab, I have the Allow Null Value checkbox checked.  On the Available Values tab I've selected Get Values from Query, specified the dataset, value and label fields.  And on the Default Values tab I've selected Specify Values and the value (Null) appears in the listbox below.  When I preview the report, the dropdown box for the parameter reads <Select Value> and  if you click on the down arrow all the expected values are there.  If I select a value, it works fine.  If I do NOT select a value, I get a message telling me to select a value.  I thought I set it up to Allow NULL values and specified that NULL was the default value.  What am I doing wrong?  I want NULL to be passed in if nothing is selected.
MariaHaltAsked:
Who is Participating?
 
chrismcCommented:
Null is not in your query list so it's not a valid value.

Make sure your query returns an entry with Null in the Value somewhere and then it will work.

ValentinoV's suggestion is a good way to get the Null into the list, although I would change it slightly to;

Union All
Select Value = Null, Label = '(Null)'

The label should have something meaningful in it and if you want it at the top of the list, surround it with parentheses as I have or some other symbol.

Union All is more efficient than Union on it's own.
Without all it scans the list for duplicate values and removes them - if your list wouldn't ever need duplicates removing you should prevent it from trying to scan for them.

Cheers
Chris
0
 
MariaHaltAuthor Commented:
Hello?
0
 
Tim HumphriesDirectorCommented:
Hi,

What are you  mapping the label vaue of your null value to? I don't think it can cope with displaying null in the dropdown - if you return <null> and <empty string> as the parameter value and label pair I would expect you to be able to select the blank value and for the <null> value associated with that to be passed to the procedure. You could use '<null>' or '<nothing>' or whatever is clearer to the end user for the label but I don't think it can be left as null.

Tim
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
ValentinoVBI ConsultantCommented:
You should see the value (null) in your list of available values.  If the value NULL is not present in the field that you're retrieving as available value, then you can add it using a UNION:

union
select null

(to be added to your SELECT statement that retrieves the available values list)

You may need to add more fields, depending on how many fields you're retrieving in your dataset.
0
 
MariaHaltAuthor Commented:
The users have no idea of what NULL means, I'd rather not list it.  I was hoping that not selecting a value and checking off Allow Nulls and setting the Default Value to NULL would imply that...from what I read above, I guess that is not possible then?
0
 
ValentinoVBI ConsultantCommented:
Why not label it "Default" or "<Default>" or something similar then?  As long as the value is NULL, then the report will work fine and the users will not see NULL.
0
 
Tim HumphriesDirectorCommented:
No, you'll have to have a value in the dropdown that corresponds to the value selected for the default. I would use a label value of '(none)' to indicate a null value in this case.

And the Union method of adding this value to the available values is the way I'd do it, too.

Tim
0
 
MariaHaltAuthor Commented:
That totally worked...it took a while for it to sink into my head!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.