Versions Compared

Old Version 5

changes.mady.by.user Amanda Thomas

Saved on

compared with

New Version 6

changes.mady.by.user Amanda Thomas

Saved on

Key

  • This line was added.
  • This line was removed.
  • Formatting was changed.

...

  • Security permissions to the feature.
  • Affiliation to the companies and divisions you are importing stats for.
  • Opened accounting periods.
  • An Excel file (.xslx) with a header that contains all stat unit import columns.

Stat Feature Setup

Ledger → Maintenance → Segment Descriptions

The stat feature will need to be fully implemented before you can perform imports. One of the most essential setups is that of "Segment Description". Despite already having our GL structure defined, you will need to populate this table with information about the GL segments that you will want to map to.

While not necessary for the import, Stat masters and units (found on the same menu) need to be defined as well, so you can see their descriptions and conversions on reports.


Image Modified

Adding Stat Unit Security

Master → Security → User Profile

To perform stat imports, you'll need security access. To grant this, you'll need to find the category for "Bulk General Ledger Stat Import" on the user profile form. It can be found under "Module Permissions → Ledger → Menu Page: System → Group: Maintenance".

Image Modified

Importing a File

You'll need an excel spreadsheet to perform the import. Any will do, as long as it conforms to the following requirements:

  • The importing sheet must be named "Sheet 1". The file can contain others, but we will import that one.
  • The file must have a header row, with columns for these values, in this order.
    • Company Code
    • Division Code
    • GL Segment Name
    • GL Segment Value
    • Sub Type Code
    • Accounting Period ("FiscalYear-PeriodNumber")
    • Stat Unit 1/2
    • Stat Unit 2/2

The column headers do not need the same names, but it is imperative that they are in the correct order.

When creating the file, you must make sure that string codes like company "01" in our database are not turned into numbers by excel. You can avoid this by typing a ' before the number you are entering. That will enter it as alpha and preserve leading zeroes.


Image Modified

Sample of the Excel format:

View file
nameStat import file.xlsx
height250

Ledger → Maintenance → Stat Import

When you select this feature, you will be prompted to import a file. You'll use a file dialog to find the file. When you are ready to import, you will select "OK".

At this point, it's possible for the import to fail if the spreadsheet does not conform to the requirements above. When that happens, you will receive an error message. Details about these errors can be found in the system's database error log.

Image Modified

Once the file is parsed you will be directed to the screen where you will validate and save the import. Nothing has been saved to the database at this point.

Image Modified

If you want to import another file after saving, or import a different one before then, you can click "New" on the ribbon menu to open the file dialog. This will replace the file you have opened with a new one.

Image Modified

Adding and Editing Rows / Validate and Save

Any of the columns in the "Values Imported From The File" band are editable. These are the values that came from the imported sheet. You can enter your own values from the "New Item Row" at the top of the screen.

Whether imported or added, any row can be right-clicked and deleted from the screen. This will cause that row to be removed from the list. It will not be sent to the database when saved. Rows can be multi selected and deleted at once. There is no way to undo a delete once it has been done. If this happens, you will need to import the file again.


Image Modified

Entries are mapped/validated after two actions:
  1. When the file is imported, and the records are displayed on screen.
  2. When attempting a "Save"

When a value fails mapping or validation you'll see error icons next to the value. Some values, like accounting periods, are composites (more than one column makes up a valid entry in the database). In these cases the error Icon will appear next to the first value.

When a value maps and validates, you will see the IDs of the database records appear in the "Mapped IDs" column. This is a handy way to identify that the value mapped, and for power users with database access, can proof the mappings before committing.

Image Modified

Only one stat can exist for one segment/period/sub type at a time. If an existing value is found, the "Do Stats Already Exist?" band will be populated. In this band, you can see what value is already entered. This value will be updated to the new values when you save.

Image Modified

Proofing Stat Units

Stat Entry Screens

Ledger → Maintenance → Stats by Segment

Ledger → Maintenance → Stats by Sub Type

You can open the stat entry screens and navigate to the GL segments and fiscal years that you have entered values for. You know the import worked because there are no duplicate periods, and the values match what had imported.

If you do not enter a sub-type on the import, then the application will enter it into the stats by segment screen, if you do include a sub type, it will enter it into stats by sub type. You must make note of whether you added a sub type to help track down the values you imported.

Image Modified

Image Modified

P&L Front Report

Ledger → Report Wizards → P&L Front

The P&L Front Detail will display account balances along with calculations against stat unit values. Running this report will not show you the stats, but it will show you that they are being applied to the correct segment/period.


P&L Front Expense Detail Report

Ledger → Report Wizards → P&L Front Expense Detail

The P&L Front Detail will break down GL accounts by cost center or department . Running this report will not show you the stats, but it will show you that they are being applied to the correct segment/period.