logo
small logo
  • Products
  • Buy
  • Support
  • About
  • Customer panel Support
    • en
    • de
    • JP
    • ZH
  • Home
  • /
  • Articles
  • /
  • How to use dynamic queries in FastReport .NET
  • How to select the top values in a matrix

    April 22, 2021

    The article is relevant until version 2022.1. FastReport .NET has a great tool for displaying data

    read more
  • How to connect to SQLCe

    November 11, 2019

    Microsoft SQL Server Compact Edition is a simple local relational database that doesn't require installation,

    read more
  • How to connect to the Sybase SQL Anywhere database

    February 29, 2020

    Sybase SQL Anywhere database has a number of very useful features that make it very

    read more
  • Reporting with PostgreSQL in a .NET 5 application for Debian 10

    May 25, 2022

    Many need a solution that will generate reports for Linux systems, and support working

    read more
  • How to use SQL queries when creating an internal report data source in FastReport .NET report designer

    September 2, 2019

    When creating a SQL database connection, you can specify a query to select filtered or

    read more

How to use dynamic queries in FastReport .NET

August 14, 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 buy
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
  • Buy
  • Download
  • Documentation
  • Testimonials
  • How to uninstall
  • FAQ
  • Tutorial Video
  • Forum
  • Support SLA
  • Articles
  • Our News
  • Press about us
  • Resellers
  • Extended licensing
  • Contact us

© 1998-2023 by Fast Reports Inc.

  • Privacy Policy

Trustpilot