Solved

SSRS 2008 Assign default value for parameter

Posted on 2011-02-17
8
1,057 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

Written by Valentino Vranken. Introduction: The first step of creating a SQL Server Reporting Services (SSRS) report involves setting up a connection to the data source and programming a dataset to retrieve data from that data source.  The data…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
You have products, that come in variants and want to set different prices for them? Watch this micro tutorial that describes how to configure prices for Magento super attributes. Assigning simple products to configurable: We assigned simple products…

762 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

20 Experts available now in Live!

Get 1:1 Help Now