Dynamically modifying parameter values in SQL Server Reporting Services

Since most of my work involves queries of 80’s TV stars using SQL Server Reporting Services (SSRS), I needed to dynamically modify a Yes / No parameter so that if the country I was searching for wasn’t “Germany”, than they should not have the option to choose David Hasslehoff as their favorite start. Otherwise, “Yes” isn’t an option, because Hasslehoff can’t get no love in those countries

To review:

Is the country selected Germany? Then the user’s choices for a parameter should be “Yes” or “No”. If not, then the only option should only be “No”.

In this example, Countries is a multi-select parameter with a data type of “Text”:

Multi-select parameter

Multi-select parameter

Now I define the DataSet that will be used to populate my Yes / No values. Normally, I would just create a parameter and manually put Yes and No as values, but because I am dynamically changing the values that are displayed in my parameter, I have to use a DataSet.

Because the @Countries is a multi-select parameter, in my DataSet defintion, I use COALESCE and JOIN to smush the parameters together, then use CHARINDEX to see if there is an instance of “Germany” in the string:

DataSet definition

DataSet definition

In case you want to copy and pasted the SQL code, here it is:

SELECT 1 AS ID, ‘Yes’ AS YesNo
WHERE CHARINDEX(‘Germany’,COALESCE(@Countries,”)) > 0
UNION
SELECT 0 AS ID, ‘No’ AS YesNo

I’m ready to define my Yes / No parameter. Here is the main declaration screen. For “Available Values”, I use the DataSet name I created above, “IncludeCountriesThatLoveDavidHasslehoff”, using the value field of “ID” and the label field of “YesNo”. I do the same for the “Default Values” screen, too.

Parameter definition

Parameter definition

I put the parameter that populates countries before the “IncludeCountriesThatLoveDavidHasslehoff” parameter so that when the first parameter is changed, that change can cascade to the second parameter. Now, whenever the “Countries” parameter is selected or changed, this query will determine if Germany is one of the selected countries. If it is, “Yes” magically appears in the list of possible values for “IsDavidHasslehoffTheFavoriteTVStar” parameter.

Advertisements

,

  1. Leave a comment

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: