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

SELECT EnglishProductName, ListPrice, StandardCost, DealerPrice FROM DimProduct

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

Label	Value
StandardCost	StandardCost
DealerPrice	DealerPrice
ListPrice	ListPrice

2. Add a parameter Operator with the Available Values below

Label	Value
>	>
>=	>=
="="	="="
<=	<=
<	<

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

="SELECT EnglishProductName, ListPrice, StandardCost, DealerPrice FROM DimProduct WHERE " & Parameters!FieldName.Value & Parameters!Operation.Value & CStr(Parameters!Value.Value)

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]

Comments

• Anonymous
  February 04, 2012
  How do you handle other parameters that are part of the CommandText?  I'd like to pass them in with the standard way of @ParamName

• Anonymous
  February 05, 2012
  The comment has been removed

• Anonymous
  February 08, 2012
  The comment has been removed

• Anonymous
  February 09, 2012
  This question is irrelevant to this walkthrough's topic, back to your question, the root cause lie in your Stored Procedure name, please make sure there are double quotes around the stored procedure name. So modify your expression to this =IIF(Parameters!flgA.value=1,"StoredProcedure A","Stored Procedure B"). If you have any other Reporting Services question, I would recommend you post it at SQL Server Reporting Services Forum social.technet.microsoft.com/.../threads Regards

• Anonymous
  March 06, 2012
  What if you need OR statements instead of AND but you need the entire where clause to be generated from an outside source and passed in?

• Anonymous
  March 07, 2012
  @StefanieCulley, if you also want to pass the OR/AND dynamically, you can also leverage Parameter to achieve this.

• Anonymous
  June 15, 2013
  Good one !! clearly explained in a step by step manner..thanks :)

• Anonymous
  July 10, 2014
  Excellent - thanks!

• Anonymous
  July 19, 2014
  it was a perfect tip. thanks a lot

• Anonymous
  March 25, 2015
  Please add instruction how to use more than one field name for the where clause at the same time.

• Anonymous
  May 05, 2015
  Is there a way to use the 'IN()' operator?

• Anonymous
  June 25, 2015
  Great post!  I will use this a lot in my report building!  Thanks!!

• Anonymous
  January 28, 2016
  This is just what I'm looking for, but I must ask, what are the security ramifications.  Should we still be concerned with the same issues that revolve around building a query from user input.  Sure this example uses dropdowns, but with this knowledge it can go further. Thanks