SytelineMaster

I have found the Syteline Financial Excel Addon a handy tool for building financial statement, much better than the financial statement writer inside Syteline.  Many long time Syteline user still have hard time understand the old financial statement writer, ever after using it for years.  But the Excel add on is really straight forward, especially for accounting people.

But before it’s latest update, the Addon has two major problems.  One is missing the must need Net Income function; another one is a bug in calculating year-end figures.  The latest update get them both fixed. 

Now the only problem for the Addon is performance.  Since each function, like SLGLBAL() is actually a query to DB, if you are constructing a multiple years detail balance sheet comparison, the report can easily has hundreds of function calls, and it will take time to run.  And if you don’t have direct DB connection, say you are Infor SaaS hosting customer,  you need to rely on web service connection, the performance would ever worst. 

But anyway, for most of user that can run report within LAN, the performance is pretty acceptable.

Just for reference, the following tables are used by the Excel based financial tool:

Ledger
Pertot
chart_bp
periods

parms
chart
site

Product Version

In SyteLine < 8.02.00

unitcd{1..4}

In SyteLine > 8.02.00

chart_unitcd{1}

When creating a new Syteline financial Statement, it is much easier to work on a copy of existing statement report, instead of starting from scratchy.  You can certainly use the Excel export/import to do the copying, but one part of the financial statement line, the total tab, would not get copied over that way. 

Syteline Financial Statements are stored in the following tables.

1) glrpth   statement definition

2) glrpthc   statement columns definition

3) glrptl      statement lines definition

4) glrptls    statement lines definition, total tab, line add up

5) glrptlc    statement lines definition, total tab, column defined.

By using Excel import, only the above 4) would not get copied over correctly.  You may use the following script to get the copy completed.

insert into glrptls
(rpt_id, seq, from_seq, to_seq, total_add)
select
‘New_Report_ID’, seq, from_seq, to_seq, total_add
from glrptls  tt1 where tt1.rpt_id = ‘Old_Report_ID’

One thing to remember, before running the above script, re-set sequence for both new and old report, make them both start from 10 and increment by 10.  This is to ensure both have the identical sequence.  Or, better yet, before you do the Excel import/export, re-set the sequence in old report. 

This is about Progress version of Syteline, on how multi-currency works in Financial Statements.

FINANCIAL STATEMENTS
The exchange rate table (could be local or global) normally used by the database from which the Financial Statement is being printed will be used for the translations. Therefore, unless Current Rate translation is to be used on all accounts, the exchange rates in the Corporate database would best be entered correctly throughout the fiscal year, even if no translations are needed between those currencies until the financial statements are printed. Alternatively, exchanged rates may be entered and back-dated (e.g.,;, for subsidiaries acquired in the middle of the year), assuming a sufficient rate history has been stored correctly elsewhere (e.g.,’, on paper or in the new subsidiary’s system).
FINANCIAL STATEMENT OUTPUT
gl/calc-bal.p:
If None translations is requested, calculate balance as it is done currently.
If Current Rate translations is requested, calculate balance as above, then find current exchange rate and translate and round to the Corporate’s currency.
If End of Period translations is requested, translate each pertot.amt at its end of period exchange rate, and translate the remaining ledger amounts at each one’s end of period exchange rate. Then round the final balance.
If Average Period rate translation is requested, proceed like End of Period, but use the following formula to calculate the average exchange rate for each period.
Avg. Rate = (SUM(Rate * Effective Time))/
( Length of Period)
where Effective Time is the amount of time during which the rate was in effect during the period ( in seconds), and length of Period is also in seconds.
For speed, you may want to calculate, before starting through the sequence lines, the average buying and selling rates for all periods appearing on the report ( if any sequence lines use this method), and save them in a workfile or array for use here.
If Spot Rate translation is requested, DO NOT USE pertot records. Translate each ledger amount at the exchange rate in effect on its Transaction Date. Round the final balance.
We Process all posted transactions and upgrade them to the current exchange rate. For each voucher that has a gain or loss, we post the mount to Accounts payable and the Gain or Loss Account.
FINANCIAL STATEMENTS
Users are able to print Financial Statements in different currencies. Prior to SYMIX 4.0, they could only accomplish this when they were consolidating multiple divisions. The “final” Financial statement would be printed in the currency of the division running the report. The exchange rates used to convert the data were the ones stored in the division running the report. Now in V4.0, users can choose which currency they wish to print their financial statements in. The exchange rates used to print the report are the rates stored in the database that contains the ledger records being processed. The translations are for display purposes only. No posting of any kind takes place & no gain or loss is calculated.
Translations are defined for each line of the financial statement. Users choose to use the Buying or Selling Exchange rate. They choose a translation method: None ( no translations), Spot (historic rate for each transaction), Current ( current exchange rate), Average Period (weighted average exchange rate in effect for the accounting period for each transaction), End of Period (exchange rate in effect at the end of the accounting period for each transaction)