This website uses cookies.
Please click the consent button to view this website.
I accept
Deny cookies Go Back

Tensoft

Business Central: Modern Business Solution for Your Modern Business

  • Home
  • Capabilities
  • Demos
  • Pricing
  • Implementation
    • DIY+ Implementation
    • Fast Start Implementation
  • Add-ons
    • Kind Connect
    • Microsoft Power BI
    • Jet Reports
  • Why Tensoft
  • Blog
  • Contact Us

By Jason Ochipa • Oct 21 2021

Microsoft Dynamics 365 Business Central: Account Schedules Excel Refreshable Reports

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.

Account Schedules

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.

Account Schedules

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.

Print Friendly, PDF & Email

Tagged: account schedules, Business Central Reporting, excel, Microsoft Dynamics 365 Business Central, Microsoft Power BI, reporting

Get Started Quickly

Sign up for a free trial today and learn how you and your company can benefit.

Start a Free Trial

  • Kind Connect
  • Blog
  • Demo Videos
  • Contact Us
  • Privacy Policy
  • Cookie Policy

Questions? Email solutions@tensoft.com or phone us at +1 408.824.9301 © 2023 Tensoft, Inc. All Right Reserved.