Dynamic SQL query with SSRS

How to build a dynamic query in a SSRS report that use an input parameter in a where clause for example?

How do I create a dynamic SQL query with SSRS, in datasets ? With Reporting Services, the fields used in a table object for example are linked to the database. This is done through a dataset which is itself the result of an SQL query. This dataset is linked to a data source. In fact, this data source contains a connection. For example to a database or an OLAP cube used as a source. The restitution of the data makes it possible to create static reports with fixed queries. Or dynamic reports with dynamic queries.

Use filters to make the most of SQL Server queries and display a relevant selection of data. In other words, without filters all data is displayed. Or the data is restricted by, for example, the current day, month or quarter.

Examples of dynamic T-SQL code in SSRS report

Filters allow the user to select a particular year, product or customer. It is recommended to implement as many dynamic functions as possible in SSRS reports. In addition to this, the use of several parameters together allows for a more refined selection of data.

Reporting Services is the Reporting and Dashboarding tool of the Microsoft BI suite. It is fully Responsive since the SQL Server 2016 version. This means that the reports can be adapted to different screen formats. This means: desktop, laptop, tablets and smartphones. There are several approaches to handling filters, here are two simple options.

Use SQL script and a SSRS expression

The easiest solution to maintain is the following, with a CITY field to select the city. First, the system evaluates the PARAMETERS!PARAMETER2.VALUE parameter against a first visible parameter. Parameter selected from a list of choices by the user, when the report is run.

SELECT *
FROM TABLE1
WHERE CITY = PARAMETERS!PARAMETER2.VALUE

To do this, in the Expression properties of parameter PARAMETER2, insert a code with a function, like this one:

=IIF(Parameters!Test.value = "Value", "CONDITION1", "CONDITION2")

Example with conditions in the SSRS query

Here is the second solution which is to write the two parameters in the query expression. It is less elegant and more complex to develop with several filters:

IF PARAMETERS!PARAMETER.VALUE = AVALUE
SELECT * FROM TABLE1 WHERE FIELDS = CONDITION1
ELSE
SELECT * FROM TABLE1 WHERE FIELDS = CONDITION2

It is therefore preferable to use the first solution. This article presents how to create a dynamic SQL query in an SSRS dataset to manage filters. To go further, here is how to manage the month name with SQL Server code.