Manage Financial Reports

Specifying financial reports

Financial reports consist of a dynamic set of data that you can specify and generate. You can create XML or CSV files or trigger events based on the data you specified. The XML or CSV file is created using the configurable output solution.

To trigger the creation of proposals and files for multiple financial reports at once, you must create report groups. See Manage Financial Report Groups.

  1. In 'Financial Report. Specify' (RGS900), specify a unique financial report ID and select 1-'Create'.
  2. On the (RGS900/E) panel, specify this information:
    Description
    Provide a description for the report.
    Name
    Specify the name of the report.
    Acctg date/per
    Select whether the applicable records in the report proposal are selected based on accounting date or accounting period. Whichever option you select for this field affects the selection of records.
    Printer file
    The printer file is used by the system when creating the XML structure that is connected to the report and when creating the XML or CSV file. This field is not mandatory at this point, but you must specify the printer file before creating the XML structure or file. The printer file must start with a hyphen - to indicate that the printer file is used to create a financial report.
    Service ID
    The Service ID is used by the system when creating the XML or CSV file. The field is not mandatory at this point, but you must specify the service ID before creating the file. Service IDs with communication type 3 or 6 for IDM, that is, 3 or 6, is not allowed.
    Rpt evt trigger
    Optionally, specify a report event trigger. If no event must be triggered for the report, leave this field blank. Report event trigger is used to indicate an event that must be automatically triggered by the financial report when the report proposal is ready.
  3. On the (RGS900/B) panel, select the 11='Sections' related option to open 'Financial Report. Specify Section' (RGS901).
  4. On the (RGS901/B) panel, specify a section sequence number between 1-999 and select 1-'Create'.
  5. In the Fin rpt section field on the (RGS901/E) panel, select a financial report section. See Available sections in (RGS901).
  6. Click NEXT and specify this information:
    Description
    Provide a description for the section.
    Name
    Specify the name of the section.
    Section table keys
    Specify the number of dynamic key objects that are used for storing data at level 1 in the section table. If more levels are required in the table, then you must specify the number of keys for level 2 and 3. You can specify up to 9 as the value.
    Select the dynamic key objects that are used for storing data in the applicable section table. The objects that are available differs depending on the financial report section.
    Selection
    Specify the selection criteria for the section if applicable. Each section has different selection criteria.
  7. Click NEXT.

    For the 22-'Purchase invoices' and the 24-'Sales invoices' sections, the (RGS901/F) panel is shown. On (RGS901/F), the key objects and number of keys per each level is defined for lines of both sections or charges, which only applies to sales invoices. The lines and charges are stored in specific tables. See Available sections in (RGS901).

  8. On the (RGS901/B) panel, exit the program.

Configuring the XML structure of the financial report

Configuring the XML structure of a financial report is optional if it is only used to trigger an event.

The XML structure is used to define the values that are included and how these values are structured, in the XML or CSV file. FREHED is used as the main table in the XML structure and all section tables are connected to the FREHED table.
  1. On the (RGS900/B) panel, select the 8-'Create XML structure' related option to create a record with the selected printer file ID and XML structure that has the same ID in 'XML Structure. Open' (CMS006). You must specify a printer file on the (RGS900/E) panel for this option to work.
  2. On the (CMS006/B) panel, select the 2-'Change' option to modify the root element of the XML structure if necessary. Click NEXT until you are back on the B panel.
  3. On the (CMS006/B) panel, select the 11='XML Structure. Open Section' related option to open 'XML Structure. Open Section' (CMS007). One main line record for the FREHED table is automatically available. This table is used as the base to which detailed tables are connected.
  4. On the (CMS007/B) panel, select the Section tables button or select F20='Add Custom Section' from the Action menu to open 'XML Section. Open Table' (CMS008). In (CMS008), connect tables using the main line section 30 as base XML section.
  5. On the (CMS008/B) panel, specify an XML section number between 101-999 and the table to connect:
    1. On the E panel, specify a field prefix and select 30-'Main line' as the base XML section. Click NEXT.
    2. On the F panel, select the key objects or values that is used to read the selected table. Using a detailed financial report table as an example, the division and financial report ID keys must be retrieved from FREHED. You must specify the financial report section sequence number key as a value because it is known in the setup in (RGS901). Set the remaining keys to the 9-'Read all records' read option and click NEXT.
    3. On the G panel, you can add other filters to select records if necessary. For example, you can filter on a specific financial report level if you defined multiple levels for your section in (RGS901). See Connecting multiple levels of the same table.
    4. Click NEXT.
  6. On the (CMS007/B) panel, select the 21='Add Custom Section' related option on the XML section 30 record and select the XML section you created.
  7. On the (CMS007/E) panel, select the element name to use it in the XML or CSV file for the custom section. If required, you can also clear the group by element value to ensure that the group by element level is not created in the file for the section.
  8. Click NEXT until you reach (CMS007/B).
  9. On the (CMS007/B) panel, select the 11='XML Structure Section. Open Element' related option on the newly created custom section to specify 'XML Structure Section. Open Element' (CMS009).
  10. In (CMS009), specify the elements required in the file. You can specify elements according to the generic configurable output solution. The only functionality specific to financial reports is the ability to translate M3 values to other values in the report through 'Business Message Data Translation. Displ' (CRS881). See Creating a translation for a field.
  11. Optionally, you can create grouping sections in XML structures for financial reports. Grouping sections do not contain data, but are used to group custom tables that contain data. Grouping sections can also contain another grouping section to create multiple levels of grouping.
  12. To create a grouping section:
    1. On the (CMS007/B) panel, specify an XML section value between 101-999 and select 1='Create'.
    2. On the (CMS007/E) panel, specify an element name and section attribute. You also select the base XML section, which is the XML section that the grouping section must contain.

      This is an example of how 2 levels of grouping sections and its custom sections can be defined. Note that the 'master' grouping section must use the first grouping section on the level below as it is base XML section to get the expected structure in the file.

      Grouping sections
  13. If necessary, repeat the previous steps.

    New sections that are connected to the FREHED table are automatically added closest to the main section. The sequence in which sections are displayed in (CMS007) is also the order in which the sections are created in the XML or CSV file.

