CODEWITHSUNDEEP
sql-serverreporting-services
Serdia
Serdia

Reputation: 4428

How to populate multi value parameter based on another parameter in SSRS

I have parameter @Coverage and parameter @CoverageLosses. They are NOT parent and child. I want default values for @CoverageLosses populated with values, based on @Coverage. So my main dataset @Coverage looks like this:

SELECT DISTINCT Coverage
FROM            PlazaInsuranceWPDataSet
ORDER BY Coverage

My secondary dataset @CoverageLosses looks like this:

SELECT DISTINCT Coverage AS CoverageLosses
FROM            tblLossesPlazaCommercialAuto
WHERE Coverage IN (@Coverage)
ORDER BY CoverageLosses

In report parameter @CoverageLosses I checked Allow Multiple values then in default-->Specify values I am writing this expression:

=IIF(Parameters!Coverage.Value="Liability","AutoLiabilityCLS" ,nothing)

It works fine for single value. But I want to see multiple values for @CoverageLosses if I choose @Coverage = Liability

How can I write the expression for that? Something like that:

=IIF(Parameters!Coverage.Value="Liability","AutoLiabilityCLS" & "AutoLiabilityCLS" & "AutoBodilyInjuryLiability"  ,nothing)

@CoverageLosses used for another table in my report. It will be hidden. But values from @Coverage will be mapped to values @CoverageLosses. So when user select, lets say value1 from @Coverage then default values in @CoverageLosses should be value1, value2,value3 etc

enter image description here

Upvotes: 0

Views: 861

Answers (1)

Kostya
Kostya

Reputation: 1605

you could create an additional dataset and use it as your source for parameter:

select CoverageLosses from  (
select 'Liability' Coverage ,'AutoLiabilityCLS' CoverageLosses union 
select 'Liability' Coverage ,'AutoLiabilityCLS' CoverageLosses union 
select 'Liability' Coverage ,'AutoBodilyInjuryLiability' CoverageLosses union
select null Coverage ,null CoverageLosses  ) x
where Coverage = @Coverage

Upvotes: 1

Related Questions