ONLINE HELP
 WINDEVWEBDEV AND WINDEV MOBILE

Help / Editors / Report editor / Data sources
  • Overview
  • Creating a report based on an SQL query with data accessed via a connection
  • Printing a report based on an SQL query via a connection
  • WINDEV and WEBDEV example
WINDEV
WindowsLinuxUniversal Windows 10 AppJavaReports and QueriesUser code (UMC)
WEBDEV
WindowsLinuxPHPWEBDEV - Browser code
WINDEV Mobile
AndroidAndroid Widget iPhone/iPadIOS WidgetApple WatchMac CatalystUniversal Windows 10 App
Others
Stored procedures
Report based on an SQL query with data accessed via a connection (ODBC, etc.)
HFSQL ClassicHFSQL Client/ServerNot available with these kinds of connection
Warning
From version 19, this type of report is not available in creation anymore. The existing reports continue to operate and they can be modified.
Overview
A report based on an SQL query with data accessed via a connection can print records from a database accessible via a specific ODBC driver or via a Native Connector. This query is defined when creating the report.
Characteristics of a report based on an SQL query via a connection:
  • the connection parameters (name of the connection, name of the user, ...). These parameters allows you to access the records that will be printed in the report.
    For an ODBC connection, the parameters are defined in the ODBC administrator (in the control panel of Windows).
    For a connection via the Native Connector, the parameters are defined when configuring the database.
  • the SQL code of the query associated with the report. This code must correspond to a Select query ("SELECT"). This query is used to specify the items that will be printed in the report.
Caution: The SQL code must be recognized by the database accessed.
The creation of a report based on an SQL query via a connection is performed by the wizard for report creation.
Once the report based on an SQL query is created, you can:
  • modify the SQL code of query ("Data" tab in the report description).
  • modify the parameters of the connection ("Data" tab of the report description).
  • add or delete Item controls linked to the SQL query associated with the report.
Creating a report based on an SQL query with data accessed via a connection
To create a report based on an SQL query with data accessed via a connection:
  1. Click in the quick access buttons.
    • The new element window appears: click "Report" then "Report".
    • The report creation wizard starts.
  2. Choose the layout of your report (table, form, label, ...). For more details, see The different types of reports.
  3. Select the data source of the report ("From a SQL query by connection").
  4. Select the type of connection to use (ODBC or Native Access)
  5. Specify the connection parameters:
    • the connection name.
    • the username.
    • the user password.
    • the name of the database accessed (if there is more than one).
  6. Enter the SQL code of the query associated with the report.
    Caution: The SQL code must be recognized by the database accessed.
  7. To group the records, you have the ability to create breaks on the sort items. To create breaks, answer "Yes" to the question "Do you want to add breaks into the report".
    The next step is used to select the sort items corresponding to the breaks. For more details on breaks, see Breaks in a report.
  8. For each query item displayed in the report:
    • type the corresponding caption. This caption will be displayed:
      • before the item. For example:
      • in the column header (for the Table reports). For example:
        This caption can be modified in the report editor.
    • select the block where the control associated with the item will be printed. The positions of different controls can be modified in the report editor. For more details on the different blocks and their position in a report, see Report blocks.
  9. Modify (if necessary) the display order of controls in the report with the arrow buttons found on the right of table.
  10. Depending on the type of report currently created, indicate the specific options.
    Type of reportSpecific options
    Report based on a formForm image, printing the form image, ...
    Options presented in detail in Report based on a form
    Label reportFormat of labels, number of identical copies, ...
    Options presented in detail in Label report
  11. Specify the format of sheet on which the report will be printed. The report is printed in A4 format by default.
  12. Select the skin template of the report if necessary.
  13. Type the name and title of the report (name of ".WDE" file corresponding to the report). This name will identify the report in your programs.
  14. Validate the report creation.
  15. The report editor automatically proposes to change the report format if the following conditions are fulfilled:
    • the report currently created includes a table.
    • the report format does not allow to display all table columns.
  16. Specify (if necessary) the mode for reducing the report size:
    • Print the report on several pages. In this case, the end user will have to group the pages. For more details, see Multi-page print.
    • Use the landscape mode.
    • Reduce the report size. Caution: according to the reduction percentage, the printed report may become unreadable.
  17. The report currently created is displayed in the report editor.
Printing a report based on an SQL query via a connection
To print a report based on an SQL query via a connection, you must:
  1. Configure the report print destination:
  2. Specify the name of the report to print with iPrintReport.
Remarks:
In Reports & Queries, you can print a report:
  • by starting a print job ().
  • by testing the report ().
Therefore, no programming is required to print the report.
WINDEVWEBDEV - Server codeReports and QueriesWindows

WINDEV and WEBDEV example

The "RPT_Customer" report is based on an SQL query performed on the Access "Customer" database. This database is accessed via the ODBC driver of Access. By default, this report is used to print all the characteristics of the customers.
The user can:
  • click the "BTN_CustomerCharacteristics" button to print all customer characteristics. The SQL code of the query will not be modified.
  • click the "BTN_CustomerNames" button to print the first name and last name of customers. The SQL code of the query will be modified.
To find out whether the SQL code of the query must be modified or not, a parameter is passed to the report:
  • "True": no modification of the SQL code.
  • "False": modification of the SQL code.
In this example:
  • the click code of the "BTN_CustomerCharacteristics" button is as follows:
    • WINDEV WINDEV code:
      // Open the preview window
      iPreview()
      // Print the report with parameters
      iPrintReport(RPT_Customer, "True")
    • WEBDEV - Server code WEBDEV code:
      // Generates a unique PDF file name
      UniqueFilename is string = fWebDir() + "\" + "C0Report_" + DateSys() + TimeSys() + ".pdf"
      // Configures the print destination
      iDestination(iPDF, UniqueFilename)
      // Prints the RPT_customer report while passing parameters
      iPrintReport(RPT_Customer, "True")
      // Sends the file to the browser
      FileDisplay(UniqueFilename, "application/pdf")
  • the click code of the "BTN_CustomerFNameLName" button is as follows:
    • WINDEV WINDEV code:
      // Open the preview window
      iPreview()
      // Print the report with parameters
      iPrintReport(RPT_Customer, "False")
    • WEBDEV - Server code WEBDEV code:
      // Generates a unique PDF file name
      File2 is string = fWebDir() + "\" + "C1Report_" + DateSys() + TimeSys() + ".pdf"
      // Configures the print destination
      iDestination(iPDF, File2)
      // Prints the RPT_customer report while passing parameters
      iPrintReport(RPT_Customer, "False")
      // Sends the file to the browser
      FileDisplay(File2, "application/pdf")
  • the code of the "Open" event of the "RPT_Customer" report is as follows:
    // Retrieve the parameter passed to the report
    PROCÉDURE RPT_Customer(Choice)
    IF Choice = "False" THEN
    // Modify the SQL code
    RPT_Customer..ODBCSQLCode = "Select LastName, FirstName FROM Customer"
    END
Minimum version required
  • Version 9
This page is also available for…
Comments
Click [Add] to post a comment

Last update: 07/04/2023

Send a report | Local help