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.
Using Dynamic SQL Query with SSRS
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.
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.
Examples of dynamic SQL code in a Reporting Services report
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.
First solution with a SQL script and an 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")
Second solution 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.