ActiveReports 9 new function: Introduction to the Visual Query designer (VQD), activereportsvqd

ActiveReports 9 new function: Introduction to the Visual Query designer (VQD), activereportsvqd
Zookeeper

Added multiple new features to the recently released ActiveReports 9 Report control to help you create a brilliant and powerful report system in a shorter period of time, this article will focus on the visual data query designer, without the need to manually write any SQL statements, the main content is as follows:

• (1) access the Visual Query designer
• (2) elements contained in the Visual Query designer

  The GUI provided by the visual query designer allows you to easily bind data by using interactive methods to create queries and preview results. Use the visual query design interactive interface to help users who are not familiar with SQL design, modify and preview query results more easily.

  The Visual Query designer supports the following SQL functions:

• Select fields
• Custom expression
• Internal Connection, left outer connection and right outer connection
• Filter data
• Grouping and Aggregate functions
• Data Sorting
• Set aliases for selected fields and Data Tables

(1) access the Visual Query designer

• Visual Query designer in page reports and RDL reports

1. Create a page/RDL report to the data source.

2. Right-click the data source node (the default name is performance1) and selectAdd DatasetOption or select from the Add buttonDatasetAdd a dataset to a report.

3.DatasetIn the dialog box, select the query page and then selectEdit the Visual Query designer.This operation opens the Visual Query designer in the page report or RDL report.

• Visual Query designer in area reports

1. PassReport Data SourceThe dialog box shows the region report connection data source. After the report is connected to the data sourceQuery designerButton.

1. When the button is available, click it. This operation opens the Visual Query designer in the regional report.

(2) Visual Query designer element 2.1 database view

The database view displays the namespaces, data tables, views, and fields contained in the database. You can drag or double-click an element in the database view to add itDesignTab. Alternatively, double-click the icon on the right of each element in the database view to add it to the design tab.

2.2 Query Tool

The Visual Query designer provides a tool for generating queries. The query tool area is divided into three main parts: Design tab, SQL tab, and toolbar button.

2.3 design Tab

The design tab is the area used by the visual query designer to create a query. It provides a visual interface for generating SQL queries.

• Selected field panel

The selected fields panel displays the fields selected from the database view, data tables, or any other element. Each field in the selected field panel contains the following series of Editable options.

Option

Description

Output

The status of the check box determines whether to include this field in the result set. When a field is added to the selected field panel, the check box is selected by default. You can deselect this check box when this field does not need to appear in the result panel.

Table

Name of the data table where the selected field belongs.

Field alias

Aliases are allowed for fields.

Total

Apply this field to groups or totals. A total (expression) is used to display calculation results, retrieve control values, define rules, create calculation fields, and define the report group level.

2 Expression-fields in the data table can be selected. User-Defined expressions can also be specified here.

² GroupBy-groups data based on selected fields.

2. Count: returns the number of items in the group. The SQL COUNT function is implemented.

2. Avg: returns the average value in the group. The AVG function of SQL is implemented.

2. Sum-return the total value of all values in the group. Implements the SUM function of SQL.

2 Min-return the minimum value in the group. MIN function of SQL is implemented.

2. Max-return the maximum value in the group. The MAX function of SQL is implemented.

2. StDev-returns the standard deviation of all values in the group. Implements the SQL STDEV function.

² Var-returns the variance of all values in the group. Implements the SQL VAR function.

Sort

Use a specific sequence to arrange data in ascending or descending order.

Sorting order

You can specify the order of the sorting fields when multiple sorting fields are used.

Condition

You can set filtering conditions for column data. The WHERE clause is used to obtain specified data and ignore other irrelevant data.

Note: When a data table is added to the selected fields panel, all fields in the data table are added to the query. Equivalent to using SelEct * from MERsQuery.

• Relationship between tables

The table and table relationship panel displaysSelected FieldsThe data table of all selected fields in the panel. When fields in multiple data tables appear on the selected field panel,LinkThe button appears under the associated data table name to show the relationship between two data tables.

The table and table relationship Panel provides the following options for each data table:

Option

Description

Table

Displays the names of data tables where all fields in the selected field panel belong.

Alias

A special name can be provided for a data table.

2.4 SQL Tab

The SQL tab displays the corresponding SQL statement for the current query. You can directly edit the query SQL in the SQL tab.

When you switch to the SQL tab, the visual query designer automatically formats the query statements and highlights keywords based on the correct syntax.

InSQL TabYou can perform the following operations:

• Enter an SQL statement to add a new query.
• Use the Visual Query editor to modify SQL statements.

2.5 toolbar buttons

Option

Description

Clear duplicate value check box

The cancel repeated value check box is used to delete repeated data in the SELECT statement results. If this option is selected, only non-repeated values are displayed.

Run

Execute the query and display the result data in the preview result panel.

Save

Save the query in the dataset dialog box.

Clear

Clear all panel content in the Visual Query designer, including content on the SQL tab.

The buttons in the upper-right corner of the drop-down list tool area show two options:

1.Switch window: Expand or closeSelected FieldsAndRelationship between tablesPanel.

2.Display prompt: Displays or hides the prompts for using the Visual Query designer. For exampleSelected Fields"Double-click the table or field name in the database view, or drag it to this point ."

2.6 preview result panel

Displays the result data queried in the Visual Query designer.

This Panel adds the required fields or data tables to the selected fields panel, and clickRunButton to fill in the data.

For more functions of the Visual Query designer, you can download ActiveReports 9 to try it out.

For more information about ActiveReports 9, click ActiveReports V9.