The Querygenerator enables users to evaluate saved data via a graphic interface according to the user‘s own criteria and results lists. The base query and the base results list will be created by CreateCtrl AG according to your requirements.

Users can then open and close the Querygenerator in Tools > Querygenerator in the header:

Selecting the Base View

Available base views can be selected via a drop-down list. For the selected query, the tab SQL shows the basic view on which the query is based as well as the name of the query. The tool button on the right enables you to view the rights assignment. Source Query displays all fields for the results list which are made available by this base view. The flag Display enables you to hide/show certain lines of the results list.

User Rights for the Querygenerator

The button opens the window that enables you to administer user rights. When you select a base view, the window below displays all users that have been assigned to this base view.

The button enables you to add users to this list. The button adds ALL users to this list. You can save your amendments by clicking on the Close button.

Notice:

CreateCtrl usually assigns all users when creating the query. Should you have any questions, please contact the CreateCtrl Support (support@createctrl.com).

Select Columns to be Displayed and Sort Data

The flag Display enables you to hide/show certain results. The sorting can already be set before executing the query. Available options which can be activated by clicking on the field Sorting several times include: sorting in ascending order, sorting in descending order, no sorting.

Entering Search Conditions

You can enter a search condition for a certain line by clicking on this line in the column Condition. This opens the dialog Define conditions:

You can enter the condition and leave the default settings in the operator field (LIKE). If required, you can select one of the following values for the operator field:

Operator	Description
!=	unequal / is not equal to (to be used for comparisons of dates and figures) Example: !=2 unequal to 2
<	less than Example: <2 less than 2
<=	less than or equal to Example: <=2 less than 2 or equal to 2
<>	unequal (see above)
=	is equal - with no exception
>	greater than Example: >2 greater than 2
>=	greater than or equal to Example: >= greater than 2 or equal to 2
BETWEEN	between (figures or dates) Example: 01 Jan 00.00 am and 01 Feb 00.00 am (all days in January, including limits)
IS NOT NULL	is not null
IS NULL	is null
LIKE	similar; with this operator, the flag „Not case sensitive” should be activated, so that the query detects similar search terms in upper as well as lower case
NOT BETWEEN	all terms outside of this period or numerical value
NOT LIKE	all terms which are not like the search term

Enter Several Criteria in One Field

In some cases, it can be necessary to enter several criteria in one field. In this case, the field has to be entered in the query several times.

Click the button Opens the list of columns for this base view.
→ This will open the dialog Available columns:
In this list, select the information that should be shown as an additional column and click on OK.
→ The results list will now display the additional column; the order of the columns can be amended with the arrow buttons. The two buttons in the dialog enable users to select any column and to delete the selection.
Please enter the additional condition for the new field.

Should more than two conditions be required, this process can be repeated until the required number of listings of this field is obtained.
Then start the search by clicking on the button Start query.

Saving Queries

When you have compiled a query with all criteria (fields, sorting and condition) that you require, you can save the query for later use. Enter a query name in the field Query name and save the query by clicking on the button . The new query is now available in the drop-down list. Saved queries can also be amended further.

Flag Groupable

If you activate the flag Groupable, you have different possibilities to extract more information of your data. The field Grouping enables you to create a evaluation using the number, sum, maximum, minimum or average of entries. The field Having enables you to narrow the results.

Select the base view in which you want to count.
Activate the flag Groupable.
Remove the flag Display of almost every line. Only leave the flag of the fields that you want to count.
Example: If you want to count, how many titles belong to a version type, display the fields Title and Version type.
In the field, that you want to count, click the field Grouping and select, if you want to select all entries (COUNT) or only different entries (COUNT(DISTINCT)).
Example: If you want to count all titles, select COUNT in the field Grouping. If you want to ignore double entries, select COUNT(DISTINCT).
In the example, a different column name was added in the field Field alias.

You can also select MIN (for minimum of each entry), MAX (for maximum of each entry), SUM (for sum of each entry - only for numbers), AVG (for average of each entry - only for numbers).
Press Start query.
→ You will now see the results of your query in the results area.
If you are not satisfied with the results, you can narrow the results down. In order to do that, please add a condition in the field Having.
Example: If you want to see only version types with less than 30 entries, to identify seldomly used version types, please enter < 20 in the field Having.