ActiveReports 9 new features: Visual Query Designer (VQD) Introduction

Original: ActiveReports 9 new features: Visual Query Designer (VQD) Introduction

Added new features to the newly released ActiveReports 9 report control to help you create a beautiful, powerful reporting system in less time, this article focuses on the Visual data Query Designer, without having to manually write any SQL statements, mainly as follows:

• (i) Accessing the visual Query Designer
• (ii) Visualization of elements contained in the query Designer

  The Visual Query Designer provides a graphical interface that uses interactive methods to create queries and preview results to help users bind data more easily. Use the visual query design interface to help users who are unfamiliar with SQL to design, modify, and preview query results more easily.

  The visual Query Designer supports the following SQL features:

• Pick Field
• Custom expressions
• Internal connection, left outer connection and right outer connection
• Filtering data
• Grouping and aggregate functions
• Sorting data
• Set an alias for the selected field and data table

(i) Accessing 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 on the Data source node (the default name is DataSource1) and select the Add DataSet option or select a dataset from the Add button to add a dataset to the report.

3. In the pop-up DataSet dialog box, select the query page and choose Edit Visual Query Designer . This opens the Visual Query Designer in a page report or in an RDL report.

• Visual Query Designer in a zone report

1. Connect the data source to the zone report by using the Report Data Source dialog box. The Query Designer button is not available until the report is connected to the data source.

1. When the button is available, click the button. This action opens the Visual Query Designer in the Zone report.

(ii) 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 add it to the Design tab by dragging or double-clicking an element in the database view. Or, in the database view, double-click the icon to the right of each element to add it to the Design tab.

2.2 Query Tools

The Visual Query Designer provides tools for building queries. The query tool area is divided into three main sections: Design tab, SQL tab, and toolbar button.

2.3 Design tab page

The Design tab is the area that the visual Query Designer uses to create queries. It provides a visual interface for generating SQL queries.

• Selected Fields panel

The Selected Fields panel displays the fields, data tables, or any other elements that have been selected from the database view. Each field in the Selected Fields panel contains the following series of editable options.

Options

Describe

Output

The status of the check box determines whether this field is included in the result collection. When the field is added to the selected fields panel, the default status of the check box is checked. You can uncheck the status when this field does not need to appear in the results panel.

Table

The name of the data table to which the selected field belongs.

Field aliases

Allows aliases to be provided for a field.

Total

Apply to grouping or totals based on this field. A total (expression) is used to show the results of the calculation, retrieve the value of the control, define a rule, create a calculated field, and define the grouping level for the report.

2 Expression-Allows you to select a field in a data table. The custom expression can also be specified here.

2 GroupBy-Group data based on the selected field.

2 count– Returns the number of items in the group. The Count function for SQL was implemented.

2 avg– returns the average in the group. Implemented the AVG function of SQL.

2 Sum – Consumed returns the aggregate value of all values grouped. Implemented the SUM function of SQL.

2 min– returns the minimum value in the group. Implemented the Min function of SQL.

2 max– returns the maximum value in the group. Implemented the MAX function of SQL.

2 stdev– returns the standard deviation of all values in the group. The STDEV function of SQL is implemented.

2 var– returns the variance value of all values in the group. implemented the SQL Var function.

Sort

Use a specific sequence to schedule data, using ascending or descending order.

Sort order

Allows the user to specify the order of the sort fields when using more than one sort field.

Conditions

Allows users to set filtering criteria for column data. The WHERE clause is used to get the specified data and ignore other unrelated data.

Note: When you add a datasheet to the selected fields panel, all fields in the datasheet are added to the query. Equivalent to using the SELect * from Customers query.

• Table and table relationships

The table and Table relationships panel shows the data table to which all the selection fields in the Selected fields panel belong. Once a field that belongs to more than one data table appears in the Selected fields panel, the Relationship button appears below the associated data table name to show the relationship between the two data tables.

The table and Table Relationships panel provides the following options for each data table:

Options

Describe

Table

Displays the name of the data table to which all fields in the selected fields panel belong.

Alias

Allows you to provide a name for the data table.

2.4 SQL tab page

The SQL statement that corresponds to the current query is displayed in the SQL tab page. The user can edit the query SQL directly within the SQL tab page.

When the user switches to the SQL tab page, the Visual Query Designer automatically formats the query statements and highlights the keywords according to the correct syntax.

On the SQL tab , you can do the following:

• Add a new query by entering the SQL statement directly.
• Modify the SQL statement by using the Visual Query Editor.

2.5 Tool bar Buttons

Options

Describe

Cancel Duplicate values check box

The Cancel Duplicates check box is used to delete duplicate data from the SELECT statement results. If checked, only non-repeating values are displayed.

Perform

Executes the query and displays the result data in the preview results panel.

Save

Save the query in the DataSet dialog box.

Clear

Clears the contents of all panels in the visual Query Designer, including the contents of the SQL tab page.

The button in the upper-right corner of the drop-down query tool area will appear with two options:

1. Toggle Window : Expands or closes the selected fields and tables and table Relationships panel.

2. Display tip : Shows or hides hints that use the Visual Query Designer. For example, at the top of the selected fields panel, double-click the table or field name in the database view, or drag it here. ”

2.6 Preview Results Panel

Displays the result data for the query in the Visual Query Designer.

This panel fills the data when you click the Execute button on the Visual Query Designer toolbar after adding the required field or datasheet to the selected fields panel.

For more features of the Visual Query Designer, you can now download the ActiveReports 9 experience.

For more ActiveReports 9 new features, please click ActiveReports V9 to view the text.

ActiveReports 9 new features: Visual Query Designer (VQD) Introduction