Walkthrough: Control Dataset Query Dynamically based on SSRS Report Parameters

Generally, using a parameterized query can filter data for a report dataset at the source. When include query parameters in a query, Reporting Services automatically creates report parameters that are connected to the query parameters. This enables users to select the data based on a particular criterion, such as values in a data field need to be greater than a specific value.

However, sometimes the criterion of the filter also depends on parameters dynamically. For example, users can choose the comparison operator like > (greater than) or < (less than) as report parameters to build the filter. This article will walk you through the processes of using a dynamic query based on parameters in a report.

The sample in this article uses the sample AdventureWorksDW2008 database. The report displays the Product list containing Product Name, Standard Cost, Dealer Price and List Price columns.

Add Data Source and Dataset

1. Create a new Report Server project and then add a blank report.

2. Add a new Data Source DataSource1 to connect the AdventureWorksDW2008 database with the connection string like
Data Source=.; Initial Catalog=AdventureWorksDW2008

3. Add a new Dataset Dataset1. Specify the Data source to DataSource1 and type in the following query

Add a Table

1. Add a table to the report body.

2. Add each data field in Dataset1 to the detail row of the table. It looks like

Add Parameters

1. Add a parameter FieldName with the Available Values below

2. Add a parameter Operator with the Available Values below

Please be aware that we need to specify an expression for the equal to item rather than only use the equal sign.

3. Add a parameter Value and set its Data type to Float.

Modify the Dataset Query

1. Right click DataSet1 in the Report Data pane and select Dataset Properties.

2. In the Dataset Properties window, click the fx button beside the Query text area.

3. Replace the query text to the expression below

4. Preview the report. Then, the Field Name, Operator and Value can be specified as parameters to filter the query data dynamically.

By using expression, the dataset query can be built dynamically based on various requirements.

[Download sample]