Database query designer

Home / Support / Administrators Guide /

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

Obrázek.png

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

Obrázek.png

  • 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.

Obrázek.png

1.3. “Joins” tab

Obrázek.png

  • 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.

Obrázek.png

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

Obrázek.png

  • 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.

Obrázek.png

1.5. “Headers” tab

Obrázek.png

  • 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.

Obrázek.png

1.5.2. ?

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

Obrázek.png

1.6. “Colors” tab

Obrázek.png

  • 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.

Obrázek.png

1.7. “Other” tab

Obrázek.png

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.

Obrázek.png

  • 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.

Obrázek.png