Archive for the ‘SSRS’ 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
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.

The Problem

In reporting services a new report is created. The report region/Language is set to English(UK). A date parameter is created.
When the report is run the date parameters are showing up in mm/dd/yyyy format 

The Fix

A parameter will take a label and a value field.
Change your query to have a label formatted in the way you like and the value left raw:
Select Right(‘0′ + Cast(Day(dateRange) as Varchar(2)),2) +’-‘+ Right(‘0’ + Cast(Month(dateRange)as Varchar(2)),2) + ‘-‘+ cast(Year(dateRange) as char(4)) as label, dateRange as value
FROM dbo.vwDateRange
Order by dateRange desc 

Where daterange is a smalldatetime 

Set the report parameters to display label as the label and value as value

This assumes a user does not have Content Manager as their SSRS role and SSRS is set up more or less as it comes out of the box.

The Problem

If a user hits subscribe on a report then the chooses email as the delivery option they will see that the To field has been pre-populated with their windows login. They will not have the permissions need to change this setting. This is fine if you are running Exchange as it will resolve the login against the Active Directory and work out the real email address.

If you are not running Exchange it is not so fine.

The fix

You need to make a change to the rsreportserver.config file.

This should be under C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting Services\ReportServer by default – if you installed SQL somewhere else then the first part of this path will be wrong.

Open up this file and look for a value <sendemailtouseralias> under <rsemaildpconfiguration>. If this is set to True, as it is by default, you will get the problem above. If it is set to False the To field will be blank and the user can enter an email address.