...
This documentation outlines a new feature that we Aeros created to allow users to import gl GL statistics for many gl segments GL segments and periods all at once. We have There are two existing forms for importing stat units, but these are designed to require users to import stats for one segment at a time, making the process a bit slow.
With the import, we you will be able to aggregate stat values for several gl segments GL segments and period periods at once, and import them into a screen for validation and save. If existing stats already exist, we users can update them from the same import. We Users will also be able to insert new rows that were not in the file to the screen itself.
...
To perform these imports, we you will need:
- Security permissions to the feature.
- Affiliation to the companies and divisions we 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. |
|
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". |
|
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 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. |
| ||||
| Sample of the Excel format: |
|
|
| ||
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. |
|
| 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. |
|
| 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. |
|
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. |
|
Entries are mapped/validated after two actions:
|
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. |
|
| 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. |
|
Proofing Stat Units | |
Stat Entry ScreensLedger → 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. | 
|
P&L Front ReportLedger → 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 ReportLedger → 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. |