Statistical Parameters
A tool that is used throughout the statistical parameterisation is provided in the software. This tool is used to create a statistical database in a totals file. This file contains the records in the following form :
Code MAJ | Company | Site | Date | Criteria 1 | Criteria 2 | Criteria 3 | ... | Amounts |
The MAJ code is a characteristic code used in the reports and enquiry programmes. It is possible to create as many codes as necessary (each corresponding to a type of statistical information).
The company and site codes are used to have detailed statistics if necessary (this is not mandatory and if this level of detail is not required, the corresponding fields remain empty in the database construction).
The date field is entered with the first date of the period corresponding to the triggering event date (according to the case, this will be the first day of the week, fortnight, month, quarter, year....).
The criteria fields are freely defined by using the fields in the on-line tables (or with parts of these fields, for example the first two characters of the postal code). This makes it possible to have statistics with the analysis axes more or less detailed. In version 140, it is possible to have 1 to 8 criteria (in version 130 the limit is 4)
The amounts fields contain the amounts totals that are to be analysed in the statistics.
The parameterisation is made using the statistics parameters, by creating the triggering records for the statistical database construction. One record at least is created by MAJ code used. Each record is identified by a unique code, which is often confused with the MAJ code, except when two (or more) records updating the same MAJ code (in this case the codes are different).
In addition it is possible to define, for the statistical parameters of this type, if the previsions must be entered (or calculated from the real situation) with regard to the statistical totals.
The statistics enquiries can be made with 1 to 10 expression calculated from the total amounts for the enquiry period, for the previous period, as well as on the budgets entered. For example it is possible to display only the average values for a total by using these expressions. To view the statistics, the enquiry function is started using the MAJ code to be viewed. It will then be possible to zoom on the different levels of detail introduced by the successive criteria. The final zooms on the detail of the aggregated movements (if they are still on line).
A contextual enquiry on the statistics is possible from the File / Statistics that can be parameterised for each object (there is a tick box that defines it). When this function is used, a list of the available statistics codes appear and the zoom is made to the most detailed level taking into account the calling context. For example, if a statistics has been defined by region (the first 2 characters of the default address associated with the business partner), then by customer statistics group, then by customer, then by product, a call to the statistics from a customer record authorising access to this statistic and automatically set itself at the level of the customer detail (then the context is used to give a value by default to the region, to the customer group and the customer code). The statistics for the totals for the customer by product group appear directly.
Prerequisite
Refer to documentation Implementation
Screen management
Header
Presentation
The entry is made over three tabs : a general definition tab, a tab for the definition of the fields to be accumulated and the advanced tab that is used for the definition of the fields to be displayed. In the record header, a code (unique identifier for the parameterisation) and a title are found.
Close
Fields
The following fields are present on this tab :
| It identifies the current statistical code. A set of totals based on one to eight crossed criteria is linked to each statistical code. |
| It used to define a name associated with each record. |
| 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. |
Close
Tab General
Presentation
Essentially the following parameters are found :
Accessibility section
In addition to the Active tick box, which is used to activate or deactivate the statistics that are already parameterised, an access code exists that is used to control access to the statistics by the different users as well as an attachment module.
Management section
Found here is the identifying code that carries out the triggering (customer order header, supplier invoice lines...), which is defined in the triggering events parameterisation, and the update code, which is the default the code for the record (but which can be changed if required in order to have the totals constructed from several parameterisation records). A date range to be used in the data aggregation can also be defined, as well as a time duration for the statistics (expressed in days) used for the expiry management. Finally, the update date can be used to identify when the last calculation took place (when the batch type statistic is re-calculated in an incremental fashion).
Characteristics section
This section defines the manner in which the statistics are aggregates :
for time ( choice of year, quarter, month, fortnight, week, the day)
the organisational level (a global amount, by company or be site)
the update method (total carried out in real time or in batch). It is important to note that only the standard triggering events can give rise to real-time aggregations (each time an update is made, the update is carried out). The added triggering events (except if a custom/specific has been realised) only operate with a batch update.
Enquiry section
This section defines the enquiry characteristics. These include:
a default screen used for the enquiry (remember that these screens can be parameterised, see the enquiry parameterisation, in order to define the pagination characteristics and the order of the fields : the corresponding enquiry is STA). In the absence of the value, the STD screen is used.
the object for the zoom, which will be proposed when carrying out a final zoom on the aggregated data. In principle, defined here is an object managing the principal table associated with the triggering event. Thus,on a customer invoice aggregation, the SIH object is used (invoice headers). It is however possible to choose one of the others. For example, on a statistic used for the product/site (ITF) intersection, it is possible to choose a zoom on the product record (ITM object), on the site record (FCY object), on the intersection (ITF).
The issue report used to print this statistic. A generic report named STAT exists, but this report presents all the columns in a uniform fashion, however it is possible, knowing the context, to create a personalised report by copy and modification of this report
Close
Fields
The following fields are present on this tab :
Accessibility
| 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. |
| An activity code is used to:
In this way, if the activity code is disabled, the marked element will not be useable, and the associated code (if any) will neither be generated nor can be activated. |
| This access code is used to to limit access to the current record for certain users. This access code makes it possible to prohibit access to the code for certain users. If the field is assigned, only those users who have this access code in their profile can modify and/or enter this code according to the rights accorded. |
| Module belonging to the setup. This field is used to specify whether the screen has to be created in the folder database. It is specified when the module linked to the screen is active in the folder. |
Periodicity
| When wanting to establish aggregates with a periodicity not predefined in the software, there is the possibility to define the name of a process here where the following sub-programs should be defined :
An example of such sub-program can be consulted by looking at the SUBSTADEC process, which defines the sub-program corresponding to management by decade. |
| Periodicity for which the statistic is to be carried out. A local menu is used to define the different periodicities (daily, weekly, fortnightly, monthly...). The aggregate records created by the statistical aggregation are dated to the first day of the period. |
Management
| Code for the triggering of the statistic that is used to identify the element that triggers the statistic. It must be defined in the table of the triggering events. |
| The validity start and end dates defined for certain records used to limit the validity of a record between two dates. |
|
|
| This code is by default the statistic code. But it can be different, notably if wanting to create a single aggregate from several different triggering events (which imposes several statistics codes). In this case, the update code will be common to all the statistics codes. |
| Life of the statistic expressed in a number of months. This time is used by the management of purging, once the decision has been made to purge the statistical data. The statistical data normally being strongly aggregated, they take up a limited amount of space and it is often interesting to keep these statistics for a fairly long time. |
| This displayed date is used to identify the last date at which an aggregate has been carried out by recalculation of the statistic. |
Inquiry
| An enquiry screen can be defined in the case of customisable enquiries. For example, in the case of account enquiries. |
| This field makes it possible to define an object. In the Zoom menu for this object, it is possible to find all the statistics codes with this object code. In order that the zoom menu appears in the object management, it is necessary to tick the box Zoom in the parameterisation of the object. |
| This field allows the entry of the Crystal Reports report associated with the consultation of this statistic code |
|
|
Characteristics
| Used to define the organizational level of the aggregate by period (a global, by company or by site aggregate). The site level of detail can only be obtained if the triggering event defines a site code; the company level of detail can only be obtained if a site or company are defined in the triggering event. On restitution, there is the possibility to request either the most detailed parametized, or an aggregate level (by company, or global if the aggregated is managed by site). |
| Used to define if the aggregate in the statistical records is updated in real time or in batch mode. It is important to note that only the standard triggering events can give rise to real-time aggregations (each time an update is made, the update is carried out). Except in the case or customized statistics, the statistics based on non standard triggering events that can only be updated in batch mode. For performance reasons, the use of numerous standard aggregations based on a given triggering event can slow the corresponding transaction. It is therefore prudent to have batch based statistics and to plan to have a regular batch task to update them. |
Block number 6
Close
Tab Fields
Presentation
This tab is used to define the statistical grouping criteria and the expressions to be accumulated in the two grid whose criteria are :
Field
This grid contains the fields used in order as grouping criteria expressed by :
The name of the table (the tables declared on line in the triggering event are accessible) and its abbreviation if necessary (from version 140 a table can be declared under several abbreviations).
The name of the field and the index if it is an indexed field.
The title that will be used to name the column (by default, this title comes from the dictionary).
The position of the first character to be taken into account and the length of the string (when it is a field of the type character string). By default, these two values are 1 and the length of the field, but it is possible to enter just a section of the string (for example, to make an aggregation of the first 2 characters of the post code).
Variables
In this grid, of 1 to 5 or 10 lines can be entered to define the calculations to be carried out to obtain the statistical totals. In the first column, it is possible to enter (or choose) a variable predefined in the triggering events parameterisation. The next column is used to give or to modify an informative title. The third column is used to enter a numeric expression that will be accumulated in the statistical value. This expression can be used in the constants, the variables and the fields coming from all the tables on line at the time of the construction of the file of the statistics : it can only be entered if no variable has not been entered in the first column.
It should be noted that is is not necessary to define all the lines of the expressions and that is also possible to have an empty line (the statistics aggregate can be transferred to another code).
Then the Previsions column is entered. It is used to define if it it is possible to enter the previsions (for the same range as the statistics) for the corresponding accumulated amount.
Finally, three columns that are used to aggregate in a homogenous fashion of the amounts expressed in the variable currencies. If the answer is Yes in the first column (conversion), then the expression is given that is used to calculate the current currency of the amount to be calculated (this is often one of the fields in the triggering table), and the currency in which the total must be made (this can be a constant expressed between double quotes, for example "USD", or even a variable such as GSYSCUR, which contains the code for the reporting currency).
Close
Fields
The following fields are present on this tab :
Table Fields
| Define the table in which is found the grouping criterion for the line of the statistic. |
| When the previous table is on line several times in the trigger event, the abbreviation to be used in this specific case is defined here. |
| Define the field code such as it is known in the table. |
| When the criterion chosen is multi-occurrence, the value of this field is used to give an index (from 0 to N-1, if N is the number of occurrences of the field). |
| Define the title of a criterion as it will appear during an interrogation of the statistics. By default, the title of the field is picked up from the dictionary and so can be displayed in the connection language of the user. But it is also possible to modify it to give a different title, so that other languages can be used to define it. In translatable fields such as this one, you can enter a description in a language other than the connection language: Click On line translations from the Actions menu. |
|
|
| When the criterion field is a string of characters, it is possible to define the aggregation on a sub-string of characters by defining the position of the first character retained (1=start of the string) and the length. For example :
|
|
|
| Display only field that indicates the data type (character,numeric, local menu, date...) corresponding to the criteria entered on line. |
Table Values
| Defines the title proposed by default with regard and the statistical total. |
| Define title for the stock totals in statistics record. |
| This calculated formula, can call all variables and tables on line at the time of the aggregation, is evaluated and its result is aggregated in the statistic aggregate corresponding to the period an the organizational level of the defines aggregation. |
| If this indicator is set toYes, it is possible to define the provisions for the corresponding aggregate amount. These provisions can be entered, calculated from actuals or from provisions in another period and this in an aggregation field freely defined from the criteria defined for the statistic. On the restitution of the statistic, there is the possibility to present both the calculated aggregates, the corresponding provisions and any expression that acts on the two (this is parameterized in the tab Advanced of the statistic). |
| This indicator is used to specify if the previous expression is expressed in a currency, where there is then the possibility to give the currency by calculate expression, as well as the common currency in which it should be converted. The conversion is made at the historic rate defined on the reference date for the triggering event, the exchange rate type used for the conversion being defined by the parameter CHGTYP (the first type if nothing is defined). Thus it is possible to get amount totals expressed in a homogenous fashion. |
| When the statistical amount is expressed in currency, this field is used to identify the code for the destination currency, in order to be able to convert the amounts in a current currency before aggregating them. It is a calculated expression either constant type, or via the intervention of a common variable for example GSYSCUR. |
| The statistical amount is expressed in currency, this field is used to identify the original currency code, in order to be able to convert the amounts in a common currency before aggregating them. It is a calculated expression either of the constant type, or via the intervention of fields that are online at the time of the aggregation. |
Close
Tab Advanced
Presentation
This tab is used to define the additional information :
Columns grid
Defined here are the columns to be displayed that are not those that come from the previous totals. Defined here are the column titles, the formulas for any calculations (differences, ratios or simply the variable itself...) and the column types and a format. In the formulas, the following variable can be used :
C1, C2... Cn represents the calculated columns defined in the grid of the statistic values, expressed in the current period.
CC1, CC2, ... CCn represents the calculated columns for the previous period (entered during the enquiry).
P1, P2... Pn representing the previsions in the current period (if they exist).
PP1, PP2... PPn representing the prevision in the previous period (if they exist).
In the complex formulas, it is possible to use operators, functions and variables : any arithmetic expression can be used here and can be parameterised with the help of the formula assistant, for example :
C1/CC1, which is used to calculate the ratio between a column on a given date and its value in the previous period.
C1/(CC1+(CC1=0)) that is identical to the previous line, but if CC1 is null, the division will be by 1 instead of 0...
max(P1,PP1)/1000 that expresses the maximum between the prevision in the period and the previous period divided by 1000.
The column type can be equal to :
Normal (the calculated amount is displayed)
Progressive total(line N is equal to line N-1 plus the result of the calculation on the line)
% of total (the ratio in percentage is displayed between the line amount and the total amount for all the lines in the grid).
% total(the ratio in percentage between the total amount of the first N lines and the amount for all the lines in the grid is displayed).
Warning, if the grid does not show all the lines (this is the case if the Next button is not greyed out for the first tab), it is not a percentage with respect to the general total, but only with respect to the liens present in the grid : this is occurs if the number of lines parameterised for the enquiry screen is less than the number of criteria at the level viewed).
Finally, the format is used to define, in the decimal format, the number of integers used before and after the decimal point. If this field is not entered, the value of the FMTSTA parameter is used.
Thus, for example, if user has an enquiry grid limited to 10 lines, 20 criteria CR01 to CR20, for which the total amount is alternatively equal from 50 to 100, and 4 parameterised columns C1 (Normal, Progressive total, % total, % aggregate), the following amounts will appear in the first tab :
Criteria | C1 Normal | C1 Prog Total | C1 % Total | C1 % aggregate |
CR01 | 50 | 50 | 6.67 | 6.67 |
CR02 | 100 | 150 | 13.33 | 20.00 |
CR03 | 50 | 200 | 6.67 | 26.67 |
CR04 | 100 | 300 | 13.33 | 40.00 |
CR05 | 50 | 350 | 6.67 | 46.67 |
CR06 | 100 | 450 | 13.33 | 60.00 |
CR07 | 50 | 500 | 6.67 | 66.67 |
CR08 | 100 | 600 | 13.33 | 80.00 |
CR09 | 50 | 650 | 6.67 | 86.67 |
CR10 | 100 | 750 | 13.33 | 100.00 |
Additional criteria
Defined here if necessary are the logical expressions applied to the constants, variables or the fields in the on-line tables. If all the logical expressions defined are not verified, the statistical total is not carried out for the current record.
Close
Fields
The following fields are present on this tab :
Table Columns
| Enter the column title. In translatable fields such as this one, you can enter a description in a language other than the connection language: Click On line translations from the Actions menu. |
| Enter the formula that is used to calculate the total for the column. The Selection button makes it possible to choose the available variables. |
| Enter the column type : - Normal : Column total. - Progressive total : the cumulative line total by line without re-zeroing. - % of the total : line total percentage with respect to the overall total. - cumulative % : the cumulative line % of the total by line without re-zeroing. |
| Used to define, in the decimal format, the number of integers used before and after the decimal point. If this field is not entered, the value of the FMTSTA parameter is used. |
Conditions
| In the case of multiple lines, the criteria are linked by an AND logic. |
Close
Menu Bar
This button is used to copy the statistic in another folder accessible from the server where the current folder is located. |
Error messages
In addition to the generic error messages, the following messages can appear during the entry :
No link to the table XXXX
An attempt has been made to use a criteria extracted from a table not referenced as being linked to the triggering event.
Non-defined variable
An attempt has been made to use in the variables grid a variable not defined in the triggering event.
Incorrect expression
The arithmetic expression entered has incorrect syntax (additional information may follow this message).