CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-2008-r2
Saifuddin
Saifuddin

Reputation: 99

Add "ALL" to drop down which is fetching values from query - SSRS 2008 r2

I have report, which has drop down to select warehouse code and it show stock position at that selected warehouse, these warehouse codes are populated through query at available values part, i would like to add in the same list as ALL, how to achieve this?

Current view at drop down
WH 1
WH 2
WH 3

Desired view at drop down
ALL
WH 1
WH 2
WH 3

please provide your inputs

Upvotes: 0

Views: 361

Answers (2)

Neil Norris
Neil Norris

Reputation: 411

Parameter Dataset:

SELECT NULL AS Value, '<ALL>' as Label
union all
SELECT DISTINCT WAREHOUSE AS Value, WAREHOUSE as Label

this will create a dataset with a distinct list of warehouses in both the value and label column - there is a reason for this which I will explain further on.

Now in your dataset change the where clause to:

WHERE WAREHOUSE=CASE WHEN @wh IS NULL THEN WAREHOUSE ELSE @wh END

By creating a NULL value with its own label you are creating an extra row in the dataset and by passing NULL in (using the where clause above) you are effectively selecting the entire table.

Upvotes: 0

Aldrin
Aldrin

Reputation: 766

You should create a specific dataset just for this parameter. Then from that dataset Just Add a union query

SELECT 'ALL' as FieldName

UNION ALL

*Your Dataset Query

Then set your parameter to Get value from a query then select the dataset you've just created.

Upvotes: 2

Related Questions