Adding dymanic filters in reports with SSRS

SSRS report paramters doesn't support runtime selection of operations by default. For example: we want to let users filter data by checking any criteria that equals, not equals or contains any specified value. We can achieve this by using ssrs expressions. Below is outlined a step-by-step guide to do this.

Following is a snapshot of a simple cube to be used in this example:

The cube contains orders data including two measures: quantity of products and dollars of orders. It has four dimensions, but for this example lets focus on the Product and Quality dimensions only.

The aim is to create a report to view total dollars received from the orders per product quality for specific product categories.

Before starting we need to create the reporting project using SSRS and then add the data cube as a shared datasource.

I've also added a dataset using query designer. It includes Dollars as measures and Product Name, Category, Quality to group the measure values (dollars).

Below is the simple matrix based report that uses Product Name as row group and Quality as column group:

Now lets proceed to adding the category filter.

Step 1: Create filter parameters

Before adding the parameter I've created another dataset for pulling all product categories. And then added a parameter with following settings:

• Allow multiple value is true
• Available values from new parameter dataset

I've also added another parameter for selecting the filter operation:

Step 2: Add dataset parameters

I've added the following parameters at the Paramters section in Dataset properies dialog for OrderDataSet. Category and Operator values will be taken from user input. FilterClause parameter value will be defined by an expression in next step.

Step 3: Construct conditional filter expression

The expression used for constructing filter expression is given below:

Here, I am constructing the filter clause that will be used in MDX query; therefore the member name must exactly match the cube dimension member name.

Step 4: Use conditional filter in dataset query

Now, the final thing is to modify the MDX query so that it uses this filter clause. 

To do so: open the OrderDataSet properties dialog and then click on the expression button instead of query designer. Replace the "From [OrdersDataCube]" part with the filtercaluse so that the final expression becomes as below:

Now, the filter should work according to user selection.