In this article you will learn how account schedules work and how to create a simple one in Business Central. Also, you will learn about the ‘Account Schedule KPI Web Service Setup’. A tool to publish any account schedule data as a web service.
- What Are Account Schedules?
- Row Setup
- Column Setup
- Account Schedule Example Result
- Account Schedule KPI Web Service Setup
- Account Schedule KPI Web Service
What Are Account Schedules In Business Central?
Account Schedules are the tool to represent finnancial data in Business Central. An example can be the Balance Sheet or the Income Statement. We have the possibility to create our own finnancial reports of the Chart of Accounts and Budgets. Thanks to the Row-Column system we can create a matrix to compare data between different peridos and calculate ratios.
Row Setup
When we look for ‘Account Schedules’ we will see a page with a list of all account schedules available in our company. Out of the box, Business Central provides some examples. But first, we will create a simple one for better understanding. Let´s create ‘EASY R’:
When we open it, it look´s like the following image. On top we don´t have a header, just the name of the account schedule.
Below we see a list with different fields. It is in this list where we define the rows of our account schedule. For every row we insert we will get a row in the resulted dataset.
Keep in mind that an account schedule is a matrix, a combination of rows and columns. In this section we are just preparing the rows.
Let´s create a simple row setup. We want 3 rows:
- We want to see the amount for the ‘40100’ account which is my Income, Services. We need to provide a ‘Row No.’. It is an alfanumeric value, let´s name it ’01’.
- For my Income, Product Sales or ‘40200’ account we will name it as ’02’.
- If we want to know the total of both amounts we can add a formula and specify the values of the rows (01+02).
There are several fields like the Totaling Type, Row Type, Amount Type, etc that specifies what you want to show. For this example we will have the following setup.
Column Setup
We know that we want to display Services and Product Sales. But when? In the column section we specify how many columns will appear in the report. And what periods it will calculate.
The name of the account schedule columns is ‘Column Layout’. Let´s open it.
We can play with the options of the Coulmn Type. And here we can select Entries or Budget entries. For this example we will get two columns.
- Actual amount.
- Previous year amount.
We can play with the ‘Comparison Date Formula’ to calculate the periods. Learn more about account schedules and formulas here.
https://docs.microsoft.com/en-us/dynamics365/business-central/bi-how-work-account-schedule
Finally, in the account schedule list we can choose the default column layout for our rows. This way, every time we run ‘EASY R’ it will do it with ‘EASY C’.
Account Schedule Example Result
From ‘EASY R’ we can select ‘Overview’ and we will have access to the result of our account schedule.
As we have setted up earlier. There are three rows and two columns of data. The ‘Description’ column will always be visible.
In this page we have serveral options available. A ‘General’ tab and a ‘Dimension Filters’ tab.
We can play on how to view the data. By Year, Quarter, Month, etc. We can choose a date filter or apply a specific dimension filter.
Important. We can choose any account schedule column layout for our rows and vice versa. We have the flexibility to mix and use a column layout in other account schedules if we want. They are not binded to each other. Only in some cases of complex formulas this combination might not work.
We can dig further into the data by clicking on the amounts of the accounts. Keep in mind that it´s not possible to drill down on formulas.
This will take us to the chart of accounts filtered by account type, ‘No.’ and date.
If we select last year amount we can see that the date is of the previous year.
Business Central comes with predefined Account Schedules where we can find multiple combinations. As you can see in the Balance sheet, we can indent rows, play with the totalling to add a range of accounts and many more possibilities.
Account Schedule KPI Web Service Setup
This is a tool that enables to publish web services of account schedule´s data. Then, external tools such as PowerBI can use the data to create graphics and more visual representations.
The ‘Account Schedule KPI Web Service Setup’ looks like the image below. We have setted up ‘EASY R’ as an example.
Account Schedule KPI Web Service
When we publish the web service we can see it on the web services page.
We can also access the data from the account schedule ‘KPI Web Service’ page.
You can learn more about Web Services and APIs in this article.
That´s all. Hope you find it useful.