Solved

SSRS 2008 Assign default value for parameter

Posted on 2011-02-17
8
1,059 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 18

Accepted Solution

by:
chrismc earned 500 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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

816 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

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now