Dependent parameters in Reporting Services

Hold onto your butts (a little Samuel L. Jackson from Jurassic Park lingo) because this will be the best post I’ve ever done on a December 27th. Yes, it’s that great.

I was creating a report through SSRS that required the Yes/No parameters of one parameter, StabSelfInEyeball, to be dependent on another. Specifically, if parameter SonnyAndCher was Yes (and our Yes value had an ID of 1, No being 2), then the dependent parameter should only have No – the Yes would disappear completely. Because without Sonny, I just don’t want to hear Cher.

So I created a DataSet that binds to the StabSelfInEyeball parameter is  populated by this query:

SELECT 1 AS YesNoId
,’Yes’ AS YesNoType
WHERE @SonnyAndCher <> 1
UNION
SELECT 2 AS YesNoId
,’No’ AS YesNoType
ORDER BY YesNoType

The first Yes/No parameter, SonnyAndCher, is populated with a similar query, just without the WHERE clause:

SELECT 1 AS YesNoId
,’Yes’ AS YesNoType
UNION
SELECT 2 AS YesNoId
,’No’ AS YesNoType
ORDER BY YesNoType

So how this works is that since the second parameter, StabSelfInEyeball, is dependent on the first, SonnyAndCher , whenever SonnyAndCher is changed, StabSelfInEyeball is refreshed. So, if the user choose ‘No’ for the SonnyAndCher parameter, then I can choose to either stay myself in the eye or not, it’s up to me. But if SonnyAndCher is Yes, the only choice for the StabSelfInEyeball  parameter is “No”. I will not stab myself in the eye – why would I do that? It’s Sonny AND Cher – can’t beat that.

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: