In Microsoft Dynamics 365 Business Central, the account schedules are available in a nice almost printable format especially if you know how to export it and refresh it with your own formatting. But sometimes, you need the raw data quickly in a Pivot Table format or a Power BI refreshable report for data analysis.
To get this data, navigate to the ‘Account Schedule KPI Web Service Setup’ page. Here, you can select the account schedules you want to expose to Excel/Power BI. By default, it just includes the ones that come with the standard out of the box. If you have any custom ones, which most of the companies do, you will need to add it here. You can look at the ‘Help’ to see some of the options available on this page. For example- the View By determines if we want it by month/quarter/year/week/day.
Once you have this setup and have selected the Account Schedules that you want to expose, navigate to the Web Services page. Search for Account Schedule KPI Web Service (Object ID 197). Copy the OData URL and create connection in Excel or Power BI.
In Excel, click on > Data > Get Data > From Other Sources > From Odata Feed > Paste it in there. If you’ve done this before, it will automatically show you the account schedules and give you an option to Load. If not, there’ll be an authentication, click on Organizational Account and then enter your Microsoft credentials- the same ones that you use to login to Business Central. You’ll see all the lines that you have in Account Schedules by period.
It is a list of the four that we selected. It will have a bunch of different values like Net Change for your P&L, Balance Sheet at that particular date, change versus last year, etc. The neat thing about this is all the calculations that you have in that account schedule are already calculated in here.
You can now create a pivot table. The screenshot below shows a simple summarized example where it includes revenue, cost of sales, gross profit and expense. If you want, you can create a more detailed one with G&A, by departments or by functional area. You can just refresh the data from here.
This is a quick way to get your account schedules into excel in more of a pivot table format. You may find this very useful for data analysis.
For more information on the other functionalities in Dynamics 365 Business Central, contact us.
– Jason has over 20 years of financial leadership experience in high growth technology companies. He is a Microsoft Certified Dynamics 365 Business Central Functional Consultant Associate. See Credential.
