YTD amounts and statistics in one column in FRx using Dynamics GP

Problem: In one column in FRx how can I have YTD amounts and statistics where the statistics need be based on “net change”? By nature, YTD columns include any accounts with beginning balances including statistical accounts so how can I work around this issue?

P&L report – in the column layout below column B would not work because it doesn’t eliminate the beginning balance issue for statistical type (unit) accounts. However, column C would give YTD amounts for the income statement and exclude beginning balances for statistics because income statement, by nature, do not have beginning balances. A balance sheet column is trickier.

Balance sheet report – A balance sheet report requires a YTD column figure to include beginning balances but for statistical accounts we need to exclude them, so what’s the work-around? One way would be to delete the beginning balances through SQL, here are some statements, the first for the open year table and the second for the history table:

Delete GL20000 from GL00100, GL20000 where GL00100.ACTINDX = GL20000.ACTINDX and GL00100.ACCTTYPE = 2 and GL20000.SOURCDOC = ‘BBF’

Delete GL30000 from GL00100, GL30000 where HSTYEAR = ‘XXXX’ and GL00100.ACTINDX = GL30000.ACTINDX and GL00100.ACCTTYPE = 2 and GL30000.SOURCDOC = ‘BBF’

BEFORE running this script be sure to take a backup of your database, an even safer method would be to create a test company and run the script there then confirm your balance sheet reports is complete before running the script again in your live company. Also, this deletes all beginning balances for all statistical accounts so you may need an additional (SQL statement) restriction.

After running the SQL script, run reconcile (tools>>utilities>>financial>>reconcile) to recalculate the period (summary) balances table.

This entry was posted in FRx. Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *