Solved

SSRS 2008 Assign default value for parameter

Posted on 2011-02-17
8
1,062 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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

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.

Question has a verified solution.

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

Suggested Solutions

Introduction In the following article I’ll be discussing and demonstrating several different ways of how images can be put on a report. I’m using SQL Server Reporting Services 2008 R2 CTP, more precisely version 10.50.1352.12, but the methods ex…
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.
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

828 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