Financial Report Formulas

Using formulas for calculating lines in financial reports you can directly reference the general ledger accounts. Enter code of account between # (hash) signs. Reference individual account code or, for that matter,classes, groups, and any analytical lever… Here you can of course use wildcard characters, most often the % sign.
Furthermore, reference can ben made to:

• D – debit transactions on account in a given period
• K – credit transactions on account in a given period
• S – absolute value of account balance in a given period = |D - C|
• P - credit – debit (credit balance in a given period) = C - D
• O – debit – credit (debit balance in a given period) = D - C

Below You Can See Examples of Correct Entry:

• #S1200# = balance of account 1200 in a given period (absolute balance)
• #D12%# = debit transactions on accounts of group 12 in a given period
• #K0%# = credit transaction on accounts of grade 0 in given period
• #O02%# = debit – credit on accounts of group 02 in a given period (debit balance) = #D02%#-#K02%#
• #P90%# = credit – debit on accounts of group 90 in a given period (credit balance) = #K90%#-#D90%#

Initial balance of financial reports formulas (formulas only below)

To display the initial balance before the characters with special meaning  (D, K, S, P, O) use letter Z. For example:  #ZS1200# it will display the initial account balance for account 1200. The conditions which must be satisfied that the initial balance of the account lists are:

• transaction on account must be on opening journal, which is dated the same date printout of balance,
• general ledger open date in administration panel need to be the same as date printout balance (since program version 55.57.00).

 When using the sign type Z is to be noted that this initial balance not deduct in other formulas. It is therefore necessary that the initial balance in the case that the same account occur in other calculations subtracted.

For example: initial balance on 01st of January at account 1200 on debit side is 2.320,00 EUR. In the period of fiscal year there is net amount (15.336,61-10.957,60) 4.379,01 EUR transactions. Total amount of account 1200 is 6.699,01 EUR.

Formulas need to be set as bellow:

Transactions in period one more time cover initial balance in calculation, what means that it must be subtracted. Otherwise, the line initial balance amount will be doubled, once in initial balance in once in transactions.

Below You Can See Examples of Correct Entry:

• #A1# - value of line 1 from column A (Formula 1)
• #B14# - value of line 14 from column B (Formula 2)

YOU CANNOT simplify referencing cells in financial report by using wildcard caharacters.

Use: It calculates the average number of employees to be displayed in income statement according to the year from Period Date from field.

Data for calculation can be found in  personnel file.  

#StDel2# - average number of employees in fiscal year with two decimals.

#StDel3# - average number of employees in fiscal year with two decimals, taking into account in the calculation data period which is less than whole year as the variables #StDel# and #StDel2#.

#WrkrNoStart# - number of employees on date from. 

#WrkrNoEnd# - number of employees on date to. 

#WrkrNoAvg# - average employee number in period (average date from an date to).  

#FxDateTo_USD# - variable allows that particular item converted on the date to of print out report (financial report for example).  Last three characters (after underscore) represents the name of the currency in which the program finds the desired output rat for date to. See example.

#Months# - number of month of business operations.

#SubjeType# - the result of variable would be or FO (person) or PO (company). Result check box Organization Type from  the Administration Panel / Program Parameters / Financials /General.

SQL statements write in formula so that is limited by the sign ~S~ at the beginning and end of the sentence. Variables that can be used in  SQL statements and replace the value at print of the entered criteria are:

Different variables can be used to show periods for data of the particular column. It depend on print out we use.

Glede na uporabljeni izpis pa lahko uporabimo tu spremenljivke za obdobja za prikaz ustreznih podatkov v posameznih stolpcih: 

Few examples of usage SQL sentences in formulas of financial reports:

1. Number of invoices by month regarding date of invoice issued for the target date of financial report from to:

~S~select count(*) from the_move where adDateInv >= convert(datetime, #DateFrom#) and adDateInv <= convert(datetime, #DateTo#) and DATEPART(month, adDateInv) = #Month#~S~

2. Number of entered subjects by entry date (Date Posted) for  Target date (Period Date) from to:

~S~Select COUNT(*) from tHE_SetSubj where adTimeIns >= convert(datetime, #DateFrom#) and adTimeIns <= convert(datetime, #DateTo#) and DATEPART(QUARTER, adTimeIns) = #Quartal#~S~