Use this function to extract financial data for general, dimensional, and budgetary accounting. You can define extraction details using formulas to define the report format as well as the actual amounts and quantities.

SEEINFO You can modify the financial data extractions included with standard.

Prerequisites

SEEREFERTTO 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 you to have 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. ).

SEEINFO Because this result exceeds the limit of 20 columns, it cannot be associated with a standard Crystal report. Requests must be exported to Excel.

Header

The Selection list displays the Grids grouped by family (Report group).

Identification

Extraction code (field TXSNAM)

Code for the financial data extraction entered on a maximum of 10 characters.

These extraction codes are unique irrespective of the group of reports.
Description (field TXSDES)

Title for the extraction entered over a maximum of 30 characters.
Group (field LSTGRP)

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.
Description (field GRPDES)

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.
Legislation (field LEG)

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.
Access code (field ACS)

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:

  • Inquiry: It is used to view the calculation of a financial data extraction.
  • Modification: It is used to modify the result of a financial data extraction calculation.
  • Execution: It is used to launch the calculation of a financial data extraction.

Ledgers

Default general ledger type (field GENLEDTYP)

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:

  • The formula transferring the values of accounts having roots 6 and 7 for the default general ledger is as follows: 6..7

  • Otherwise, the formula transferring the values of accounts having roots 6 and 7 for a type of ledger not selected by default - like ledger 4 - is as follows: [4](6..7)

    (4 being the number corresponding to the ledger 4 in local menu 2644).
    In this case, it is mandatory to add the value of the ledger type between square brackets at the begin of the formula.
    These additions are automatically managed by the financial data extraction assistant.
Default analytical ledger type (field ANALEDTYP)

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:

  • The formula transferring the values of the analytical account CPTANA1 for the dimension SEC1 for the default analytical ledger is as follows: NM(CPTANA1,SEC1,*,*,*,*,*,*,*,*).

  • The formula transferring the values of the account CPTANA1 for the dimension SEC1 for a type of ledger not selected by default - like ledger 5 - is as follows: [5]NM(CPTANA1,SEC1,*,*,*,*,*,*,*,*).
    (5 being the number corresponding to the ledger 5 in local menu 2644).
    In this case, it is mandatory to add the value of the ledger type between square brackets at the begin of the formula.
    These additions are automatically managed by the financial data extraction assistant.

Tab Definition

Columns

The number and columns in the financial data extraction are defined on this tab.

  • 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

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

Grid Columns

Column title (field NAMCOL)

Title of the columns in the grid entered on a maximum of 30 characters.
Total (field COLNUM)

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.
Input (field SAICOL)
Style (field FMTCOL)

The style applied to the lines in the column is defined here. This setup is only active in printing mode.

  • Normal,
  • Bold,
  • Italic,
  • Bold italic.
Effect (field EFFCOL)

The printing effect that is applied to the column is defined here.

  • Normal,
  • Strikethrough,
  • Underlined.
Divisor (field DIVCOL)

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.
Decimals (field DECCOL)

Indicate the number of decimals for the amounts in this column. This number must be between 0 and 15.

Storage options

Site detail (field DETSIT)

Storage options for the details used during the calculation of the financial data extraction.
If an option is not checked, the corresponding detail will not be stored in the tables.
The site detail is transparent for the user and is always active.

SEEINFO The business partner detail depends on the account detail and the dimensions detail also includes the natures detail
field FCYINF

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.
Account detail (field DETACC)

Storage options for the details used during the calculation of the financial data extraction.
If an option is not checked, the corresponding detail will not be stored in the tables.
The site detail is transparent for the user and is always active.

SEEINFO The business partner detail depends on the account detail and the dimensions detail also includes the natures detail
field ACCINF

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.
BP detail (field DETBPR)

Storage options for the details used during the calculation of the financial data extraction.
If an option is not checked, the corresponding detail will not be stored in the tables.
The site detail is transparent for the user and is always active.

SEEINFO The business partner detail depends on the account detail and the dimensions detail also includes the natures detail
field BPRINF

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.
Dimension detail (field DETCCE)

Storage options for the details used during the calculation of the financial data extraction.
If an option is not checked, the corresponding detail will not be stored in the tables.
The site detail is transparent for the user and is always active.

SEEINFO The business partner detail depends on the account detail and the dimensions detail also includes the natures detail
field CCEINF

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

Auto numbering of versions (field AUTVER)
Negative in parenthesis (field NEGSTO)

