With Advanced Account Schedules for Microsoft Dynamics 365 Business Central it’s possible to use values from any module in the system.
Quite often, the biggest shortcoming of the account schedule module is the lack of non-G/L values. Getting AR, AP, inventory, even jobs or warehousing numbers into financial reporting can be challenging. Business Central is filled with all the raw data to build great reports. Advanced Account Schedules is designed to be able to pull any number or value out of any module directly into the account schedule. Making it possible to create a one-click KPI report or prepare everything for Excel or PowerBI without having to juggle with multiple data sources.
Draft data is also available in Advanced Account Schedules, making it possible to use quotes, order and even information from journals can be used.
You can select single values or calculate sums and count records in a dataset.
You define an Indicator. The indicator has a Method and table and filters.
Advanced Account Schedules support the following indicator methods:
- Lookup – Find any single value
- Sum – Sum all records within a filter of records
- Count – Count all records within a filter of records
- Min – The smallest value within a filter of records
- Max – The largest value within a filter of records
- Average – Calculate the mean value of all records in a filter
- Custom – Allow a developer to create custom indicators
Examples of indicators that can be created with Advanced Account Schedules:
- Number of invoices in a period
- Mean value of items sold
- Ageing and due balances from AR and AP
- Customer count (filtered by regions)
- The total sum of issued credit limits
- Split values on non-posting codes
- Outstanding quotes
- Open order
- Job budget
- Draft entries in Job Journal
There are two different “modes” when it comes to summing several records in a dataset. One of the core technologies in Business Central is called FlowFields. A FlowField is a way to calculate sums of a large number of records in an instant. When selecting the Sum method, Advanced Account Schedules will, behind the scenes, figure out what method to use:
- Classic – Select any field in any table and let Advanced Account Schedules calculate the sum. This can be performance heavy if there are many records within the filter specified.
- SumFieldIndex – If the field selected is the source behind a FlowField, then the calculation will use the same method as a FlowField to perform the summation lightning quick.
An example of using a SumFieldIndex is shown in the screenshot above. This is the same as the Balance Due (LCY) field on the customer. But instead of adding up the balance for each customer, it’s a single calculation.
A lookup indicator is great for locating a specific value, like a price of an item, a credit limit on a customers or perhaps a resource base rate. Lookup indicators are often used as hidden lines as a parameter to a formula.
Counting records is a great performance indicator. Number of transactions for a period or number of jobs are good examples.
Finding the maximum value, or the minimum values are often used in statistics and calculating the average document amount is also a useful indicator.
You can set 4 different types of filters on an Indicator:
- Static – This is a classic Business Central filter. You can use all the usual filtering syntax.
- Column Period – This will set a date filter defined by the column in the account schedule. The Row Type field is supported.
- Global Dim 1 – This will transfer the global dimension 1 filter from the account schedule header.
- Global Dim 2 – This will transfer the global dimension 2 filter from the account schedule header.
Only global dimension 1 and 2 are supported since they are the only dimensions represented as field throughout Business Central.
Testing your Indicator
When setting up an indicator you have the option to test the indicator right from the Indicator card to see that your filters are working and also to check the execution time. Some sum indicators can take a long time depending on how many records are with the filters (* Locating a field with SunFieldIndex support is often the solution if a Indicator takes too long to execute).
With the app comes an entire library of indicators that can used directly, or as inspiration:
With each indicator in the library comes a set of the right filters to apply:
You cannot use an Indicator on the first line of an Account Schedule. This is due to a limitation in the current version of Business Central. The limitation will be removed in an upcoming minor update from Microsoft.