Graphical query designer

• Code (field COD)

This code is used to identify a request.

• Title (field INTIT)

It used to define a name associated with each record.

• Short title (field INTITSHO)

This title, which provides a description of the record, is used in some screens or records when there is not enough space to visualize the long title.

• Active (field ENAFLG)

This check box is used to activate or deactivate the current record without losing its content.

A deactivated record cannot be used (by calling its code) in other records (documents, setups, etc.) or during mass processings.

The authorizations for a given function can prohibit the creation of an active record. In this case, the box is cleared by default and it can only be modified by an authorized user or via a signature circuit defined by Workflow.

• field CLBFL

• Number of lines (field NBRLIG)

Used to define a logical number of lines by page in the inquiry screen generated by the query designer. If this number of lines exceeds the number of physical lines that can be displayed, a scrolling bar appears on the righthand side of the grid. Buttons located in the upper part of the screen will enable navigation between logical pages (view next, previous, last, first page).

• Maximum lines (field MAXLIG)

The maximum number of lines corresponds to a query parameter (maxrows) that stops the search once the number of lines meets the conditions that have been found in the database (these lines will then be sorted, but the database does not guarantee that the N lines found are the first N in the sort order). This represents an optimization used to limit the database load.

• No. fixed columns (field NBRCOL)

Makes it possible to fix the first x columns of a grid in the case of horizontal paging.

• Maximum times (field MAXTIM)

Allows the stopping of the search after this period of time. (expressed in seconds) This makes it possible to avoid waiting too long a time, namely in the case where a new query is being set up.

Note: Contrary to the maximum number of lines, this information is not transmitted to the data base. The maximum time can be exceeded if the duration of the inquiry is too long.

• Report (field RPT)

It defines the code of a report that will be associated with the File / List function from the query inquiry. If this is not entered, the report ALISTE is used, which will submit a default page format. It is possible to duplicate this generic report (in principle it will then only be necessary to modify the page format) to create reports perfectly defined for the query thus defined.

The report code entered here can be associated with a set of print codes via the dedicated function.

• Selection criteria (field SEL)

Used to enter selection criteria, which are logical expressions applied to table fields, and that can include constants, functions and operators. Only the lines in the table that meet these conditions will be extracted. The formula editor is used to simplify the writing of such expressions.

• Link expression (field LNK)

It is necessary to define here the links that have not been defined in the dictionay or which have to be used in priority. The link structure must correspond to the key of the file to be linked. It must have as many fields separated by semi-colons as key parts. In effect, the exact syntax is the following:

[F:ABV1]CLE=expression1 ; expression2..., where:

    [F:ABV1]CLE is the key of the linked table. Using the right click provides access to the Choose a table function, which submits the list of all the tables presented in the first tab. Selecting a table leads to the display of the first index in the table. If another index is required, the function Choose an index is also accessible via right click, which will make it possible to choose from amongst the list of all the indices for the table previously selected.

    expression1, expression2 represent the expressions used to give values to the various fields of the previously defined key (if the key has only one part, there is only one expression, otherwise, the expressions are separated by a semi-colon). There is also the Choose a field function, used to enter the various values of the key with fields of one of the tables concerned. After the choice of the key, which displays the number of semi-colons defining the sections of the key to be entered, this function is used to successively fill all the missing sections. It should be underlined that constant key parts can be defined.

From the database perspective, this syntax defines left outer joins. It should be noted that a second syntax enables strict joins to be created (which can be interesting from a performance point of view). A tilde (~) is then added to the syntax which becomes:

[F:ABV1]CLE=expression1 ; expression2...

• Query type (field ALLUSR)

Used to manage the display of a query in inquiry mode, with the following behaviors:

• Normal query: the result of the query is displayed if the query has already been viewed by the user. In effect, if the box is not checked, each user launching the query creates a set of data that belongs to them and which cannot be viewed by anyone else.
• Shared query: the result of the execution of this query by a user can be viewed by other users. Shared queries are useful in that when a large query is launched at a given moment by a single user, a group of users can then access the common information. It is recommended, for this type of query, that only one user can run the query, the others having inquiry access. In addition, it is preferable to run this type of query in batch mode if it is particularly large; the corresponding batch task code is called CALALH.
• Recalculated query: the result of the query is never displayed automatically. It is necessary to press on the button to refresh or enter the criteria. This type of query is interesting in that it makes it possible to avoid reloading data if they are known to become rapidly obsolete.

