As mentioned in our previous blog post, OData reporting is one of the ways to get reports in Microsoft Dynamics 365 Business Central. You can get the data directly from Business Central just by refreshing the data source in Excel or Power BI, without the need to log in to Business Central. Here’s how you can set it up:
Search for Web Services
“Web services are a lightweight, industry-standard way to make application functionality available to various external systems and users. Developers can create and publish functionality as web services, where they expose pages, codeunits, or queries, and even enhance a page web service by using an extension codeunit. When Business Central objects are published as web services, they're immediately available on the network.” - SOAP and ODATA Web Services - Business Central | Microsoft Learn
In Business Central, search for Web Services. This will show you a list of built-in Excel or Power BI refreshable reports. Open this listing in Excel and save it somewhere as a reference. Look for a query that looks interesting to you based on the description.
For a quick understanding, let’s take an example and get an Excel refreshable list that has all your G/L entries in it. In the Excel sheet, search on the Object Name here for G/L entries.
Copy the “OData V4” URL for G/L Entries and then in the Excel, go toData > Get Data > From Other Sources > OData Feed.
Paste the copied URL here. It may ask you for your credentials. If it does, click on Organizational account > Sign in.
Sign in with your Office 365 credentials and then click Connect. You only have to do this once, the first time you create the query. After this step, it goes real-time to Business Central and pulls all your G/L entries into Excel.
It creates a query and from here you can create a pivot table, add custom functions, etc. The best part is that after you save this to your desktop, all you need to do is right-click and click Refresh and it will always pull the latest data from Business Central.
Necessary cookies are absolutely essential for the website to function properly. This category only includes cookies that ensures basic functionalities and security features of the website. These cookies do not store any personal information.
Any cookies that may not be particularly necessary for the website to function and is used specifically to collect user personal data via analytics, ads, other embedded contents are termed as non-necessary cookies. It is mandatory to procure user consent prior to running these cookies on your website.