logo
small logo
  • Products
  • Comprar
  • Suporte
  • Articles
  • Customer panel Suporte
    • en
    • pt
    • es
    • de
    • pl
    • JP
    • ZH
  • Home
  • /
  • Articles
  • /
  • How to use dynamic queries in FastReport.Net
  • MS Word format vs. Open Document Text. Which is better?

    19 de outubro de 2020

    Speaking of text editors, we immediately present coryphaeus as Microsoft Word, which for many years

    read more
  • Want to generate tables for a user - Excel or OpenOffice Calc?

    8 de outubro de 2020

    Tables. For centuries, they have been used to present similar data for record-keeping, counting amounts,

    read more
  • Toilet paper printing

    30 de março de 2020

    Gentlemen, jokes aside! Today we are talking about toilet paper. This essential hygiene product was

    read more
  • Como proteger seu PDF?

    30 de novembro de 2020

    No dia da proteção de dados, decidimos preparar um artigo sobre a proteção de documentos

    read more
  • Copiando as bandas e todo conteúdo de um relatório para outro

    31 de março de 2021

    Algumas vezes é necessário usar partes de um relatório em outro. Em casos simples, você

    read more

How to use dynamic queries in FastReport.Net

14 de agosto de 2018

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.

about product download comprar
avatar
Dmitriy Fedyashov
Technical Writer
Fast Reports Team: Dmitriy Fedyashov - Technical Writer at Fast Reports
.NET FastReport SQL

Add comment
logo
  • 800-985-8986 (English, US)
  • +4930568373928 (German)
  • +55 19 98147-8148 (Portuguese)
  • info@fast-report.com
  • 901 N Pitt Str #325 Alexandria VA 22314
  • Comprar
  • Download
  • Documentação
  • Depoimentos
  • Como desinstalar nossos produtos
  • Ticket system
  • FAQ
  • Tutorial Video
  • Forum
  • Articles
  • Our News
  • Informação sobre nós
  • Parceiros
  • Contatos

© 1998-2022 by Fast Reports Inc.

  • Privacidade