How to filter out parameters value in SSRS

Question

I currently have a SSRS report which is giving me Telephony Data based on two sites, Cape Town and Dallas.

At present I have a parameter called Site and a parameter called Data_Label. It works absolutely how I wish by selecting Cape Town and the relevant Data_Label and the same for Dallas. This then shows all the data I need.

The issue I have is that there is a very long list of Data_Label's, half are attached to the Cape Town site and the other half to the Dallas site. What I would like to have happen, is when I select Cape Town as my site, half of the Data_Label's appear and then when I select Dallas the others will then show.

This is my current query in the Dataset:

WHERE ([LB ODS].Telephony_LifestyleBenefits_VIEW.Site IN (@Site)) AND ([LB ODS].DataLookup.Data_Label IN (@DataLabel))

So each Data_Label will be linked to it's relevant site in the main database but I just need a filter in my parameter so that it doesn't show the long list

Thanks Dan

Answer 1

You can cascade Data_Label parameter from Site parameter value.

If you populate Data_Label parameter via a dataset you can filter the available values using the Site parameter, something like this

select Data_Label
from Table1
where Site IN (@Site) 

When you run the report the dataset that populates the Data_Label parameter will run the above query and return the Data_Label values related to the value selected for the Site parameter.

Also if you populate the parameter manually you can hardcode the values in a CTE what results in something like this:

WITH DLValues AS (

select 'Data_Label1' [Data_Label], 'Dallas' [RelatedSite]
union all
select 'Data_Label2' [Data_Label], 'Dallas' [RelatedSite]
union all
select 'Data_Label3' [Data_Label], 'Cape Town' [RelatedSite]
union all
select 'Data_Label4' [Data_Label], 'Cape Town' [RelatedSite]

) 

select Data_Label
from DLValues
where RelatedSite IN (@Site)

Let me know if this helps you.