Subject:

How to manually build a reporting tree.

Possible Resolution:

Before building reporting trees, first determine the various reporting structures required. The best approach is to carefully consider how the General Ledger account structure is set up. Next, draw an organizational chart of the company using the current General Ledger departments, projects, and so forth as the lowest detail level. Draw as many boxes as needed to show how the different segments might represent divisions or how regions are grouped together on higher-level reports.

Note: At least one detail unit must be specified when building a reporting tree. If the full account is used in a row, the masking in the tree is ignored.

To manually build a reporting tree, define all lowest level child units, and then define each successive level of parent units. Each parent can then be linked to its appropriate children. Every unit in the tree must have a company code (column B) and a unit code (column C).

1. In FRx, select File / New / Tree. Create or edit a reporting unit from the reporting tree worksheet, or the 'Reporting Units' dialog box.
2. Click the 'Tree Form' button, or click 'View Form' from the Tree menu to open the 'Reporting Units' dialogue box.
   Note: The following steps refer to the 'Reporting Units' dialog box. The boxes in the reporting tree worksheet are essentially the same as the boxes in the dialog box. When the labels that identify these boxes differ between the worksheet and the Reporting Units dialog box, the worksheet label is in parentheses after the dialog box label.
3. In the 'Unit Code' field, enter a code to identify this reporting unit (maximum 16 characters). This code identifies the unit in the graphical reporting tree. The @RUNIT code in report headers and footers can be used to display the unit code in the report. For ease of use, establish a coding system that is consistent and easily understood.
4. In the '% Allocation to Parent' field, enter a percentage of this unit allocated (rolled up) to its parent unit. The percentage with or without a decimal point can be entered. For example, a 25% rollup can be entered as .25 or 25. A fraction of a percent can also be entered. For example .025 would be a quarter of a percent.
   Notes:
   - If a percentage that is less than 1 is used, the 'Allow Rollup <1%' option in Catalog of Reports / Report Options / Tree Options must be enabled.
   - The percentage entered here applies to each row of the row format before it is added to the parent report. For example, if a child unit is to be divided evenly between two departments, the amounts in each row would be multiplied by 50% before being added to the department report.
   - A reporting unit cannot have two parents. To do this, another reporting unit (with the same account mask codes) to roll up the additional 50% must be created.
5. Click the 'Company' arrow. From the list, double-click the company code. If this tree is used for multiple companies with similar account coding, select '@ANY' so that this tree can be used for all companies. For consolidation reporting, select the appropriate company code for each unit. This directs FRx to the appropriate location and format of General Ledger data for that company. This information is defined in the 'Company Information' dialog box available from the 'Company' menu.
6. In the 'Title/Desc' field, enter an appropriate title for this reporting unit. The title entered appears in selection windows for reporting units. It also displays in the report header or footer if '@TITLE' is entered as a code in the 'Header/Footers' tab of the 'Page Options' tab in the Catalog of Reports or in the report row description if '@UNIT' is entered in the row format description box.
7. In the 'Informal Desc' field, enter an optional informal description. This description is used for reference purposes only; it does not display in any window listings or print on any reports.
8. If entering a detail unit (one that pulls data from the General Ledger, a row format, or an external worksheet), it must contain either a valid General Ledger account mask or spreadsheet link. This mask combines with General Ledger account information from the row format to produce a financial report. Every account mask must contain hooks (&&&&) that are replaced by row format account codes at report generation time. Wildcards (????) can be placed in any position of the account mask. An account mask can also be placed in a summary unit (for example, for expenses directly related to that unit). If this is done, accounts that are placed in parent units should not be used in child units because this would cause amounts to be duplicated.
9. Click the 'Int'l Format' arrow to assign a format style to apply to specific reporting unit amounts. Formats are defined in the International Formats command of the Company menu. If an international format for this unit is not assigned, the format that was specified for the unit's company (in this window's Company box) is used.
10. Select the 'Page Break Before Unit' check box for this reporting unit to begin on a new page. When this option is selected for any reporting unit in the tree, the 'Use Reporting Tree' option must be selected in Catalog of Reports / Report Options / Tree Options. This uses the page breaks defined in the reporting tree.
11. In the 'Optional Row Formats and Links' section, enter information that specifies the unit's links to the General Ledger, external spreadsheet, or row format.
12. The Row Format Name allows a different row format for each unit in the tree to be specified. With FRx, the name of a row format with each reporting unit in the Row Format Name box can be stored. When the report is generated, FRx uses the appropriate row format for each unit. Identifying a row format in this box is necessary only if using multiple format, or if importing data from an external spreadsheet (using a link to an external worksheet column in the row format). If using multiple formats, a row format for every unit in the reporting tree must be entered.
    Note: If specifying row formats in the reporting tree, the 'Use Row Format from Reporting Tree' check box must be selected in Catalog of Reports / Row Format section when generating the report.
13. If a row format is entered, use the 'GL' or 'Row Links' fields to select the link (in the row format) for this reporting unit. Use this option when multiple links of the same type in the row format exists.  Note: Entering a GL link is optional and should be avoided unless a specific reason for doing so exists. FRx automatically determines which link to use in each report. Entering a link here overrides that selection process.
14. If the selected row format has more than one link, use the 'Wks Link (Ext Link)' option to specify which link to use for this reporting unit.
15. Click the 'Wks Link' arrow to display a list of worksheet links in the row format, and select the appropriate link.
16. If a separate link to an external worksheet has been established in the row format, specify the Lotus 1-2-3 or Microsoft Excel file to be imported into this unit in the 'Wks File Name (Ext File)' field.
17. To specify a worksheet, select from the following options:
• To specify a worksheet within a Microsoft Excel workbook, type the workbook name in brackets followed by the worksheet name. For example: [COMPBUDG.XLS]DENVER
• To specify a sheet within a Lotus 1-2-3 WK1 spreadsheet, type the spreadsheet name in brackets followed by the sheet letter.
  Note: FRx imports only the first sheet in a Lotus 1-2-3 WK3 spreadsheet. To select a different sheet, save the sheet as a separate WK3 file.
18. If a file is entered without a full path, FRx uses the path defined for the default IO_Data directory in the 'Company Information' dialog box. An external worksheet can be imported into any unit in the reporting tree.
19. Click 'OK' to add the reporting unit to the tree. This returns you to the 'Reporting Tree' window where the next unit record can be defined.
20. Repeat steps above to add all of the detail units for the reporting tree.

Note: This procedure outlines the process of setting up units manually from the 'Reporting Units' dialog box. It may be easier to use the worksheet in the 'Reporting Tree' window to add the other units. This option allows the information for the previous units entered to be viewed.