Skip to main content

Legal

Tax & Accounting

Trade & Supply

Risk & Fraud

News & Media

Books

Developers

Expressions

Calculation expressions

Calculate financial metrics like percentage changes, gross profit ratios, and current ratios.
Expression
Description
(select reporting.Queries.CategoryValueorZero('P 1', 0.01m, 'PriorPeriod', null, 'E1', null, null, null, false) case 0m: 0m
default:
((reporting.Queries.CategoryValueOrZero('P 1', 0.01m, 'CurrentPeriod', null, 'E1', true, null)-
reporting.Queries.CategoryValueOrZero('P 1', 0.01m, 'PriorPeriod', null, 'E1', true, null))
reporting.Queries.CategoryValueOrZero('P 1', 0.01m, 'PriorPeriod', null, 'E1', true, null))
*100)
Calculates the percentage increase or decrease in turnover/revenue in the current year of the financial period.
(select reporting.Queries.CategoryValueorZero('P 1', 0.01m, 'CurrentPeriod', null, 'Company', null, null, null, false) case 0m: 0m
default:
(reporting.Queries.CategoryValueorZero('P 1', 0.01m, 'CurrentPeriod', null, 'Company', null, null, null, false) + reporting.Queries.CategoryValueorZero('F 1', 0.01m, 'CurrentPeriod', null, 'Company', null, null, null, false))
reporting.Queries.CategoryValueorZero('P 1', 0.01m, 'CurrentPeriod', null, 'Company', null, null, null, false)*100))
Calculates the gross profit ratio for the current period.
There are 2 cases defined in this expression:
  • Case 1: Current period revenue is zero
    • The expression returns zero when the current period revenue equals zero.
  • Case 2: Current period revenue isn't zero
    • The expression calculates the following when the current period revenue isn't zero: ((Current Period Revenue - Current Period Cost of Sales) / Current Period Revenue) * 100
(select reporting.Queries.CategoryValueorZero('CL', 0.01m, 'CurrentPeriod', null, 'Company', null, null, null, true) case 0m :0m
default:
reporting.Queries.CategoryValueorZero('CA', 0.01m, 'CurrentPeriod', null, 'Company', null, null, null, false) /
reporting.Queries.CategoryValueorZero('CL', 0.01m, 'CurrentPeriod', null, 'Company', null, null, null, true))
Calculates the current ratio (working capital ratio) for the current period.
There are 2 cases defined in this expression:
  • Case 1: Current period and current liabilities total is zero
    • The expression returns zero when the current period and current liabilities total equals zero.
  • Case 2: Current period and current liabilities total isn't zero
    • The expression calculates the following when the current period current liabilities total isn't zero: (Current Period and Current Assets / Current Period and Current Liabilities)
reporting.Queries.FinancialPeriodDataSum
("FYNonImport|
NotesToBalanceSheet|
CurrentReceivables|
TradeReceivablesProvisionForDoubtfulDebts|
ProvisionForDoubtfulDebts|
Columns|
Value|*", 'PriorPeriod', 'FY|FY', 'E1', null)
Calculates the sum of values in the prior period column of a financial period data capture grid.
In this example, the NL content sums all values in the data grid for the provision of doubtful debts. This sum automates the opening balance in the current period based on data entered for the prior period.

Expression illustrations

The top row in the financial period data capture grid represents the opening balance. There's a column for each period: current period, prior period, and so on. The opening balance of the current period equals the sum of all values in the prior period column.
" "
You can use the expression shown in the previous image to automatically generate the sum. After doing this, the sum value of the prior period matches the balance of the opening balance in the current period.
" "
Expression
Description
(reporting.queries.FinancialPeriodDataSum
("FYNonImport|
NotesToBalanceSheet|
TangibleFixedAssets|
TangibleFixedAssets|
TangibleFixedAssets|
Rows|
*:filter[Section|
Value=B]:filter[RowRef.|
Value=Dep]|
*", "CurrentPeriod", "FY|FY", "E1", null))
Calculates the sum of values contained within a row of a financial period data capture grid.
In this example, sum all the values within the depreciation row of the data capture grid for property, plant, and equipment. This allows you to automate the depreciation balance in the statement of cash flows for the current period.
Filters ensure the selection of the exact row. Specifically, filter on the section label
B
 and fetch the sum of values from the row with the row reference
Dep
.
The expression sums all the values in the depreciation row, resulting in a total of -8,000,000. The expression uses filters for the section and Row Ref to accurately identify the specific row to sum.
" "
" "
Enter this expression into the statement of cash flows to eliminate the need for re-entering data that has already been manually input into the PPE data capture grid.
" "
" "

Arithmetic operators

In ONESOURCE Statutory Reporting, you can use arithmetic operators in expressions to calculate values. Here are the operators and their symbols:
  • Sum
    :
    +
     symbol for addition.
  • Difference
    :
    -
     symbol for subtraction.
  • Product
    :
    *
     symbol for multiplication.
  • Quotient
    : The
    /
     symbol is used for division.

Keep calculations simple

Be careful of using very complicated calculations in ONESOURCE Statutory Reporting. Users often ask for help with automating complex parts of the statement of cash flows. While you might be able to use a complex calculation, make sure it will work reliably. For instance, if your depreciation value uses GL codes that can change, it's better to calculate it manually and enter the number directly.

This article applies to:

  • Product: ONESOURCE Statutory Reporting