Report formulas

 

Please note that the Report formulas is currently only available in those Procountor environments that have the new chart of accounts functionalities in use. In most environments these functionalities are already in use, but the current estimate for updating these functionalities to the rest of our customers is during the summer 2022.

Report formulas allows creating and editing custom report formulas that can be used on the Accounting reports view in the Accounting report type menu. Report formulas view can be accessed by clicking the Report formulas button on the Accounting reports view.

 

 

Creating a new report formula

To create a new report formula, click the New button. This opens a new window where you can select a template for the new report formula:

 

 

The template can be one of the following:

• Income statement
• Balance sheet, or
• Use empty template, that opens a blank report formula editing view.

When the template is selected from the drop-down menu, click Continue to open the report formula editing view. This view is more closely explained in the next section.

Custom report formulas will be visible on the Report formula view:

 

 

Report formulas can be removed and copied using the Delete and Copy buttons after selecting the report formula row. Edit button leads to the report formula editing view, which is more closely explained in the next section.

Report formula's language is determined by user's chosen language for the software. If the user has chosen for example English as language, report formula is in English.

 

Report formula editing view

 

This view is for editing the report formula, and the formula can be edited either

• By editing each row and field directly, which is explained further in the following sections, or
• By editing the formula as text through the Edit as text button, which is explained further in its own section.

On the right side of the view is the Account list section, which is used to edit certain types of report formula rows. More information on this can be found here.

The report formula name is specified in the Name field. This name will be visible in the Accounting report type drop-down menu on the Accounting reports view, where this newly created custom report formula can be selected.

Type selection determines whether the accounting report type is Income statement, Balance sheet, or Cash flow.

Change number signs for account values? selection switches all the report formula values to opposite values.

Changes made to the report formula are saved with the Save button at the top of the page.

 

Table buttons

Report formula editing view has the following buttons:

• New row is added with the Add row button. The new row is added underneath the row that was selected before clicking the Add row button.
• A selected row can be deleted with the Delete row button or copied with the Copy row button.
• Move up and Move down buttons move the selected row up and down.
• Show / hide graphical report KPI selection determines if the KPI column will be shown in the table. KPI’s are Key Performance Indicators used on Management reporting and Management reporting by dimension, and these indicators can be edited through the default report formulas. More information and examples on how to make KPI selections can be found here.
• Show / hide translations shows and hides the fields for translations to different languages.
  • These columns show translations for all rows that are included in the default chart of accounts.
  • If the default translations are changed, the new translation will be used as the primary translation when reports are generated in different languages.
  • When reports are created in different languages, the name of each report row is generated by the corresponding translation set in the column of different languages, and not by the general row name set in Name column.
  • Please note that if the default report formulas include accounts or titles that differ from the default chart of accounts, these will not be automatically translated, and the fields left blank as they do not have existing translations. These translations must be manually filled out.

 

Table columns

The default account report formula rows can be edited through the following columns:

• Row column indicates the row number. First row is R1, second R2, third R3, and so on. The Row column identifiers (such as R1, R2, and R3) can be used in the Formula column to create formulas that connect to or reference other rows on the report.
• Name column is for selecting a name for the row.
  • When reports are created in different languages, the name of each report row is generated by the corresponding translation set in the column of different languages, and not by the general row name set in Name column.
  • Content of the Name column is synchronized to the translation of the language that has been set as company language in Basic info view. For example, if Finnish has been set as the company language, editing the Name column affects also the Finnish translation column on the report formula and vice versa.
• Indentation column determines how much indentation the row will have. Indentation value is selected from numbers 1 to 6, where number 1 means the row will have no indentation, and number 6 means the indentation is the largest possible. By selecting different levels of indentation, the report can be structured in different ways.
• Type column determines the row type:
  • Title selection means the row is a report title row that will not have any balance. These types of rows can be, for example, the Assets or Liabilities title of the balance sheet.
  • Blank selection creates a blank row that will not have any content. This type of row will show up as an empty row on the report. These empty rows can be used to structure the report in a certain way, if for example certain parts of the report need to be separated by empty lines.
  • Balance selection means the sum of the selected accounts will be shown as the row balance. The accounts can be selected either
    • directly from the Formula column by using the [XXXX:XXXX] format account selection or
    • from the right-hand side Account list section, which is explained further in its own section.
  • Row function selection can be used to create functions related to other rows in the Formula column. More specific examples of row functions are introduced later its own, separate section.
