Skip to main content

Build Business Central Reports with Excel in Minutes

Published Date
Author Michael Intravartolo

Table of Contents

Key Article Takeaways

  • Connect Excel to Business Central via OData for live reporting.

  • Use Power Query to filter by date and transaction type.

  • Calculate gross profit and margin using Excel formulas.

  • Pull customer and item data with VLOOKUPs.

  • Refresh reports easily with one click.

For Business Central users, Microsoft Excel remains one of the most familiar and flexible tools for reporting. When paired with live OData feeds from Business Central, Excel transforms from a static spreadsheet into a dynamic reporting engine. Whether you're reviewing sales data, tracking inventory, or analyzing costs, this integration empowers you to work with real-time data without needing to export or manually update reports.

Watch the Step-by-Step Video Tutorial


Prefer to follow along visually? Watch this quick video walkthrough of building a live Excel report from Business Central using OData links. It covers everything from setting up web services to calculating gross margin, perfect for first-timers and pros alike.

Creating Reports with Excel in Business Central: Live Data Using OData

Setting Up OData Links in Business Central

Create a Web Service for the Item Ledger Entries

Start by navigating to the Web Services page in Business Central. Here, you can either use an existing query or create a new one. For this tutorial, we create a new page by selecting "Page" as the object type and searching for the Item Ledger Entries page (ID 38). Assign a name like ItemLedgerEntriesDemoReport and click Publish. A unique OData URL will appear—copy it for use in Excel.

Copy the OData Link and Connect in Excel

In Excel, go to the Data ribbon and select Get Data > From Other Sources > OData Feed. Paste the URL you copied and click OK. If prompted, sign in with your organizational account to authenticate access.

If errors occur, go to Data > Data Source Settings and clear any active permissions. Furthermore, clear the cache under Query Options to ensure a smooth connection.

Building a Live Report in Excel

Filtering and Formatting Data with Power After establishing the connection, Power Query Editor presents

After establishing the connection, Power Query Editor presents a preview of the Item Ledger data. Filter the Posting Date to a specific range (e.g., July 1 to July 31, 2022) and apply a filter on the Entry Type to show only sales transactions. Click Close & Load to bring this filtered data into Excel. Rename the query and worksheet for easier reference.

Calculating Gross Profit and Margin

From the loaded data, pull in key columns: Document Number, Item Number, Quantity, Sales Amount Actual, and Cost Amount Actual. Use Excel formulas to calculate:

  • Gross Profit = Sales Amount + (Negative Cost Amount)

  • Gross Margin = Gross Profit / Sales Amount

These formulas turn your raw data into actionable insights.

Adding Extra Details to Your Report

Pull Item Descriptions from the Items Table

Create another Web Service for the Items page (ID 31). Publish it and use the OData link to connect in Excel just like before. Once loaded, use VLOOKUP to bring in the Item Description based on the Item Number in your main report.

Add Customer Names from Posted Sales Shipments

Repeat the process for Posted Sales Shipments (Page ID 130). After loading this data into Excel, filter by the same Posting Date range. Then, use VLOOKUP to match the Document Number and pull the Sell-to Customer Name into your main report.

Refreshing and Updating Your Report

Once you’ve fully built your Excel report, refreshing it is simple. Go to the Data ribbon and click Refresh All. Excel will automatically pull the latest data from Business Central through the OData connections, just like Power BI.

To avoid displaying errors or unnecessary zero values, consider wrapping your formulas in IFERROR statements or using conditional formatting to clean up your layout.

Final Thoughts

By integrating Excel with Business Central via OData feeds, you can unlock real-time, customizable reporting without ever leaving your spreadsheet environment. From sales metrics to customer insights, your reports stay fresh, accurate, and tailored to your needs—all with a few clicks.

Are you seeking to enhance your Business Central reporting capabilities? Western Computer’s experts help you maximize the power of Excel reporting through Business Central. Whether you're building your first live report or scaling enterprise-wide analytics, our team can help you get there faster and smarter.

Explore More Business Central Tips and Tricks

Have questions or want help with your Business Central solution? Contact us today.