CODEWITHSUNDEEP
reporting-servicesssrs-2008ssrs-2012
whitz11
whitz11

Reputation: 229

Cascading parameters - are you able to skip a parameter?

Thank you to the previous posters, I've had to adapt the report a bit since I posted the initial question, so this is an edit.

I'm trying to create a report using cascading parameters. I've added an 'All' option into the 'LocalAuthority' parameter but get no values back when it moves on to the @ward parameter.
enter image description here

This is how the parameter criteria is set in my 'main data' stored procedure

WHERE [county] = @county

            AND [LocalAuthority] = @LocalAuthority 

                     AND CHARINDEX  (','+Ward+',', ','+@Ward+',') > 0

This is a partial view of my stored procedure for LocalAuthority

 SELECT * FROM 

         (

                  select distinct


                     LocalAuthority,
                     county               
                     from tableA 



                    where [county] like 'essex'


                union all 


                select distinct

                    LocalAuthority,
                    county             
                    from tableA 



                  where county like 'kent' 


                union all

                    select distinct

                       'All' as LocalAuthority,
                       'CountyWide' as county      

                      from tableA 

                    )a

WHERE 

    LocalAuthority = @LocalAuthority

Upvotes: 0

Views: 122

Answers (1)

iamdave
iamdave

Reputation: 12243

If you are using single valued parameters, I would suggest populating your parameters with a dataset and having that dataset simply check for an All in the @City parameter and return one value such as Ignore if All is selected. In your main dataset you then return all LAs when IGNORE is returned using a case statement.

You can use something similar to the query below to specify your @LocalAuthority value list and set the default value to IGNORE which will populate if available and require user selection from the list of LAs if not. This way you do not need user interaction if you are ignoring your LAs:

declare @City nvarchar(50) = 'a';

declare @LAs table (LA nvarchar(50));
insert into @LAs values
 ('Birmingham')
,('Grenwich')
,('Exeter')


select distinct case when @City = 'All'
                    then 'IGNORE'
                    else LA
                    end as LocalAuthorities
from @LAs


set @City = 'All';

select distinct case when @City = 'All'
                    then 'IGNORE'
                    else LA
                    end as LocalAuthorities
from @LAs

Upvotes: 1

Related Questions