Archive for the ‘Parameters’ Category

Using Parameters in SSRS

Posted: December 1, 2011 in BI, Parameters, SSRS

Sometimes you’ll write a report and you’ll want to use parameter drop-downs. Actually it’s not sometimes, it most of the time. Most people requesting reports ask for some form of parameter, either dates or a data driven value. This is all easy stuff, all you need to do is create a new dataset and associate the parameter with that. You then change the main dataset to filter data based on the parameter value. But how do you deal with the All records value?

This is how I do it.
In this example I am going to draw data from a table called chess players – because I am chess geek.
This is a basic parameter dataset. I’m bringing back a list of world chess champions – and I want a distinct set – no duplicates. In this fictitious table world champions includes ex-champs.
Select Distinct ChessPlayerName as value, ChessPlayerName as label
From ChessPlayers
Where WorldChampion = ‘True’;
My main dataset will now look like this (It’s a list of tournaments where the winner was a world champion:

Select ChessTorunaments, TourneyYear, Victor
From ChessMatches
Where Victor = @WorldChampion;

That example is fine if you always want to filter, but I don’t. I want to be able to list all tournaments regardless of the winner. And so, I will change my query.

SELECT null as Value, ‘All’ as label
Union
Select Distinct ChessPlayerName as value, ChessPlayerName as label
From ChessPlayers
Where WorldChampion = ‘True’
Order by value;

Order by value will ensure that the null value appears at the top of the list.
I will now change my parameter values to allow nulls.
Now I need to change my main dataset.
Select ChessTorunaments, TourneyYear, Victor
From ChessMatches
Where Victor = IsNull(@WorldChampion, Victor);

So, if the @WorldChampion parameter is null it will be replaced by the Victor column value. As the Victor column value will always match the victor column values we will always get the full record set, won’t we? Hey? Well, not always. Victor = Victor will only work when Victor is not null. If the column Victor allows for nulls, and has nulls in it, it will not match. Why? Because null means unknown – so null = null will return false because you can’t compare two unknown values. What you’ll need to do if Victor (or your column) is nullable is use IsNull again:
Where IsNull(Victor,0) = IsNull(@WorldChampion, IsNull(Victor,0));
Now you won’t get null = null you’ll get 0=0 which is true.