• Hide column checkbox hides the selected row from the report.
• Bolded column checkbox bolds the selected row on the report.
• Hide if zero checkbox hides rows that have zero balance. If for example a certain account has no balance, and this selection is checked, this account row will not be shown on the report.
• In default length determines if the row is to be included in a default length report. The report length can be determined in the Accounting reports search criteria.
• In short length determines if the row is to be included in a shortened report. The report length can be determined in the Accounting reports search criteria.
• Invert checkbox inverts the row value from a negative to a positive value, or vice versa.
• Formula column determines how the row balance is formed. The Formula column can include, for example, certain account numbers in the format [XXXX:XXXX], meaning that the row balance is the sum of these selected accounts. the Formula column can also include references to other rows of the report, and the referenced rows are shown e.g. in the following way: [R21:R25]. More detailed information about the calculations and applications enabled by the Formula column can be found in this section.
  

 

Account list section

 

Report formula editing view has a section called Account list on the right-hand side. This section is active and available when the selected report row has Balance selected in the Type column. The Account list will be inactive if the selected report formula row has a row type other than Balance.

Accounts and account groups are selected to the balance rows in the Account list section. If accounts belonging to a certain account group are added to the chart of accounts after creating the report formula, they will be automatically included in the balance of that row.

There are two options in the Account list section that affect the selection of accounts: Use expandable ranges and Use exact ranges:

• Use expandable ranges means the account or account range selected from the account list will include all existing accounts and accounts created in the future in the selected account range, as well as all future accounts that are created in this account range with an account number that is greater than the last account in that account range, and before the next account in the chart of accounts.
  • Example 1: Only one account is selected from the account list, account number 4000. The next account in the account list is 4090. When Use expandable ranges option is selected, selecting account number 4000 will create the formula [4000:4089]. This means that if a new account is created within this range, for example account number 4010, this would be included in the formula.
  • Example 2: Accounts 4000, 4090, and 4110 are selected from the account list. When Use expandable ranges option is selected, selecting these three accounts creates the formula [4000:4129]. Because the next account following account 4110 is 4130, the expandable formula is created in such a way that all accounts that are possibly created after 4110 (all the way up to account number 4129) would be included in the formula.
• Use exact ranges selection means the accounts or account ranges selected from the account list will not include any accounts besides the existing ones, and the ones possibly created in the future in this account range.
  • Example 1: Only one account is selected from the account list, account number 4000. When Use exact ranges option is selected, selecting account number 4000 will create the formula 4000.
  • Example 2: Accounts 4000, 4090, and 4110 are selected from the account list. When Use exact ranges option is selected, selecting these three accounts will create the formula [4000:4110]. If an account is created in the chart of account, for example 4050, it will be included in this formula. Any accounts added to the chart of accounts after 4110, for example 4115, will not be included in the Use exact ranges type formula.

There is a Selected formula field at the bottom of the Account list section:

 

 

When Balance is selected from the Type column, the contents of the Formula column cannot be edited within the column itself. These changes must be made in the Account list section, in the Selected formula field and using the Update formula button.

When accounts are selected from the accounts list, the contents of the Selected formula field will update according to the selection. After the selections have been made, the formula is updated to field in the Formula column with the Update formula button.

The contents of the Selected formula field update automatically when accounts are selected from the list, so creating a formula is the easiest using the account list. The field contents can be manually edited; in this case, typing in a formula in the Selected formula field automatically selects the corresponding accounts from the Account list. When the contents are updated manually, the number on the left-hand side must be greater than the number on the right. If the field contains errors, it will be highlighted in red. Hovering the cursor over the field shows the more detailed reason for the error.

The field contents can be formed in the following ways (when Use exact range is selected):

• If a unified account range is selected (and, for example, some accounts are not skipped) the formula is in the format [XXXX:XXXX].
• If an account range that skips certain accounts is selected from the list (in other words, two separate account ranges have been selected from the list) the formula is in the format [XXXX:XXXX]+[XXXX:XXXX].
• If an account range along with a single account is selected, the formula is in the format [XXXX:XXXX]+XXXX.
• If only a single account is selected, the formula is XXXX.
• If two single (but not consecutive) accounts are selected, the formula is in the format XXXX+XXXX.

