whitz11 whitz11 - 1 month ago 16
SQL Question

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

@DeliveryGroup 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

@DeliveryGroup 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

Answer

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

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