logo
small logo
  • Products
  • Buy
  • Support
  • About
  • Customer panel Support
    • en
    • de
    • JP
    • ZH
  • Home
  • /
  • Articles
  • /
  • How to use SQL queries when creating an internal report data source in FastReport .NET report designer
  • 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 use custom libraries with functions in Designer

    September 6, 2018

    Despite the fact that so-called arsenal of built-in functions in the report designer is by

    read more
  • Plugin for importing data from .XLSX to FastReport .NET

    March 16, 2022

    With the release of FastReport .NET 2022.2, we added a plug-in to connect .XLSX

    read more
  • Connection plugin for Cassandra from FastReport .NET

    August 18, 2022

    We are pleased to present you a new plug-in for configuring а connection to

    read more
  • Comparison of SSRS and FastReport .NET part 1

    November 18, 2017

    Electronic and printed reporting is used in many areas of our life. Can you imagine

    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 combined data. The standard tools of the data source creation wizard also allow you to include parameters in the SQL query. In this case, the designation of the parameter will depend on the type of DBMS to which the connection is made. For example, for MS SQL parameter in the query has the prefix @. And for the Access database instead of the parameter the symbol "?" is indicated. If there are several parameters, they will be inserted into the request according to the order in the list of parameters.

Suppose we have created a connection string to the MS SQL database. The next step is the selection of tables:

 

At this stage, we can create a SQL query using the Add SQL query ... button. The result of the future query will be saved in the table. It is required to set the name of the query object:

 

 Then, write the request text:

 

Of course, here you can use the visual query designer, but now we don’t need it, the purpose of the demonstration is a query.

We specified a parameter in the request condition. In the next step, you need to create a parameter with the same name.

 

Since we are simply demonstrating how to work with parameters, we simply set the default value:

Next, select the desired columns.

That's all. The table is ready.

Remember, it is advisable to write queries according to all the rules of SQL, with an indication of the scheme, and possibly with the names in quotes. Here is an example of a valid postgresql database query:

1
SELECT * FROM public."Users" WHERE "Users"."Name"= @Name

 But, in addition to the parameterized query, we can also use dynamic queries. These are requests that we form in a text form, and then we execute them. Such queries allow using report variables to substitute, for example, the name of a table or parameter, or part of a ‘where’ clause – whatever is needed. It looks like this:

 

But that's not all. You can also use stored functions and stored procedures in a query. Here is an example of using the function:

 

Parameters in functions are passed in brackets, separated by commas.

An example of using a stored procedure:

Parameters in the stored procedure are followed by a procedure name separated by commas.

In this way, you can expand the data generation capabilities in order not to do this within the report.

about product buy
avatar
Dmitriy Fedyashov
Technical Writer
Fast Reports Team: Dmitriy Fedyashov - Technical Writer at Fast Reports
.NET FastReport Designer 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