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