7

I have a drop-down list that gets populated based on a stored-procedure - that parts works fine.

enter image description here

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:

enter image description here

Any tips appreciated thanks.

Chris Latta
  • 20,316
  • 4
  • 62
  • 70
Caffeinated
  • 11,982
  • 40
  • 122
  • 216

5 Answers5

3

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 --.

Chris Latta
  • 20,316
  • 4
  • 62
  • 70
  • Hi Chris - thanks a lot. Well, I tried this , and for some reason I still see the "Select a Value" parameter option . Strange thing is that when I click "View report" I get an error message saying "Please select a value forthe parameter 'Supplier' " whereas before It'd work fine – Caffeinated Jan 15 '13 at 00:27
  • 1
    Does the parameter allow Null selection? Maybe make the value for the All option be 0 or some other value not returned by the legitimate values and use this as your default value instead. – Chris Latta Jan 15 '13 at 00:58
  • Hi Chris, - yes I checked the "Allow nulll value" box – Caffeinated Jan 15 '13 at 01:15
  • hI Chris - an update - so it works now, I had to alter the stored procedure though and add in `UNION SELECT null, ' -- All -- ' ` , I know there's likely another way.. but since this worked i guess its ok – Caffeinated Jan 15 '13 at 01:34
2

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.

djangojazz
  • 14,131
  • 10
  • 56
  • 94
  • 2
    No problem, defaults are good practice in my book when working with SSRS. Dynamic defaults are even better :) – djangojazz Jan 16 '13 at 00:04
1

For some reason, the only way I got it to work is by altering the stored procedure itself; and add in 

UNION SELECT null, ' -- All -- '

It looks like when you fill a drop-down parameter using a query, it's not easy to add a non-query value/label to the parameter.

I know there's likely another way. but since this worked i guess its ok

Caffeinated
  • 11,982
  • 40
  • 122
  • 216
1

If you select the "Allow multiple values" in the parameter's pop up gui (click on the parameter on the left under the "Parameters" folder), it will give a "select all" option.

Rainhider
  • 806
  • 1
  • 17
  • 31
1

From above you would have figured out to add Select All using Union to your dataset Query.

Now to select "ALL" as default value, rightly mentioned above setting it to specific value Null won't work; instead I used specific query and used same dataset I used to fill my dropdown parameter and it worked.

Note : My dataset was sorted ascending and ___ALL__ came at top with value as Null.

abhiuno
  • 11
  • 1