I have a drop-down list that gets populated based on a stored-procedure - that parts works fine.
It gets populated dynamically, based on whatever the "Survey ID" is
But I don't want it to say <Select a Value>
, rather it should say " -- All -- "
, because that is how the report works.
So far I tried to create a dummy-dataset that returns Null
and the label of "all" but it didn't work. Maybe I did that wrong? I used this:
select distinct Name, ID from LK_TargetTypes
where Name like '%pizza'
union select '-- All --', null ;
So LK_TargetTypes
is some irrelevant table. I want to merge that with the other DDL-values.
Here's another picture:
Any tips appreciated thanks.
You are doing it right by returning the -- All --
option as part of the dataset that the query is based on.
You are getting the <Select a Value>
message because, well, you haven't selected a value. What you need to do is assign the default value of -- All --
to this parameter. Open the parameter settings and click on the Default Values tab and enter the default value, which in this case is Null
for -- All --
.
I agree with Chris's answer however I would add that sometimes you want to add defaults to a multi value that are not all and not a single choice either. Generally I set two datasets up , each in a table variable for reporting. Similar to this:
'AvailableValues' Data set could be:
declare @Table Table ( personID int identity, person varchar(8));
insert into @Table values ('Brett'),('John'),('Peter');
select *
from @Table
'DefaultValues' Data set could be:
declare @Table Table ( personID int identity, person varchar(8));
insert into @Table values ('Brett'),('John');
select *
from @Table
If I set up a parameter and bind 'Available' values to a 'get values from a query' and choose my available dataset it has the first set. If I then choose 'Default' values and choose 'get values from a query' and choose the default I can select a subset as the default. The only issue is you must ensure your datasources in the second is a legitimate subset of the first.
You may also choose your Default from set one to get a list of all as well to choose all the values you specified.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With