SSRS tablix filter – allow multiple values and NULL

Sometimes it is preferable to filter a tablix on the SSRS side, instead of within the stored procedure.  Usually I do it to avoid using sp_executesql.  In these cases, I’d rather filter the data post-query than make my query harder to write and read.  If it’s low volume, it’s a good opportunity to use filters.

But to accomplish this, it is necessary to do advanced things like filter parameters that allow multiple selections, and to allow NULLs to pass through.

To allow multi-value parameters to be filtered, I did not find the way to use integer values.  However, I was able to filter by characters and labels.   An alphanumeric DataSet field is IN a multi-value parameter label.  It looks something like the following:

BlogMultiParameter

In order to allow a NULL value, it is a bit more tricky

1. Edit the tablix data source (the view or stored procedures) to change a NULL to a unique value.  Example:
SELECT isnull( [State].StateCode, ‘None’) as StateCode,

2. Edit the parameter’s data source (DataSet) to provide ‘None’ as a value.  Example:
SELECT 0 as StateId,  ‘None’ as StateCode
UNION ALL
SELECT StateId, StateCode
FROM [State]

These two values absolutely must be the same.  For example, they are both ‘None’ in my two code examples.

As a side, I’d rather say ‘None’ than ‘NULL’ for two good reasons
1. Business users sometimes don’t know what NULL means
2. The string ‘NULL’ is not the same thing as a value of NULL.  It gets confusing, so it’s better to avoid that.

The parameter with a None value will look like this:

BlogNoneParameter

To allow NULLS, the Tablix filter still says the same as it was for the multi-value parameter.

Advertisements
This entry was posted in Business Intelligence and tagged . Bookmark the permalink.

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