CODEWITHSUNDEEP
sqlsql-serverreporting-services
whitz11
whitz11

Reputation: 229

Cascading Parameters

I have looked at various ways of doing this but cant seem to implement it correctly.

I have a 3 parameter report in SSRS

@County @LocalAuthority @Ward

I want these parameters cascading. So when @county is chosen @LocalAuthority only displays local authorities within that county. And when local authorities are chosen only the wards in those authorities appear.

the County to Local Authority is working but Local Authority to Ward isn't. The correct wards are showing in the drop down parameter but they are not filtering by ward in the actual report.

I am using stored procedures. This is my main dataset

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

   SELECT

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

FROM table1

WHERE   [County] = @County AND [LocalAuthority] = @LocalAuthority and 

[Ward] in @Ward

2nd dataset for local authority

    @County varchar (5)       

         SELECT DISTINCT

                LocalAuthority,
        county              

              FROM table1

  WHERE [County] = @County

and a final dataset for Ward

         @LocalAuthority (max)

        SELECT DISTINCT


        Ward,
        LocalAuthority 


              FROM table1

       WHERE [LocalAuthority] = @LocalAuthority

Many Thanks

Upvotes: 1

Views: 145

Answers (1)

Jayvee
Jayvee

Reputation: 10873

Try this instead of ward in @ward if you wanted to get the dataset via stored procedure:

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

Upvotes: 2

Related Questions