By default, the negative amounts are printed with the minus sign: '-'.

SEEWARNING  This display option is only valid in print mode. It is not taken into account in the inquiry restitution screens.

Report code (field RPTCOD)

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.

Tab Lines

Once the columns are set up, the extraction lines can be created by entering the corresponding titles line by line.

From the Action icon at the beginning of a line:

  • Select Line format to view and modify the line formatting.
  • Select Detail to easily navigate from one cell to another to view and modify if necessary.

From the Action icon in a cell:

  • Select Edit to change the formula.
  • Select Add to add to the current formula.
  • Select Format field to modify the print formatting.
  • Select Exclude details to omit account details like BP or dimension from the report. When you select this option, the characters in the field change color and you then have the option to select Include details and include account details.
  • Select Copy to the bottom to copy the formula in the current cell to all the cells below in the grid. Warning: This replaces all the existing formulas in those cells.
  • Select Significance to view account information for the formula in the cell.

In each cell, you can enter a formula of up to 250 characters, no spaces. If the formula contains several expressions, the amounts must 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

B

All accounts with the same reporting code root value

BE

Only those accounts with the exact reporting code

Bn("XY")

All the accounts having "XY" in the 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 credit or debit. 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 to see if it is credit or debit.

C70 or C"70"

All the accounts starting with root 70 where the balance is credit.

CA and DA

Makes it possible to only extract the credit/debit amounts regardless of the account balance.

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.

SEEINFO Difference between formulas H and I. I(C1) reads all the individual accounts of control accounts; (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 of all entries with at least the three following criteria: analytic accounting CANA1, dimension SEC1 for dimension type 1 and dimension SEC4 for dimension type 2.
If one of these three criteria is missing, the entry of the total cannot be carried out.

NM(CANA1,SEC1,SEC4,?*,?*,?*,?*,?*,?*,?*)

Amount total including all entries with only the three selected criteria: analytic accounting CANA1, dimension SEC1 for dimension type 1 and dimension SEC4 for dimension type 2.
If one of these three criteria is missing, the entry of the total cannot be carried out.

[4]NM("c","s1","s4","s5",*,*,*,*,*,*)

Amount total including all entries with 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).
If one of these three criteria is missing, the entry of the total cannot be carried out.

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 including all entries with at least the three following criteria: analytic accounting CANA1, dimension SEC1 for dimension type 1 and dimension SEC4 for dimension type 2.
If one of these three criteria is missing, the entry of the total cannot be carried out.

[4]NQ("c","s1","s4","s5",*,*,*,*,*,*)

Amount total including all entries with 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).
If one of these three criteria is missing, the entry of the total cannot be carried out.

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 are included

?

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.
Group: group belonging to the selected analytical pyramid.
For the default reference base type, totals the amounts of accounts attached to the pyramid group if they distribute at least the three dimensions chosen.

[4]NM("pyramid.group","s1","s4","s5",*,*,*,*,*,*)

Pyramid: analytical pyramid code.
Group: group belonging to the selected analytical pyramid.
For the selected reference base type, totals the amounts of accounts attached to the pyramid group if they distribute at least the three dimensions chosen.

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.

Grid Details

Description (field FRM0)
field FRM1

 SEEREFERTTO See function help.
field FRM2
field FRM3
field FRM4
field FRM5
field FRM6
field FRM7
field FRM8
field FRM9
field FRM10
field FRM11
field FRM12
field FRM13
field FRM14
field FRM15
field FRM16
field FRM17
field FRM18
field FRM19
field FRM20
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.

Tab Variables

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.

Grid Details

Name (field VARNAM)

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.
Description (field VARDES)

No help linked to this field.

Type (field VARTYP)

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.
Parameter (field VARPAR)
Default value (field VARDEF)

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.

Tab Report

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.

Grid Page header

Left text (field TL)

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

Center text (field TC)
Right text (field TR)

Grid Page footer

Left text (field FL)

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

Center text (field FC)
Right text (field FR)

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

It is possible to transfer a financial data extraction to other folders using this button.

Block number 1

field OBJET
field CLES

Block number 2

From folder (field DOSORG)

Use this field to define the folder from which the record will be copied. The possible syntaxes are described in the Dedicated appendix.
All folders (field TOUDOS)

Use this option to copy the record to all the folders defined in the dictionary (ADOSSIER table of the current solution).
To folder (field DOSDES)

Use this field to define the folder to which the record will be copied. The possible syntaxes are described in the Dedicated appendix.