CODEWITHSUNDEEP
sqlreporting-services
whitz11
whitz11

Reputation: 229

Cascading parameter selecting multiple options

I've looked at various sources at trying to do this but I'm finding it very confusing.

I have a 3 parameter report in SSRS - they are cascading.

@County @LocalAuthority @Ward

Its working fine when selecting a single county or Local Authority, the problem occurs when i want to select one or more counties....the Local Authority parameter just displays a blank drop down.

I am using stored procedures. This is my main dataset

@County varchar (max),
@LocalAuthority varchar (max),
@Ward varchar (max)

 SELECT

     [DateTimeOfCall] 
     ,HourOfDay 
    ,[ConcatAddress] 
    ,[LocalAuthority]
    ,[Ward] 
    ,[County] 
    ,[PropertyType]

FROM table1

WHERE   [County] in (@County) AND [LocalAuthority]in (@LocalAuthority) and 

  CHARINDEX(','+ward+',', ','+@ward+',') > 0

2nd dataset for local authority

     @County varchar (max)       

     SELECT DISTINCT

    LocalAuthority,
    county              

    FROM table1

  WHERE [County] in (@County)

and a final dataset for Ward

     @LocalAuthority (max)

    SELECT DISTINCT


    Ward,
    LocalAuthority 


          FROM table1

   WHERE [LocalAuthority] in (@LocalAuthority)

Upvotes: 0

Views: 215

Answers (1)

grafgenerator
grafgenerator

Reputation: 818

The way your SQL-code written working when you writing it inside dataset, then parameter values just substitute placeholders that you wrote. When you use SP and you have parameters with multiple values, you should join values into one string by yourself and then parse (split) in SQL-code. Open dataset properties, go to Parameters tab and edit Parameter values, instead of passing your @Country, you should make it Expression =Join(Parameters!Country.Value, ",") Then in your SP's code, split input string by comma, there are many sites where you can find how to do this, f.e. there. Of course, you need to do that for all of your multivalued parameters.

Upvotes: 1

Related Questions