Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SSRS 2008 Assign default value for parameter

Posted on 2011-02-17
8
Medium Priority
?
1,076 Views
Last Modified: 2012-05-11
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.
0
Comment
Question by:MariaHalt
  • 3
  • 2
  • 2
  • +1
8 Comments
 

Author Comment

by:MariaHalt
ID: 34920046
Hello?
0
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 34924098
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 34924127
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
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 18

Accepted Solution

by:
chrismc earned 2000 total points
ID: 34924171
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
 

Author Comment

by:MariaHalt
ID: 34925672
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
 
LVL 37

Expert Comment

by:ValentinoV
ID: 34925796
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
 
LVL 15

Expert Comment

by:Tim Humphries
ID: 34925813
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
 

Author Closing Comment

by:MariaHalt
ID: 34973343
That totally worked...it took a while for it to sink into my head!
0

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…

885 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question