Business Central feature spotlight: “Filter totals by” in the Chart of Accounts
Business Central’s Chart of Accounts shows you all the ledger accounts storing your financial data. It can also be a behemoth to navigate, making the ability to filter on different values to find exactly what you’re looking for all the more valuable for your accounting practices. And there’s one type of filter that is common to overlook that we’d like to dig into—the “Filter totals by” function. But first, let’s look at the Filter Pane as a whole.
Opening and Exploring the Filter Pane
Filtering is available on any list. Begin a filter by either opening the filter pane in the top left corner of the list or by using the keyboard shortcut SHIFT+F3.
The filter pane will open in the left side of the screen.
Your standard filter pane is made up of views and two methods of setting up filtering: “Filter list by” and “Filter totals by.” Let’s look at each:
Views: These are saved filters, typically created for oft-used filters or combinations of filters you use regularly. You can save as many views as you want in a list, which show up directly under the “Views” heading, and these views will apply to all companies you have access to and are available on any device you use to access the system. Importantly, be aware that your default view is set in the top ribbon on the far-left side. In the image above, the default filter is set to “All.”
Saving a view is pretty easy, simply click the “Save” icon in the filter pane after setting up the filters you want. Name it something identifiable so you won’t forget its purpose.
Save filters as views.
You can also share views with teammates who have access to this list by either copying the URL at the top of the screen or clicking the “Copy link” action under the “Export” dropdown.
Share your view by clicking the "Export" action and then "Copy link" option.
Filter list by: Add filters on specific fields to narrow down which records display. For instance, we can filter the list by the Income/Balance field and choose to only populate records that are Income Statements (as shown in the image above). You can add as many filters as you want to narrow down the records as much as needed, and each field will populate either dropdown choices specific to that field or a write-in field to add whichever filter language you want. And again, if you will find yourself using certain filters/filter combinations often, save them as a view to easily access later!
(Note: Filter language [otherwise called filter criteria and operators by Microsoft] is a whole other conversation, but you can check out Microsoft’s documentation on the topic.)
Filter totals by: If you’ve been using filters in Business Central, chances are you’re at least somewhat familiar with Views and the “Filter list by” option in the filter pane. But the focal point of this blog is the last option, “Filter totals by,” which can oftentimes be overlooked as an option. But in the Chart of Accounts, this option filters balances by several useful criteria on calculated fields, such as the “Balance,” “Net Change,” and “Balance at Date” fields. First, let’s define each:
Balance – The account’s balance currently in the system.
Net Change – The total amount that was added or removed from the account balance during the time period indicated within the Date Filter field.
Balance at Date – The General Ledger account balance on the last date included in the Date Filter field.
We’ll focus in and talk about Filtering totals by Date and Dimension.
“Filter totals by” Date
Using the “Filter totals by” Date filter, you can set dates or date ranges in which the Net Change and Balance at Date will change.
If I enter a date (let’s choose December 22, 2022) in the filter, like in the example below, I have a Petty Cash “Balance” of $76,549.23, which does not change by applying the filter. But my “Net Change,” which is the same as the balance unless a date or date range is entered, has shifted to $3,316.37 = this is the amount that was added to the Balance on this date. And my “Balance at Date”—the date being December 22, 2022, is $45,800.15.
“Filter totals by” Date results in a change in the “Balance at Date” and “Net Change” fields.
Now let’s add a date range instead (December 22, 2022 – March 22, 2023, or 3 full months). Ranges are indicated by two dots between the numbers you wish to range. We can see the range has impacted the “Net Change” and “Balance at Date,” but the “Balance” again remains the same at $76,549.23. The Net Change is -$441.27, meaning that during this year period, the Petty Cash balance has decreased by this amount. And the “Balance at Date” (aka the balance up to March 22, 2023) is $42,042.51, a reduction of about $34,000 from the current balance.
“Filter totals by” a Date range will also change the “Net change” and “Balance at Date” results.
(Note: “Balance at Date” and “Balance” matching might mean that the date you filtered to is the same or that no additions have been made between the current date and the filtered date.)
Depending on our filtering needs, we may filter to a single date or a date range to populate the desired fields in the list, allowing for a narrowed down Chart of Accounts based on date.
“Filter totals by” Dimension
You can also “Filter totals by” dimension, at the same time or independently of the date. This filter will query only what has been posted to a particular Dimension code, and if I don’t establish a date filter, the Net Change, Current Balance, and Balance at Date will all be the same.
In our demo environment, we have filters set up for our two global dimensions within this company – Department and Customer Group.
The two dimension filters shown are the global dimensions: Department and CustomerGroup.
The General Ledger Setup page shows the two global dimensions as Department and CustomerGroup.
Let’s look at this date range for a different General Ledger: Total Assets. We can see that during that date range, we have:
Net Change (from Dec 22, 2022 to March 22, 2023) of $73,945.97
Balance (currently) of $195,550.87
Balance at Date (March 22, 2023) of $176,103.65
Example: “Filter totals by” Date range, Total Assets.
Now we will add a Department Dimension Filter—we’ll pick SALES—and we see that our numbers change:
Net Change of $31,555,69
Balance (currently) of $178,357.97
Balance at Date (March 22, 2023) of $158,910.75
No change between the “Balance” and “Balance at Date” fields would mean the same as before: that the current date and the end-date you filtered to are the same, or that nothing has been posted, this time to that dimension, between the current date and the filtered date.
Example: “Filter totals by” Date range and Sales Department dimension, Total Assets.
Conclusion
The “Filter totals by” function in the filter pane within your Chart of Accounts can help you find what account balances you’re looking for faster and filter them down to your exact needs. This can come in handy in many situations. Here’s just two common uses:
“Filter totals by” Date: You want to see your Accounts Receivables balance as of the end of last month, but you’re not looking to run the entire Aged Accounts Receivables Report.
“Filter totals by” Date and Dimension: You want to see expenses for your Sales Department (which is a dimension in your environment) for the last month because they seem high.
As always, if you want help getting started with this function, feel free to reach out to your Syvantis consultant!