Balance Queries
Overview
The Balances screen, available in the Subledger tab, allows you to calculate Balances for specified queries. A query comprises a set of attributes and filtering criteria. The system can aggregate Journals based on the attributes defined in a query and provide Balances on the level of granularity determined by the selected attributes and criteria.
A Balance is the sum of the values of the Transaction Amount fields of all Journal Lines that meet the query parameters.
If you enable support for multiple currencies you can choose to aggregate balances either by amounts in Transaction Currency, Functional Currency, or both. For more details, refer to Multiple Currencies.
Using the same attributes and criteria, you can also calculate Balance Movements, i.e. how a Balance has changed over a specified time period.
In order to enable the calculation of Balance Movements, you have to select the Balance Movement query type using the radio button and then define properties that will allow to calculate the Opening balance date.
The Balances screen comprises an editable section on the left, where you can view, modify, and run saved queries, create new queries as well as export queries to XLSX and CSV formats, and a results grid on the right. It allows you to create two types of queries:
- A Balance query
- A Balance Movement query
Tutorials
How to Create a Balance Query?
- Go to Subledger > Balances.
- Select the Balance query type using the radio button.
- Select the Balance date type from the list. The available options are:
- Current date - the actual date on which you create the query, i.e. the current Business Date
- Previous day - the date on the day before the Current date, determined based on the Business Date
- Previous period end - determined based on the selected Fiscal Calendar
You will be prompted to select a Fiscal Calendar after you choose this Balance date option.
- Previous year end - determined based on the selected Fiscal Calendar
You will be prompted to select a Fiscal Calendar after you choose this Balance date option.
- Specific period end - determined based on the selected Fiscal Calendar
You will be prompted to select a Fiscal Calendar after you choose this Balance date option.
- Specific date - any specific date you define
- If Multiple Currencies functionality is enabled, you will have to choose which currency to display the Balances in using the radio button.
- Select the Balance attributes that will be used to aggregate the Journal Lines into Balances and will allow to define filtering criteria. You have to choose minimum one attribute.
If you save a query with a Specific date, the system will use this specific date every time you run the query. However, all other Balance date types, such as Current date, will be re-calculated every time you run a saved query. For example, if you save a query with Previous day Balance date type on 23 Apr 2021, the Previous day on that day will be 22 Apr 2021. However, if you run this saved query on 26 Jun 2021, the Previous day will be 25 Jun 2021.
- Click the Balance attributes field. A list with the available attributes will appear. Scroll down the list to find the desired attribute:
- Click the attribute to select it. The attribute will appear below the Balance attributes field with Filter and Delete icons. Also, an item count will appear in the Balance attributes field, the count will go up with each selected attribute. If you click the x on the item count, all selected attributes will be deleted.
- Click the Filter icon to select the criteria for the selected attribute. The available criteria types and the Attribute data types they can be selected for are listed in the table in the Query Criteria and Criteria Types section.
If you select a criterion for an attribute, you have to provide a value. If you leave the value field of a criterion blank, the system will throw an Incorrect value error.
- Additionally, you can combine multiple criteria for one attribute with the AND and OR operators using the AND and OR radio buttons. Only one of the operators can be used for the given attribute.
The more attributes you select, the more granular the Balance and the longer the time required to calculate the Balance or Balance Movements.
- You can shift the order of the selected attributes by using the icons next to the attributes’ name. The order of the attributes affects how the records are displayed in the results grid after the query is run.
- Once you have defined all the required attributes, you can run it by clicking the Run button. The query will send a request to the database and return Balances or Balance Movements for all Journal Lines for the specified date based on the specified attributes.
- You can save the query using the Save as new button. A Save as new window will appear. Type the unique name for your query and click the Save as new button.
After saving the query you can delete it. However, if the query is used as a data source in an extract, you will not be able to delete it.
How to Create a Balance Movement Query?
- Go to Subledger > Balances.
- Select the Balance Movement query type using the radio button.
- Select the time period for which the Balance Movement is to be run:
- Daily
- Period to date - determined based on the selected Fiscal Calendar and Business Date
- Period from/to - determined based on the selected Fiscal Calendar and period selection
- Year to date - determined based on the selected Fiscal Calendar
- Previous period - determined based on the selected Fiscal Calendar
- Previous year - determined based on the selected Fiscal Calendar
- Specific period - determined based on the selected Fiscal Calendar and period selection
- Specific dates - determined based on the calendar selection
- Depending on the type of time period you selected, you will be prompted to select different additional properties:
- For Daily Balance Movement, you need to select the Balance date based off of the Opening Balance date will be calculated. The Balance dates available in this selection are listed above in the How to instruction for creating a Balance query.
- For Period to date, you need to select the Fiscal Calendar which will be used to calculate the Balance date and Opening balance date.
- For Period from/to, you need to select the Fiscal Calendar which will be used to calculate the Balance date and Opening balance date.
- For Year to date, you need to select the Fiscal Calendar which will be used to calculate the Balance date and Opening balance date.
- For Previous period, you need to select the Fiscal Calendar which will be used to calculate the Balance date and Opening balance date.
- For Previous year, you need to select the Fiscal Calendar which will be used to calculate the Balance date and Opening balance date.
- For Specific period, you need to select the Fiscal Calendar which will be used to calculate the Balance date and Opening balance date.
- For Specific dates, you need to select the Dates from/to for which the query will be run.
If you save a query with a Specific date, the system will use this specific date every time you run the query. However, all other Opening balance date types, such as Daily, will be re-calculated relative to the Balance date every time you run a saved query. For example, if you save a Query with Previous day Balance date type on 23 Apr 2021 and Daily Opening balance date, the Previous day on that day will be 22 Apr 2021 and Daily will be 21 Apr 2021. However, if you run this saved query on 26 Jun 2021, the Previous day will be 25 Jun 2021 and Daily 24 Jun 2021.
- If Multiple Currencies functionality is enabled, you will have to choose which currency to display the Balances in using the radio button.
- Select the Balance attributes that will be used to aggregate the Journal Lines into Balances and will allow to define filtering criteria. You have to choose a minimum one attribute:
- Click the Balance attributes field. A list with the available attributes will appear. Scroll down the list to find the desired attribute.
- Click the attribute to select it. The attribute will appear below the Balance attributes field with Filter and Delete icons. Also, an item count will appear in the Balance attributes field, the count will go up with each selected attribute. If you click the x on the item count, all selected attributes will be deleted.
- Click the Filter icon to select the criteria for the selected attribute. The available criteria types and the Attribute data types they can be selected for are listed in the table in the Query Criteria and Criteria Types section.
If you select a criterion for an attribute, you have to provide a value. If you leave the value field of a criterion blank, the system will throw an Incorrect value error.
- Additionally, you can combine multiple criteria for one attribute with the AND and OR operators using the AND and OR radio buttons. Only one of the operators can be used for the given attribute.
The more attributes you select, the more granular the Balance and the longer the time required to calculate the Balance or Balance Movements.
- You can shift the order of the selected attributes by using the icons next to the attributes’ name. The order of the attributes affects how the records are displayed in the results grid after the query is run.
- Once you have defined all the required attributes, you can run it by clicking the Run button. The query will send a request to the database and return Balances or Balance Movements for all Journal Lines for the specified date based on the specified attributes.
- You can save the query using the Save as new button. A Save as new window will appear. Type the unique name for your query and click the Save as new button.
After saving the query you can delete it. However, if the query is used as a data source in an extract, you will not be able to delete it.
Query Criteria and Criteria Types
Criteria can only be set for the attributes selected in the query. You can only select a criterion type that is valid for the given attribute data type, e.g., for an Attribute with Date data type you cannot select a “begins with” criterion type.
Table listing query criteria
Combination of Criteria
You can combine multiple criteria for one attribute with the AND and OR operators. Only one of the operators can be used for the given attribute.
The criteria across multiple attributes are by default combined with AND.
Examples:
a) Entity = 'Entity A' OR Entity = 'Entity X' OR Entity = 'Entity 123'
b) Account > '1000000' AND Account <= '2300000'
Example of an incorrect query:
a) Account > '1000000' AND Account <= '2300000' OR Account > '3600000'
Example Balance Calculation
In this example, no prior balance exists, the Journal for 01/01/2020 is the first journal created.
a) Journal Lines received on 01/01/2020
The balance for 01/01/2020 is -3.
b) Journal Lines received on 02/01/2020
The balance for 02/02/2020 is -7 (calculation: -3 + 5 - 9)
c) Journal Lines received on 03/01/2020
An additional Journal Line was received for Posting date 02/01/2020, which means that the balance for that date is different.
The new Balance for 02/01/2020 is 0 (calculation: -3 + 5 – 9 + 7)
The Balance for 03/01/2020 is 2 (calculation: -3 + 5 – 9 + 7 +2)
Saved Queries
On the top-left side of the Balances screen, you can see the Saved criteria section, which lists all saved queries. If you run the same query frequently, you can save it and have it ready to be run. After saving the query you will not be able to edit and delete it.
Once you click one of the saved queries it will appear in the Criteria panel where you can run it or modify it and save it as new.
Navigating the Query Results
The results grid on the right-hand side of the screen shows the records returned by the query. The Balance date and number of returned records are available on the top of the grid.
The maximum number of returned results is 50,000. If your query returns more than 50,000 results the system will throw an error. Only 50,000 records will be visible in the grid.
Initially, the query results are grouped in the table in the order of the attributes defined for the query. Each column allows you to perform alpha-numeric sorting on the records. Moreover, each column in the grid has a filter icon which allows you to further filter the query results.
Table listing filtering criteria
All filtering in the results grid is performed on the results displayed in the grid. Enabling a filter does not re-send queries to the database.
You can drag a column header to the top section of the grid to group the query results using this attribute.
Drill-down
The Opening Amount, Balance Movement and Balance Amount columns have the drill-down icon (magnifying glass) which allows you to open the details for the given Balance in a separate browser window. When you click the drill-down icon, the application will open a new window in your browser, displaying all Journal Lines related to this Balance. You can further query these Journal Lines by using the same query features, i.e., attributes and Core Date. The query attributes you configured for the Balance query are configured as query attributes on the new page by default.
You can further drill down to one of the Journals constituting the Balance by clicking the drill-down icon (magnifying glass) next to the Journal ID. When you click the drill-down icon, the application will open a new window in your browser, which will display all Journal Lines relating to this Journal. You can further query these Journal Lines by using the same query features, i.e., attributes and Core Date, described above. The Journal ID is configured as a query attribute by default. For more details regarding queries, refer to the How to Create a Query? section available in Journal Query.
