Setups
This function is used to carry out financial data extractions relating to the following elements: general, dimensional and budgetary accounting.
The attributes of the financial data extractions are defined within this function: it is used to specify the report format as well as the formulas that will make it possible to extract the amounts and quantities.
The financial data extractions shipped as standard can be modified by the user.
Prerequisites
Refer to documentation Implementation
Screen management
The creation of a new financial data extraction is subdivided into five stages:
- the header is used to enter the extraction characteristics: name, access code title, assignment, etc.,
- the first tab is used to define the columns that it contains,
- the second tab is used to define the lines and fields as well as formulas in order to extract the data,
- the third tab is used to define the variable that will represent criteria or calculation parameters,
- the fourth tab is used to define the header and footer of the report associated with the parameters.
Entry transaction: 99 columns at a maximum
This transaction mode allows the user to set up up to 99 columns in a single screen. This allows the creation of periodic or comparative analysis reports, particularly in the analytical accounting field (for twelve periods in a fiscal year, the achieved budget compared to the budget, in amount and percentage, etc. ).
Since this result exceeds the limit of 20 columns, it cannot be associated with a standard Crystal report. Requests must be exported to Excel.
Header
Presentation
The left list displays the Grids grouped by family (Report group).
Close
Fields
The following fields are present on this tab :
Identification
|
Enter a code for the financial data extraction. There is a 10-character limit. These extraction codes are unique regardless of the report groups. |
|
Title for the extraction entered over a maximum of 30 characters. |
|
The report group is designed only for the classification of grids and it is used to present a left list hierarchy by report group/grid. |
|
Title for the report group entered over a maximum of 30 characters. This field can only be accessed when creating a group or in the first grid (by alphabetical order) of the report group. It is then used again automatically for each grid of the group. |
|
Non-compulsory field containing the legislation code (miscellaneous table 909). The entered value is used upon creation and validation of the folder and upon addition of data during legislation copy. The data concerning the requested legislations and the common data (legislation code empty) on folder creation. Only the records concerning the folder legislations are retrieved on folder validation. The Legislation field is also used as a filter and checked upon movement selection and entry (entry of invoices, payments, journals etc.). If the field is empty, the current record can be used whatever the legislation of the company concerned by the movement. |
|
This access code makes it possible to prohibit access to the financial data extractions for certain users. According to the rights granted, the following operations will be possible:
|
Ledgers
|
All ledger types can be accessed at the level of the formula entry in the financial data extraction. Selecting one type of general ledger by default has following effect on the formula entry:
|
|
All ledger types can be accessed at the level of the formula entry in the financial data extraction. Selecting one type of analytical ledger by default has following effect on the formula entry:
|
Close
Tab Definition
Presentation
Columns
The number and columns in the financial data extraction are defined in this single tab. It is possible to set up a maximum of twenty columns.
- Column title: this is the name of the column.
- Total: this section is only accessible from the second column. It can be either empty (it means that the values are calculated from formulas entered in each of the fields - See tab 2), or a total column that will be automatically calculated by the action of simple operators referring to a column number (for instance: Column 4 = (Column 1 + Column 2) * Column 4 becomes: (1+2)*4).
- Style and Effect: the selected values are applied during printing to all the fields in the column.
- Divisor: it is used to convert the amounts on the basis of the specified value.
- Decimals: each column has available its own number of decimals because according to the data type (an amount, a percentage) it is useful to be able to modify this aspect.
For instance:
Title | Total | Style | Effect | Divisor | Decimals |
Turnover Year N-1 |
| Bold | Normal | 1 | 2 |
Turnover Year N |
| Normal | Normal | 1 | 2 |
% year N-1 | 1/(1+2) | Bold | Italic | 0.01 | 0 |
% year N | 2/(1+2) | Bold | Italic | 0.01 | 0 |
|
|
|
|
|
|
If percentages are to be obtained throughout the column, the divisor can be adjusted to 0.01.Finally, the divisor/decimals combination can lead to rounding anomalies.
For instance, an amount of 0.03 EUR is considered as being the same as 0 EUR if the requested number of decimals is 0.
Storage
By default, all calculated amounts are stored in detail. For each financial data extraction it is necessary to specify whether the account, business partner or dimension detail should be stored.
The detail storage is then used to navigate within the inquiry: sorting tree structures are available. For each detail storage, it is possible to specify an additional section linked to the stored section.
For example:
Printing of an auxiliary balance sorted by site/account/Business Partner and displaying the country code for each Business Partner.
Yes / No detail | Stored information | Additional information |
Yes | Site detail |
|
Yes | Account detail |
|
Yes | Business Partner detail | CRY |
It is possible for simplification or volume purposes to only want to obtain a report at an aggregated level: in this case, the storage check boxes must be de-activated. Only the storage by site is mandatory because each returned record is attached to a site.
Special features
The numbering of the Financial data extraction versions can be performed in an automatic fashion once this is requested; a manual numbering remains obviously possible in this case.
It is also possible to print the negative amounts in brackets.
Finally, a default report or graph code can correspond to each Financial data extraction.
Close
Fields
The following fields are present on this tab :
Grid Columns
|
Title of the columns in the grid entered on a maximum of 30 characters. |
|
Field used to parameterize calculations between columns. The formula is simply entered by using the four arithmetic operators and by designating a column by its number (e.g.: 1-2 to define a difference calculation between column 1 column 2). Brackets are authorized. Warning, each entered number represents a column number. If a formula is entered here, all the lines of the column (Lines tab) will be entered with the formula and it will then not be possible to enter them. However, the re-zeroing of this field does not erase the formulas in the Lines tab. |
|
|
|
The style applied to the lines in the column is defined here. This setup is only active in printing mode.
|
|
The printing effect that is applied to the column is defined here.
|
|
Indicate the divisor for the amounts in this column. The divisor must be strictly greater than zero. It makes it possible, for instance, to display amounts in thousands of Euros. By default, this field is initialized to the value of the divisor of the previous column. |
|
Indicate the number of decimals for the amounts in this column. This number must be between 0 and 15. |
Storage options
|
Storage options for the details used during the calculation of the financial data extraction.
|
|
This field is used to associate an additional information, such as a label, to each detail field if required. This information will appear in detail enquiry mode as well as in printing mode. For instance, for the accounts, enter the DESSHO field if the short title should appear next to each account. |
|
Storage options for the details used during the calculation of the financial data extraction.
|
|
This field is used to associate an additional information, such as a label, to each detail field if required. This information will appear in detail enquiry mode as well as in printing mode. For instance, for the accounts, enter the DESSHO field if the short title should appear next to each account. |
|
Storage options for the details used during the calculation of the financial data extraction.
|
|
This field is used to associate an additional information, such as a label, to each detail field if required. This information will appear in detail enquiry mode as well as in printing mode. For instance, for the accounts, enter the DESSHO field if the short title should appear next to each account. |
|
Storage options for the details used during the calculation of the financial data extraction.
|
|
This field is used to associate an additional information, such as a label, to each detail field if required. This information will appear in detail enquiry mode as well as in printing mode. For instance, for the accounts, enter the DESSHO field if the short title should appear next to each account. |
Special features
|
|
By default, the negative amounts are printed with the minus sign: '-'.
|
|
Specific/custom report code for the extraction. The report used during the printing can be customized. This zone is optional. By default, the TXSVAL report is launched. |
Close
Tab Lines
Presentation
Once the columns are set up, the extraction lines can be created by entering the corresponding titles line by line.
- With a right click on a line, it is possible to identify the Line format and to assign a style and specific effect to this line. The Detail option is used to rapidly move through the lines and/or columns using the radio buttons, in order to identify all the characteristics of a field.
- With a right click, by choosing to modify a cell, it is possible to identify the Field format and to assign a specific style and effect to this field. It is also possible to indicate whether the field can be centered or not: this characteristic is used during printing. If this field can be centered, then the account (or BP or dimension) detail can be omitted for this cell according to the choice made by the user in the inquiry. The cells which must be centered are identified by a different color (red as standard) Via the "recopy to bottom" action, the entered formula can be copied to the lower lines. Finally, the Meaning option explains literally how it is made up.
On each line/column crossing, it is possible to enter a formula of up to 250 characters. Each formula must be entered without spaces and if the formula contains several expressions, the amounts add up.
The expressions are broken down into 4 types according to their origin:
- general balance,
- analytical balance,
- budgets,
- Formulas.
Formulas
Formula | Meaning |
Amounts extracted from the General balance | |
70 or "70" | Accounts starting with root 70 |
70..72 | All the accounts starting with root 70 to 72 inclusive |
70 (5) | All the accounts starting with root 70 except those starting with 705 |
701 704 | Sum of the amounts for the accounts starting with root 701 and 704 |
I(C1) or I("C1") | Control account C1 only |
H(C1) or H("C1") | Control account C1 only but without the detail of the accounts |
Bn("XY") | All the accounts having "XY" in reporting code number n |
An("XY") | The same but if a control account corresponds to the criteria |
C and D | Placed at the start of the expression, these letters make it possible to only extract the accounts where the balance is creditor or debtor. Can be applied to all the expressions referring to the accounts. When the calculation of a credit or debit balance is requested in a planning workbench and it the calculation is launched without selection criterion on the site, the account balance (for all types of sites) is calculated. The balance is analyzed then in order to see if it is creditor or debtor. |
C70 or C"70" | All the accounts starting with root 70 where the balance is creditor. |
CA and DA | Makes it possible to only extract the Credit/Debit Amounts irrespective of the balance of the account. |
For instance: CA401 or CA"401" | All the credit amounts for the accounts starting with root 401. |
-7, -I("C1"), 6 -"7" | Amounts are expressed according to the Debit-Credit convention. It is possible to invert the amount in any expression by placing a minus sign in front of it. |
Difference between formulas H and I. I(C1) reads all the individual accounts of control account C1 ; H(C1) reads the global balance.The results obtained are only different if anything other than the global balance is required.Example:DI(C1) is different from DH(C1).
Formulas and components | Comments |
Amounts extracted from the Analytical balance | |
Analytical amounts |
|
NM("c","s1","s4","s5",*,*,*,*,*,*) | All the amounts corresponding to: |
c | Account attached to the dimensional reference base by default |
s1 | Dimension 1 of the dimension type 1 of the default dimensional reference base |
s4 | Dimension 4 of the dimension type 2 of the default dimensional reference base |
s5 | Dimension 5 of the dimension type 3 of the default dimensional reference base |
* | The other dimension types are not requested |
NM(CANA1,SEC1,SEC4,*,*,*,*,*,*,*) | Amount total taking into account all entries distributing at least the three following criteria: analytic accounting CANA1, dimension SEC1 for dimension type 1 and dimension SEC4 for dimension type 2. |
NM(CANA1,SEC1,SEC4,?*,?*,?*,?*,?*,?*,?*) | Amount total taking into account all entries distributing only the three selected criteria: analytic accounting CANA1, dimension SEC1 for dimension type 1 and dimension SEC4 for dimension type 2. |
[4]NM("c","s1","s4","s5",*,*,*,*,*,*) | Amount total taking into account all entries distributing at least the three selected criteria: dimensional account CANA1, dimension SEC1 for the dimension type 1 and dimension SEC4 for the dimension type 2, for the reference base corresponding to no. 4 in the reference base type list (menu 2644). |
Analytical quantities | |
NQ("c","s1","s4","s5",*,*,*,*,*,*) | All the quantities corresponding to: |
c | Account of the default dimensional reference base |
s1 | Dimension 1 of the dimension type 1 of the default dimensional reference base |
s4 | Dimension 4 of the dimension type 2 of the default dimensional reference base |
s5 | Dimension 5 of the dimension type 3 of the default dimensional reference base |
* | The other dimension types are not requested |
NQ(CANA1,SEC1,SEC4,*,*,*,*,*,*,*) | Amount total taking into account all entries distributing at least the three following criteria: analytic accounting CANA1, dimension SEC1 for dimension type 1 and dimension SEC4 for dimension type 2. |
[4]NQ("c","s1","s4","s5",*,*,*,*,*,*) | Amount total taking into account all entries distributing at least the three following criteria: dimensional account CANA1, dimension SEC1 for the dimension type 1 and dimension SEC4 for the dimension type 2, for the reference base corresponding to no. 4 in the reference base type list (menu 2644). |
Pre-committed amounts |
|
PM(CANA1,SEC1,SEC4,*,*,*,*,*,*,*) | identical to the previous expressions |
[4]PM(CANA1,SEC1,SEC4,*,*,*,*,*,*,*) | identical to the previous expressions |
Pre-committed quantities |
|
PQ(CANA1,SEC1,SEC4,*,*,*,*,*,*,*) | identical to the previous expressions |
[4]PQ(CANA1,SEC1,SEC4,*,*,*,*,*,*,*) | identical to the previous expressions |
Committed amounts |
|
EM(CANA1,SEC1,SEC4,*,*,*,*,*,*,*) | identical to the previous expressions |
[4]EM(CANA1,SEC1,SEC4,*,*,*,*,*,*,*) | identical to the previous expressions |
Committed quantities |
|
EQ(CANA1,SEC1,SEC4,*,*,*,*,*,*,*) | identical to the previous expressions |
[4](CANA1,SEC1,SEC4,*,*,*,*,*,*,*) | identical to the previous expressions |
Special characters |
|
* | Replaces n characters. |
NM(CANA*,SEC1,SEC4,*,*,*,*,*,*,*) | All accounts with the route 'CAN' will be taken into account. |
? | Replaces 1 character. |
NM(CANA1,SE?1,SEC4,*,*,*,*,*,*,*) | Dimensions from the dimension type 1 from SE01 to SEZ1. |
Formulas and components | Comments |
Using the Pyramids | |
"pyramid.group" | All the previous syntaxes (analytical, committed, pre-committed amounts and quantities) can be applied to a pyramid group. |
NM("pyramid.group","s1","s4","s5",*,*,*,*,*,*) | Pyramid: analytical pyramid code. |
[4]NM("pyramid.group","s1","s4","s5",*,*,*,*,*,*) | Pyramid: analytical pyramid code. |
Formulas and components | Comments |
Amounts extracted from the budgets | |
Budgetary amounts |
|
GM("b","v","cpt","sec1","sec2") | All budgeted amounts corresponding to: |
b | Budget code |
v | Budget version |
cpt | Budgeted dimensional account |
sec1 | Dimension 1 of dimension type 1 setup at the level of the budget definition |
sec2 | Dimension 2 of dimension type 2 setup at the level of the budget definition |
GM(BU1,1,CANA1,SEC1,SEC2,*) | Amount total of the dimensional account CANA1 that can be budgeted and distributing at least the dimensions 1 and 2, and registered in the version 1 of budget 'BU1'. |
Extracted budget quantities |
|
GQ("b","v","cpt","sec1","sec2",*) | All the budgetary quantities corresponding to: |
b | Budget code |
v | Budget version |
cpt | Budgeted dimensional account |
sec1 | Dimension 1 of dimension type 1 setup at the level of the budget definition |
sec2 | Dimension 2 of dimension type 2 setup at the level of the budget definition |
GQ(BU1,1,CANA1,SEC1,SEC2,*) | Quantity total of the dimensional account CANA1 that can be budgeted and distributing at least the dimensions 1 and 2, and registered in the version 1 of budget 'BU1'. |
The other formulas are as follows:
Formulas and components | Comments |
Other formulas | |
L1 | Picks up the calculation in line 1 of the current column. |
L3(2) | Picks up the calculation in line 3, column 2 |
L1:5 | Sum of lines 1 to 5 in the current column |
L1:5 (2) | Sum of lines 1 to 5 in column 2 |
R(L1*V_TAUX) | Multiplies the amount in line 1 (current column) by the V_TAUX variable |
Since the calculation is carried out column by column, it is possible to reference in a formula of this type all lines for one of the preceding columns or the preceding lines of the current column. The search for a value of another version and/or financial data extraction is performed by means of a variable, that is simply entered into the formula. When starting the calculation, the version and any extraction code is entered in the variable, preceded by the $ character, as well as the cell(s) according to the "L" totals formula described above.
For example:
$V1("L5") to use the amount of line 5 of the current column, for version V1 of the current grid,
$TAB1. V2("L5(2)") to use the amount of line 5 of column 2, for version V2 of the TAB1 financial data extraction. The " " are optional.
Unlike with the "L" totals formula, it is possible using this syntax to access any cell in a grid or version, even if the column number is greater than that of the current column.
Close
Fields
The following fields are present on this tab :
Grid Details
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Close
Action icon
Standard Rerouting Action
Detail
The option Detail is used to rapidly move through the lines and/or columns using the radio buttons, in order to identify all the characteristics of a field.
Close
Tab Variables
Presentation
The variables are used in the analytical and totals formulas, as well as to search for a value in another version and/or extraction. They can replace any argument in these formulas.
Name
The name of a variable always starts with "V_". If these two letters are not entered, X3 will complete the name as necessary.
It is possible to define as many variables as required in an extraction. This number is limited to the number of lines that can be entered in the setup screen. Modifying a variable name leads to modifying this name in the extraction formulas.
The deletion of a variable can only take place if it is no longer used in the formulas.
Type
This field is optional notably in the case where the control (or selection) is not required. If for instance, a variable is created on the Nature field and the Nature value is fixed ("601MAT"), this field is of no interest.
On the other hand, by entering a data type, a selection window is available that will help with the choice of values in the "value by default" column as well as at each calculation launch.
For example:
Select type "CNA" to obtain the selection and the control on the natures or nature groups in the " default value" column. It will then be possible to change the nature upon each launching of said data extraction. A selection window will be available to make the choice. Its existence is controlled with respect to the nature plan.
Close
Fields
The following fields are present on this tab :
Grid Details
|
The variables are used in the analytical, totals and fixed asset formulas, as well as to look for a value in another version and/or extraction. They can replace any argument in these formulas. The name of a variable is entered over a maximum of 10 characters and always starts with "V_". If these two letters are not entered, X3 will complete the name. It is possible to define as many variables as required in an extraction. This number is limited to the number of lines that can be entered in the TXSA3 screen. Modifying a variable name leads to modifying this name in the extraction formulas. The deletion of a variable can only take place if it is no longer used in the formulas. |
|
No help linked to this field. |
|
Enter an X3 data type in order to control the value of the variable entered at the launch of the calculation. A selection by right click will also be available on the values of this data type. This zone is optional. Leave the field blank if the control (or the selection) has no sense for this variable. For instance, chose the "CNA" type to obtain the selection and the control on the natures and groups of natures. |
|
|
|
Enter a default value for the variable. This value will load by default the values of the variables entered during the launch of the calculation. |
Close
Tab Report
Presentation
In this tab it is possible to set up the page format information that will be used in the report.
A free text must be entered between " " ("My Financial data extraction") contrary to a formula field ([F:TXS]DES: Financial data extraction title ) or call to a variable (GUSER: current user).
These different expressions can be combined.
Close
Fields
The following fields are present on this tab :
Grid Page header
|
Enter the additional text to be printed on each page header and footer of the financial data extraction. The extracted data are the result of the evaluation of the alphanumeric expressions, fields in the so-called "basic" tables (companies, sites, currencies) or even variables on line at the time of the calculation. The free text must be entered between brackets, unlike the variables. (Eg.: date$, time$, GUSER, "Print test", etc. ). |
|
|
|
|
Grid Page footer
|
Enter the additional text to be printed on each page header and footer of the financial data extraction. The extracted data are the result of the evaluation of the alphanumeric expressions, fields in the so-called "basic" tables (companies, sites, currencies) or even variables on line at the time of the calculation. The free text must be entered between brackets, unlike the variables. (Eg.: date$, time$, GUSER, "Print test", etc. ). |
|
|
|
|
Close
Reports
By default, the following reports are associated with this function :
TXSPAR : Fin. data extraction setup
This can be changed using a different setup.
Specific Buttons
|
|
The following fields are included on the window opened through this button : Block number 1
Block number 2
Close It is possible to transfer a financial data extraction to other folders using this button. |