Calculation expressions
Calculate financial metrics like percentage changes, gross profit ratios, and current ratios.
note
The following lists common expressions to help you automate data in ONESOURCE Statutory Reporting for an entity or template. This is a reference guide, and doesn't include all expressions. Contact Thomson Reuters Support if you need more help with expressions.
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.
note This expression might cause a problem if divided by zero. If the prior year's revenue is zero, the calculation will cause an error. At the start of the expression, the category for prior year revenue is selected, and the outcome for a zero value is defined. In this example, when prior year revenue is zero, the expression returns zero. When prior year revenue isn't zero, the percentage increase or decrease is calculated using this formula: ((Current Period Revenue – Prior Period Revenue) / Prior Period Revenue) * 100 |
(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:
|
(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:
|
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.