🚀 Build Custom Excel Reports in Business Central—No Code Required!

-

Hi Readers, hope you're having a great day!

Have you ever been asked by a client or ERP Manager:

“Can we create Excel reports ourselves, in the format we need, using the data we want?”

It’s a common request—and a valid one. Users want flexibility to build reports tailored to their needs, without relying on developers or technical customization.

Initially, I suggested using the “Open in Excel” feature. But that’s limited to the fields available on a single page and table—it doesn’t truly generate a report. Another option is customizing Excel reports, but that requires technical effort and doesn’t scale well when different users want different formats or data sources.

So, I decided to build a solution.

🛠️ Introducing the Excel Reporting Tool for D365 Business Central

This tool empowers Business Central users, ERP Managers, and Consultants to create Excel reports directly from the Business Central UI—without writing a single line of code.

Yes, you read that right: No Code. No Cost. Just Click and Create.

✨ What Can You Do With This Tool?

  • ✅ Create unlimited reports and run them anytime

  • 🔄 Export data from multiple tables (unlike “Open in Excel” or Configuration Package)

  • 📊 Use up to 100 tables per report

  • 📈 Include up to 16,384 columns and 1,048,576 rows (Excel’s max limits)

  • 🔐 Access restricted tables like Company, User, Access Control, AllObj, Session, Fields, Table Metadata and more

  • 🔗 Define relationships between tables for iterative reports (e.g., Sales Register, Customer Ledger)

  • 🎨 Customize column captions, file names, and report structure—your way

🧩 Tool Overview: How It Works

📋 Report List

Displays all configured report codes. You can create as many reports as you want—just keep the codes unique.

🧾 Report Card

This is the configuration page where you build your report. It includes four FastTabs:

1. General Tab

Define basic report details:

  • Report Code – Unique identifier for your report

  • Description – Purpose or notes about the report

  • Caption – Display name of the report

  • Max Rows – Maximum number of rows to generate

2. Data Tables

Specify the tables to pull data from:

  • Table ID & Caption – Select one or more tables

  • IsSubDataTable & Main Data Table ID – Define relationships for nested or iterative reports

3. Data Table Filters

Apply filters to refine your data, it can be to link Tables or ad-hoc runtime filters :

  • Report Table ID & Field ID – Define the Table and Field Where the filter will be applied

  • Filter Type – Fixed value or dynamic lookup

  • Lookup Table & Field ID – Define Source of dynamic filter value, table and field

4. Data Columns

Define the columns to include in your report:

  • ID No. – Unique column ID and decides sequence

  • Column Caption – Header name in Excel columns

  • Report Table & Field ID – Source of the column data

🎉 Generate Your Report—Instantly

Once configured, just click “Generate Report” and voilà—your Excel file is ready.

You can now:

  • Create pivot tables

  • Build charts and slicers

  • Analyze data with ease

  • Re-run the report anytime with updated data—same format, same structure

📚 Real-World Use Cases

🔹 Sales Register Report

Combines data from Customer, Ledger Entry, and Sales Invoice tables for a comprehensive view.



🔹 Permissions Analysis

Generates a report showing all user permissions—perfect for audits and role reviews.


📬 Want to Try It?

Interested in creating powerful reports and saving on technical effort? Connect with me at dhirennagar97@gmail.com or on LinkedIn

🚧 What’s Next?

I’m currently working on the next version of this tool with even more features. Stay tuned for updates!

Until then—Happy Reading!

Comments

Post a Comment

Popular posts from this blog

Configure Bank Statement Import through Data Exchange Definition Part 1

-
Image
Hello Readers, Greetings to you. Time for new blog and new learning! In this blog I am going to showcase you the process to configure Bank Statement Import functionality in Business Central through Data Exchange Definition. In Business Central when user is required to perform Bank Reconciliation process, user needs to enter bank statement lines manually. There is an option of "Suggest Lines", but it will just copy the Bank Account ledger entries on Bank Statement lines. If there is any difference in bank statement and Bank Account ledger entries, then user needs to manually enter, delete or change the amount as needed to post the reconciliation. Through Bank Statement Import setup you can import CSV file of bank statement that is provided by your bank directly into Business Central. This functionality is available in both Bank Account Reconciliation and Payment Reconciliation Journal pages. User can decide which of the two methods to use for posting reconciliation. Business C...
Read more

Security Filter in Business Central and its limitations

-
Image
Greetings Readers, Today I'll write about a very useful functionality of Business Central - Security Filter in Permission set. Introduction: In Business, many times we come across scenarios where user's rights are restricted to Enter, Access, Modify and Delete, all or some of the data in ERP. These requirements arise during implementation of Standard operating procedures and Delegation of Authority decided by Business and Management. Business central provides Permission set, Profile, Security Group, User setting etc. features to make sure these requirements are taken care of during the implementation. As all these options takes care of Objects and data that a user can access or profiles of the user there is a very important functionality of Security Filter which helps to restrict access to only certain records based on some predefined filter values. Business Use Case: If a business wants to restrict a user's access to customer data then Customer table rights can be restrict...
Read more

Configure Bank Statement Import through Data Exchange Definition Part 3

-
Image
Greetings, Readers, I have previously published two blogs on this particular topic, and this blog serves as the third installment. Before proceeding, I encourage you to read the first and second parts. Here are the links to my previous blogs: Part 1:  Configure Bank Statement Import through Data Exchange Definition Part 1 (dhirennagar.blogspot.com) Part 2:  Configure Bank Statement Import through Data Exchange Definition Part 2 (dhirennagar.blogspot.com) In this blog post, we will look into the process of configuring a Bank Statement that includes the symbols "Dr" and "Cr" instead of separate columns for Debit and Credit amounts. The screenshot below illustrates a Bank Statement that requires configuration for import: Firstly, we need to create a Data Exchange Definition for this purpose. Navigate to the Data Exchange Definition page and select "New." In my previous blogs, I have already provided an explanation on how to create a Data Exchange Definition. ...
Read more