BI: Beer Intelligence?

In this article I’ll be describing a method which you can use to filter data in your Reporting Services reports without making any changes to the dataset query.

To get started, I’ll first imagine I’m being interviewed.  (Yeah, I like that.)

Q: You mentioned “without making any changes to the dataset query”.  So you’ll be filtering on the SSRS side then?

A: (Darn, I think she’s onto me.)  Well, yes.

Q: Would you recommend this method?

A: (Yep, she is.) Well, erm, no.  For performance reasons it’s much better to filter on the database server side.  You don’t waste any network bandwidth and database servers are specialized in filtering data.

Q: Then why are you writing an article about filtering on the report side?

A: Well, because sometimes you don’t have another option.  Not all data sources are as flexible as SQL Server, and SSRS supports many different providers.  In some occasions, the DBA that supports exotic data source X gives you a command, similar to a stored procedure call, that returns the data for your report.  And you don’t get any say on how that gets implemented, you’re just told to use that command.  Unfortunately that command returns items A-Z while the report has an item filter.  In those scenarios, it’s interesting if you’d be able to filter on the report side.

Q: Let’s say I would like to filter on the database server side, how would I do that?

A: Just use the WHERE clause in your query.

Alright, enough intro, time to start the article!

To build the report I’ll be using SQL Server 2012, more precisely:

Microsoft SQL Server 2012 (SP1) – 11.0.3000.0 (X64)
Oct 19 2012 13:38:57
Copyright (c) Microsoft Corporation
Developer Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

I’ve made the RDL available for download from my SkyDrive.

The Scenario

You’ve been asked to develop a report that shows a list of products, grouped by category.  The report needs to take a filter on product category into account.  The values on which you need to filter are stored in a configuration table.  You’ve been given a query which for some mystical reason can’t be modified.

Weird enough?  Okay, let’s get that implemented then!

The Report

Let’s first set up a simple report with a dataset and a table.  The table groups the products on category:

In Preview it would look like this:

The dataset used in the report above is called ProductList and uses the following query:

select DPC.EnglishProductCategoryName ProductCategoryName
    , DPS.EnglishProductSubcategoryName ProductSubcategoryName
    , DP.EnglishProductName ProductName
from dbo.DimProduct DP
inner join dbo.DimProductSubcategory DPS
    on DPS.ProductSubcategoryKey = DP.ProductSubcategoryKey
inner join dbo.DimProductCategory DPC
    on DPC.ProductCategoryKey = DPS.ProductCategoryKey
order by DPC.EnglishProductCategoryName, DPS.EnglishProductSubcategoryName
    , DP.EnglishProductName;

Retrieving The Filter Configuration

So now we need to filter this report based on configuration stored in a table.  That means we’ll need an additional dataset, let’s use the following query and call it CategoryFilter:

select 'Category' FilterName, 'Components' FilterValue
union all select 'Category', 'Clothing'

(Sure, I’ve hardcoded the configuration data but for scenario’s sake, imagine it’s coming from a table.)

Here’s what the result looks like:

The dataset contains two fields: the name of the filter and a value on which to filter.  Each value on which we need to filter has its own record.  Having this FilterName field in the table means that it can be re-used for several different filters, if needed.  Your dataset would then use a WHERE FilterName = ‘YourFilter’ clause.

Now, one crucial question remains: how can we use this dataset to filter the data from the other dataset?

Depending on your requirements, there are several possibilities. If you’re sure none of your data regions require the data that will get filtered out, you can use the Filter options on the Dataset.  Alternatively you can use the Filter options on your data region.  In any case, those filters all use the same interface, so implementation-wise it’s all the same.

I’ll implement the filter on the tablix, this gives me the opportunity to show how to get to the properties (dataset is easier).

Okay, so how do we implement a filter on a tablix?

First select the Tablix and right-click the grey square in the top left corner:

Select Tablix Properties and switch to the Filters page.  Click the Add button to add a filter.  As you can see, filters support expressions.  That’s a good thing!

In the Expression combobox, select the field on which you want to filter the data.

The Operator that fits with our requirements should be one that supports multiple values.  In case you’re wondering why, take a good look at our CategoryFilter dataset mentioned earlier.  We want to filter on both Components and Clothing.  So the default equals sign is not really what we’re after, the IN operator will serve much better.

The only remaining empty textbox is Value.  How can we get the values from our filter dataset into this textbox?  In any case, we’ll need to use an expression so click the little fx button next to the Value textbox.

As you’re probably aware, a data region can only be linked to one dataset.  Which means we can’t say:

=Fields!FilterValue.Value

because FilterValue doesn’t exist in the dataset that’s linked to the tablix.

So how can we implement a filter based on data from another dataset?  Once again we’ve got several options.  Let’s look into them!

Option One: Report Parameter

If we’d set up a Report Parameter, we could reference that, right?  Let’s close the properties popup for now (click OK) and create a new report parameter:

Our parameter is called CategoryFilter, its type is Text and it should allow multiple values.  We’ll also set visibility to Internal because it doesn’t need to get exposed in any way.

The parameter’s default values are the values retrieved by the CategoryFilter dataset, so switch to the Default Values page and specify the dataset as shown in the screenshot:

With the parameter created, switch back to the Filters page on the Tablix Properties and enter the following expression:

=Parameters!CategoryFilter.Value

If you’ve created the expression by double-clicking the parameter in the Values box, don’t forget to remove the (0) or your data will get filtered on the first item only.

Click OK and render the report:

It’s working!  Accessories is no longer the first category that gets shown because it’s been filtered out.  The data is now filtered on the configuration as specified through the second dataset.

But… do we really need to set up a report parameter for this?  Well, no!  Let’s investigate option number two.

Option Two: The LookupSet Function

A couple of years ago I wrote an article to explain how you can retrieve data from another dataset through the new lookup functions.  Well, “new” at the time meant SQL Server 2008 R2.  We’re now on 2012 so the functions still exist!

The function that can help us out today is LookupSet.  Open up the Filter properties once again and replace the Value expression with the following:

=LookupSet("Category", Fields!FilterName.Value, Fields!FilterValue.Value, "CategoryFilter")

Close the popup window and preview the report.  If it runs fine, delete the report parameter and run the preview again.

Yes, it still works, nice!  So we don’t need to use a report parameter to filter our data, we can use a fairly simple expression using the LookupSet function!

Option Three: The Split Function

To finalize let’s investigate a variation on the requirement.  In this variation, the filter configuration doesn’t need to come from a table.  It can just be hardcoded in the report.  Can we do that, without using an additional dataset?  Yes we can!

In the Filter properties, change the Value expression to:

="Components,Clothing"

Now render the report:

Hmm, that didn’t work well.  Ow right, the IN operator expects an array of string values and not a comma-separated value string.  Let’s see, how can we get an array out of a character-separated value string?  Actually, I’ve covered this in one of my #tsql2sday posts.  We can use the Split function:

=Split("Components,Clothing", ",")

With that expression specified, let’s render the report a last time:

Woohoo, it works once again!

Conclusion

In this article I’ve demonstrated three different possibilities to implement filtering on multiple values on the report side.  It is not a recommended method but in some cases it can be very useful, especially when you don’t have another choice!

Have fun!

Valentino.