Creating a translation for a field

  1. On the (CMS009/B) panel, select the Virtual fields button or select F7='Virtual fields' from the Action menu to open 'Virtual Fields. Connect' (CMS012).
  2. On the (CMS012/B) panel, specify a virtual field ID starting with & and select the 1='Create' option.
  3. On the (CMS012/E) panel, specify a name and provide a description.
  4. Select the 11='Translate Business Message Data' field type option.
  5. Click next to create a header record in (CRS881) and open 'Business Message Data. Translate' (CRS882).
  6. In (CRS882), specify the M3 values to translate and the value which they should be translated to in the Message data field.
  7. On the (CMS009/B) panel, select the Related tables button or select F6='Related tables' from the Action menu to specify 'Related Tables. Connect' (CMS011).
  8. On the (CMS011/B) panel, specify the MBMTRN table and a prefix.
  9. Select the 1='Create' option.
  10. On the (CMS011/E) panel, click NEXT.
  11. On the (CMS011/F) panel, specify these key values and click NEXT:
    Key field Read option Value
    TRQF 1-Equal to 0
    MSTD 1-Equal to FINREPORT
    MVRS 1-Equal to 1
    BMSG 1-Equal to 'Printer file'
    IBOB 1-Equal to O
    ELMP 1-Equal to 'Virtual field ID'
    ELMD 2-Blank value
    ELMC 2-Blank value
    MBMC 2-Blank value
  12. On the (CMS011/B) panel, specify the MBMTRD table and a prefix.
  13. Select the 1='Create' option.
  14. On the (CMS011/E) panel, specify a sequence number value greater or equal to 1 and click NEXT.
  15. On the (CMS011/F) panel, specify these key values and click NEXT:
    Key field Read option Value
    CONO 10-Current company
    DIVI 1-Equal to Division from the master table
    IDTR 1-Equal to IDTR from MBMTRN
    MVXP 2-Blank value
    EXTP 2-Blank value
    MVXD 1-Equal to The field from the section table to be translated.
    MBMD 3-First record
  16. Exit (CMS011) to access (CMS009).
  17. On the (CMS009/B) panel, add the MBMD related field to include the translated value in the file.

Connecting multiple levels of the same table

When you defined multiple levels for a section in (RGS901), you must use virtual fields and table filtering to carry the key values from one level to the next. This handling also applies to connect FREHED as a section table to the main line section that has FREHED as table. You can use the latter to include a header section in an XML structure.

To connect an instance of a table to another instance of the same table:
  1. For the custom section defined for level 1, create virtual fields in (CMS012) that contain the keys required to read the records on level 2. For example, this can be the division, financial report ID, and one or more financial report keys. To retrieve these values, you can use various field types such as a substring that contains the full value of the selected key, or a logic formula that always returns the selected key value.
  2. When you add the section table for level 2 in (CMS008), select to read all records on the (CMS008/F) panel and specify the virtual fields created on level 1 in the previous step as key objects when filtering records on the (CMS008/G) panel.
  3. In case you want to connect level 3 to level 2, repeat the process for the levels required.