• Group (field GRP)

Used to make a classification criterion available for requests.

• Access code (field ACS)

This access code is used to to limit access to the current record for certain users.
If the field is entered, only the users having this access code in their profiles can view and modify this record.

The execution right controls who can execute the query in question (by refreshing the query or by modifying the parameters and then re-running it).

• Object (field OBJLNK)

Used to determine the authorization filters applied to the forming of the query. These filters are the filters by site, by role and by access code.

The filtering principles are the following:

• The object code is used to define the access code and site code fields on which the filter will be applied.
• This filter is then carried out based on the authorized sites and access rights assigned for object management to the user running the query.
• Similarly, the filter on the roles, defined by the object code, is also applied.

This of course supposes that the table on which the filters are carried out is part of the generated query. What is meant here is the table containing the access codeand site code fields, and, when necessary, the field defined by the roles assigned to the user. This is not necessarily the main table in the query: for instance, a query on document lines can be filtered with respect to authorizations given for the fields of tables linked to the document header.

Warning, these filters are only applied during the running of the query and not during inquiry. This is important if the query is shared, for instance, or if the filters by site for a user have changed between the time when the query has been calculated and the time when it is viewed.

• Function (field FCTLNK)

Used to link the authorizations to access the query with those authorizations granted on the function.

The principle is as follows: upon entering the query, it is checked that the linked fucntion is authorized to the user (for at least one site, if the authorization on said function is granted by site). This is checked only in the case when this query is not shared.

In effect, if the query is shared, it is admitted that the authorizations of the person that run the query are applied and shared with all those that have the right to view it. It is therefore necessary to be very careful when defining the authorizations of the shared queries if they concern sensitive data. This is done using the access code located in this tab.

• Representation (field REPGRA)

This indicator defines the fashion in which the representation of the data will be made in the screen. The possibilities offered are as follows :

• Character :  the data is uniquely represented un the form of a grid.
• Char. or Graph. : the data are presented in the form of one or to modes. The default mode is determined by the field Default display defined in the graphical parameters of the record.
• Char. and Graph. : the data is presented in the both modes simultaneously. The graphical position  with respect to the grid is determined by the field Position for the Graph tab.
• Character :  the data is uniquely presented in the form of a graph.

• field OPTGRA

Used to organize the relationship between the character and the graphic, when the presentation mode for the data allows both. According to the case :

• if the representation is Char or Graph, the default value is chosen for the representation.
• If the representation is Char and Graph, the position of the graph is given with respect to the data grid.

• field IMAG

Image explaining the information previously entered.

• Type (field TYPGRA)

This field is used once there is more than one numeric value in the grid that is likely to be presented in a graphical form. It can take one of two values :

• Single graph : in this case, only one column of values is displayed at a time (a selector is used to make to choice)/
• Multi-graph : in this case, all the columns are displayed, combined according to the parameter defined by the parameter Representation.

• Representation (field FSHGRA)

When several series are presented in the graph (provided that the presentation of all the series is identical and is not by sector type), it is possible to specify how the series values are combined in the display, by entering the following value :

• Multiis used to present the values of each series side by side (one color by series)
• Cumulative is used to present the values in a vertical total.
• Comparative is used to present them in a relative comparison (the height of the graph is constant).

• Default graph (field DEFGRA)

Defines the graphical representation used for the data to be presented (from the choices Bars, Lines, Area, Sectors). An image is displayed as a function of the choice made illustrating the type of presentation obtained.

If only one series of values is available, the graphical representation is by default fixed by this parameter.

If several series of values are presented, it is possible to mix the presentation modes (one series in the form of bars, another in the form of lines for example). In this case, each series of values is represented as a function of the field Representation located in the grid of fields :

• if it is Baror Line, this representation mode is used for the series of values concerned if it is compatible with the default graphical representation (it is not possible to mix sectors with bars or lines).
• if it is Default, the default graphical representation for the series is used.

When used, if the representation of the series is homogenous (that is there is only one series in the graph), there is a graph type selector for the user to select a different mode of presentation.