The Selected formula field contents differ from the above-mentioned, for example, when Use expandable ranges is selected. In this case, selecting a single account would create the formula in the format [XXXX:XXXX], since the formula will include any accounts that may be created in the future with an account number that comes after the selected account and before the next account in the chart of accounts.

When Use exact ranges is selected, singular, non-consecutive accounts that do not form an account range in the [XXXX:XXXX] format can be selected in the formula. If, however, individual accounts in consecutive order are selected, the account range in the [XXXX:XXXX] format will form automatically according to the selected accounts. This means that if an account that belongs to this account range is added to the chart of accounts in the future, this account will be included in this formula.

• If the intention is to create a formula that does not form the [XXXX:XXXX] type formula when consecutive accounts are selected, follow these steps: fill out the necessary consecutive accounts in the Selected formula field separated by plus signs. For example, 4000+4090+4110. The formula is updated in the Formula column when Update formula button is clicked. If an account, such as account no 4005, is added later to the chart of accounts this will not be included in the formula, since it only consists of singular, already existing accounts.

 

Calculations with row functions

Different calculations can be created on the rows when Row function is selected from the Type column of that row. In this case, calculations can be created in the Formula column in the following ways:

• Addition, subtraction, multiplication, and division of other rows and row ranges. The Row column information is used as row identifiers (R1, R2, R3 and so on). If, for example, one report formula row needs to be divided by another row, these rows are entered in the Formula field as follows: R1/R2. If two row ranges are to be added together, these are entered in the Formula column as follows: [R1:R5]+[R10:R15].
• Addition, subtraction, multiplication, and division of other rows and row ranges that utilize manually entered sums, multipliers, or divisors. To add a certain sum to a report formula row, enter the row and sum in the Formula column as follows: R20+32500. To divide a row interval with a certain number, enter these in the Formula column as follows: [R6:R15]/2,5.
• Addition, subtraction, multiplication, and division that do not relate to other rows or row ranges, and different sums, multipliers and divisors are entered in the Formula column manually.

Please note that direct references to certain accounts or account ranges cannot be made in the Formula column by using the account number (such as 4000/2):

• All values added to the Formula row are considered normal integers, and the result of the previous example would always be 2000, and not based on the balance of account number 4000.
• To add a row that divides the balance of account 4000 by two, it must be done by referring to the row number (for example R5/2).
• If the report formula does not yet have a row that only contains account 4000, this can be added by selecting Balance in the Type column and adding just account 4000 to the Formula column (using the Account list section on the right-hand side).

The calculations on the row function row Formula column can include references to other rows (e.g. R3), references to row ranges (e.g. [R10:R20]), plus signs (+), minus signs (-), multiplication signs (*), division signs (/), parentheses () and numbers up to four decimal places (e.g. 12500,1663).

 

KPI selections on the graphical reports

Please note that, the KPI selections are only available on the Default report formulas views. Even though KPI selections can be made on custom report formulas, these will not transfer to the graphical reports via the custom report formulas. 

 

Editing the report formula as text

Editing the report formula as text is a practical solution for when a report formula created in Procountor environment A needs to be moved to Procountor environment B:

• Click Edit as text in environment A. The report will be shown in text format in the opened window.
• Select the entire contents of the window (Ctrl + A) and copy it (Ctrl + C). The text format report formula can be temporarily pasted (Ctrl + V) and saved, for example, to a separate Word file.
• Switch to environment B and click Edit as text on the report formula view. Empty the contents of the current formula and replace it with the text format report formula copied from environment A. Click Continue.

As a result, the report formula imported to environment B as text from environment A will have all the corresponding rows and columns as the report formula in environment A.

If when choosing Continue on the Edit as text window you get an error message, you can try again after adding ; (semicolon) on each row as the last character first.

 

Important to take into consideration

Report formulas sometimes include some accounts on hidden rows, that do not appear on the reports but are used as some other row's row function. Making sure that the desired accounts or row ranges are included on these rows as well is important. Especially if the template for the report formula has been Procountor's default Income statement or default Balance sheet or the chart of accounts has been modified after creating the own report formula.

The following picture demonstrates how on Procountor's default Income statement the Raw materials and services in total is calculated with a row function from the next row R26. The balance to row R26 is taken from the account range [4000:4999]. If the company wishes to include for example some other accounts in the purchases during the financial year, the report formula should be modified if necessary so that the row R26 includes these as well, if the account range provided is not correct.

By clicking the image it can be opened larger on the browser.