Database query designer
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.
- 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.
- 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.
- 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”.
- 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.
- 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.
- 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.