Using OData with Business Central

By Rob Jolliffe | July 15, 2020
5 min reading time

Using Excel to analyze data with OData with Business Central - An Example

We get a lot of customers who ask about reporting on information from within their Business Central software. Those who come from older software packages usually think of a report as something that prints on a page, out of a laser printer, in a formatted way. The modern approach to ERP is getting away from that environmentally unfriendly, and frankly less useful form of reporting.

Today people tend to report more often on a screen, whether it be a tablet, phone or PC. The typical end-user wants to both see their data, but also do some slicing and dicing. Analysis of that information is very important. The term Business Intelligence originally referred to reporting tools that were on-line, with the ability to filter and analyze in real-time.

If you want to pull data out of Business Central for analysis you should be looking at OData. You may be familiar with the old ODBC tools for pulling data out of databases and analyzing it. OData is the web friendly version of ODBC - and is supported by most of the same tools you used. We usually rely on Excel or a similar OData aware program when using OData with Business Central.

The most important of those tools is Microsoft Excel.

Here's a quick primer on using OData with Business Central and Excel to do a little slicing and dicing.

First : Find your data

When you want a report out of Business Central, you probably know where the data you want can be found. As you are using the software, make a note of any page you want to report on. You can use the Ctrl-Alt-F1 key combination while in Dynamics to see the "Page Inspection." This will give you a Page name and number and type. I ran this for the Item List and see this:

Second: Make a Web Service

A Web Service is the primary way of accessing OData with Business Central. Within Business Central, search for the Web Services page. There are a lot of pre-existing web services, and it's best to use those if you can. Often the data you want isn't found in the Web Services. No Problem! Add the page(s) you need to the web services list. Let's add the Item List. I'll add a New Web Service on the Web Services page. Select Object Type of Page. Enter (in my case) ID 31 for the Item List. Then I need to name it and publish it (the check box to the right of the Service Name). It looks like this when I'm done:

configuring a web service for using OData with Business Central.

When I reload the page, the service my_item_list will now have an ODATA URL. Make sure you're logged into the right company. Right click and "copy link" if you use Edge. My experience is to use ODATA not ODATA V4 in Excel.

Third: Import into Excel

Now that we have an ODATA link, let's import it into Excel.

I'll open a blank worksheet.

In the Ribbon - go to Data, and under the Get and Transform Data select Get Data | From Other Sources | From OData Feed:

using OData with Business Central in excel

Paste in the URL:

You will be prompted for login information.  If your administrators have setup your computer so that your windows user is your Business Central user, then select Windows.   

Otherwise you probably want an Organizational Account. 

After you have logged in successfully, you'll see a "Data List" like the one below: 

This list of data is is directly out of the system, and is the key using OData with Business Central.

Click Load and it will populate your current Worksheet: 

pulling OData with Business Central into an excel sheet

The most important feature of this now is that you can refresh the data.  Click anywhere inside the Excel table that's been downloaded.  Click the Query ribbon and you will find a Refresh icon. 

Finally: Do Some Reporting

From here you can now run Power Query to join this ODATA to other tables, use Pivot Tables to slice and dice, use pivot charts to visualize your data … the sky is the limit with Excel. 

The bigger question is - what are some valuable analysis I should do?  For instance, I am a big believer in doing some statistical analysis of inventory transaction history (Item ledger entries) to find out what my average demand per period is, or my standard deviation of material usage.  These are necessary pieces of data for calculating safety stock or lead times.  This kind of data analysis is something I'll cover in a future post. 

If you need help using OData with Business Central., call us at (519) 585-7524 x.45 or contact us, at Sabre Limited we are happy to help!

Rob Jolliffe

Robert has been an ERP consultant in the Manufacturing space for over 25 years, starting immediately after graduating University of Toronto Engineering. In addition to a deep knowledge of Manufacturing (including teaching MRP at the Supply Chain program at Conestoga College in Kitchener, Ontario, Canada) Robert holds a Microsoft Systems Engineer designation and is much less of an expert in Networking and IT infrastructure than he thinks, but is still pretty good. He also has applied his engineering skills to learning programming, and is warned frequently by the professional developers who work for him that he is pretty good, but don't write any code for customers without letting them check it.

Related posts

4 Barcode Warehouse Management Mistakes
November 20, 2019 by Rob Jolliffe | 4 min reading
Many companies have punch clocks at their doors in order to collect attendance data for their payroll systems. And when they consider installing a shop floor data collection system they do not understand why when they are already collecting time at the door. There are so many ways that data collection can improve a business by connecting to your ERP.
Explaining MRP In Dynamics Business Central
October 23, 2019 by Rob Jolliffe | 6 min reading
In my years of working with companies looking for ERP products I have found that some embrace the idea of going paperless while others will not even consider it. It is good that you can implement a good ERP and CRM package in a manner to be either paperless or not or a combination of the two.
Copyright © 2020 SabreLimited.com