Database query designer

Manual in PDF format

1. List of tabs in the settings dialog database query

• General – Setting general properties
• Conditions – Definition of restrictive conditions
• Joins – Definition of joins
• Columns – Aggregate table column definitions
• Headers – Aggregate table header definitions
• Colors – Graph color definition
• Other – Setting other properties

1.1. “General” tab

1.1.1. Database table

• Select the edit form from whose database table the records stored in the database will be retrieved.

1.1.2. Options

• Aggregate query - Checking this box determines whether the query should result in an aggregated data set compiled using grouping.

1.1.3. Sort by

• Selection of the column according to which the database records will be sorted, including the sorting method – ascending (ASC) or descending (DESC).
• Optional selection of the second column according to which the database records will be sorted, including the sorting method – ascending (ASC) or descending (DESC).

1.1.4. Color by

• A column selection that determines whether and by which column a colored rectangle will appear in each view table.

1.1.5. Time span by

• Column selection, which determines whether and according to which column a filter for selecting the “from-to” time period will be displayed above the view table.

1.2. “Conditions” tab

• Definitions of query constraints that follow the “WHERE” clause of a database query.

1.2.1. Add condition

• You can use the “Add condition” button to add a new query condition.

1.3. “Joins” tab

• Definitions of joins that are built using the “JOIN” clause of a database query.

1.3.1. Add join

• You can use the “Add join” button to add a new query join.

1.3.2. Refresh

• Using the “Refresh” button, the list of columns on the left and right side of the condition is updated based on the selected accepted table.

1.4. “Columns” tab

• Only when the “Aggregate query” box is checked
• Definition of the columns of the resulting aggregation table.

1.4.1. Add column

• Using the “Add column” button, it is possible to add a new column to the resulting aggregation table.

1.5. “Headers” tab

• Only when the “Aggregate query” box for the “DataGrid” or “LiteDataGrid” control is checked
• Definition of the headers of the resulting aggregation table.

1.5.1. Add header

• Using the “Add header” button, it is possible to add a new header to the resulting aggregation table.

1.5.2. ?

• Use the “?” button to display header syntax help.

1.6. “Colors” tab

• Chart control only
• Definition of colors that will be used to draw individual columns of the chart.

1.6.1. Add color

• Use the “Add color” button to add a new chart color.

1.7. “Other” tab

1.7.1. Template name

• The template name is used to name the database query with the option to copy it when creating other database queries with the same source database table.
• When creating a new database query, all available templates are available in the “Templates” drop-down list on the “General” tab. After selecting a template, all parameters of the database query will be automatically pre-filled with data from the selected template.
• A list of all database queries that are marked as templates can be displayed using a report. A detailed description of the reports is given in a separate manual “Reports”.

1.7.2. Notes

• Notes are used to enter any text intended for the application administrator.

1.7.3. Load only first

• Limitation of the maximum number of records retrieved by a database query resp. the SQL equivalent of the TOP() or FIRST() statement.

1.7.4. Remove records with duplicate

• Selects the column according to which duplicate rows in the retrieved data set will be evaluated, and these rows will then be removed from this set.

1.7.5. Options

• ngef(NETGenium.DataTable)
  • The result of a database query is always a set of data from the database, temporarily stored in an object of type “DataTable”. This data set is then passed to the individual controls for evaluation or visualization.
  • Checking this box determines whether an external function should be run before passing the “DataTable” object to the control, which has the option to change the properties of this object – add rows, change values in individual columns, or delete rows.

1.7.6. Logging

• Using the “Logging” button, a detailed report is displayed with individual records of database query calls and data about
  • the date and time the query was started,
  • the user who initiated the query
  • query processing time in milliseconds,
  • the number of records returned, and
  • specific SQL query.

• The number of records is limited to 100 by default. This number can be manually increased or decreased by changing the “maxrows” parameter in the report URL.