How to use dynamic queries in FastReport.Net

2018-08-14

Most users of FastReport.Net build reports receiving data from SQL DBMS, and would like to take advantage of Sql in their reports. Nothing prevents you from using dynamic queries, stored procedures and functions. In this article, we will have a look at how to use dynamic queries when creating a report data source.

This type of query is used to bypass the syntactic restrictions of the SQL language. But such requests can be performed longer due to a suboptimal query plan, and security needs to be built differently. But now it's not about that. Dynamic queries are convenient, and so we want to use them in FastReport.

Let's look at the example. Let’s suppose you want to filter the data before the report is displayed. It seems that everything is pretty simple. But, if you want to change the field on which the data will be filtered? Using a dynamic query, you can add variables to the query body. One of which is the field name for imposing the condition, and the second is the value of this field.

Create a report. Previously, we need to create two report parameters. Add ParamName and ParamValue of the string type. Now create a connection to the database. We connect to the SQL server:

 

At the stage of selecting tables, click the button . It runs the query wizard:

 

Set the name of the future table and click Next.

Enter a dynamic query manually. This query has two parameters: @ Param1 - field name, @ Param2 - field value. Here you can add the third parameter - the operation sign ( =, in, <, >, <, >).

But we will limit ourselves to two. Since the query is represented as a text value, we use the Execute statement to execute. Click the Next button.

In the parameter definition window, create two with the same names as in the SQL query. You must specify Expression. For the first parameter, this is [ParamName], and for the second parameter - [ParamValue]. As you guessed, these are the report parameter names that we created earlier. Click the Finish button.

We got the data source:

 

Drag the fields from the Product table to the band's data.

And now, let's add a dialog form. Drag two parameters from the Data window to it.

Now we can specify the name of the field and its value for filtering the sample. Run the report.

 

Set the parameter values and click Ok.

And we get the records we need. In our case, it is one. Let's try to filter the report on a different field. Run the report again.

 

And the result:

Thus, you can use dynamic queries to help you avoid unnecessary code or report settings. And if you work with large amount of data, you can speed up the time of report generation, by cutting off unnecessary data at the stage of receiving them.

19. September 2024

Behebung der häufigsten Probleme bei der Installation von FastReport VCL

Wir haben eine Liste der häufigsten Probleme bei der Installation von FastReport VCL zusammengestellt und detaillierte Anweisungen zur Behebung der aufgetretenen Probleme vorbereitet.
12. August 2024

Erstellen und Installieren des Postgres-Plugins in FastReport .NET

In diesem Artikel wird beschrieben, wie Sie mit dem FastReport .NET-Plugin für den Berichtsdesigner aus Visual Studio über den NuGet-Server eine Verbindung zur Datenbank herstellen.
8. August 2024

Installation von FastReport .NET und ihren Komponenten unter Windows

Schritt für Schritt Anleitung zur Online- und manuellen Installation über den Registrierungscode FastReport .NET und seine Komponenten in Windows.
Fast Reports
  • 800-985-8986 (Englisch, die USA)
  • +4930568373928 (Deutsch)
  • +55 19 98147-8148 (Portugiesisch)
  • info@fast-report.com
  • 901 N Pitt Str #325 Alexandria VA 22314

© 1998-2024 Fast Reports Inc.