Finance teams often need to move beyond summary figures and understand exactly what is behind a number. With , Excel, and , users can build summary reports in Excel and drill directly into the Business Central transactions that make up each figure, without exporting data or rebuilding reports. Here's how to drill down to Business Central transactions in Excel in six steps.
The process takes six basic steps: connect Excel to Business Central, select the required tables and fields, build a summary report, configure drill-down functionality, review the underlying transaction details in Excel, and refresh or share the finished report.
Key Highlights
- Excel can connect directly to Dynamics 365 Business Central data through the Exsion365 Add-in
- Summary figures can be linked to the individual Business Central transactions behind them.
- Drill-down results can be filtered, sorted, analyzed, and saved using familiar Excel functionality.
- Existing Business Central permissions continue to apply when data is accessed through Excel.
- Exsion365 supports multi-company reporting and lets users refresh reports without rebuilding them.
How Do You Drill Down to Transaction Details in Excel with Dynamics 365 BC?
The basic process for drilling down from a summary figure to transaction-level detail involves six steps:
- Connect Excel to Business Central. Establish a live connection between Excel and your Business Central environment.
- Select the required tables and fields. Choose the Business Central data needed for the report.
- Build the summary report in Excel. Organize the data using aggregations such as sums, counts, or averages.
- Configure drill-down functionality. Connect summary cells to the transactions that support their values.
- Open transaction-level detail. Select a summary figure and view the individual posted transactions behind it.
- Refresh and share the report. Keep the report current with a one-click refresh and convert live formulas to values when sharing a static copy.
This approach gives finance users a practical way to investigate the details behind Business Central financial data while continuing to work in Excel.
How Do You Connect Excel to Dynamics 365 Business Central?
The first step is establishing a live connection between Excel and your Business Central environment. An Excel add-in that reads directly from the Business Central database lets users work with BC data without exporting it first.
After installing the Exsion365 Excel Add-in, open Excel and use the add-in ribbon to create a new connection. Enter your Business Central Environment URL and authenticate using your Microsoft credentials.
The connection respects existing Business Central user permissions. If you can access a customer record in Business Central, you can pull that data into Excel. If your Business Central permissions do not allow access, the add-in will not make that information available.
Which Business Central Tables and Fields Should You Use?
Once Excel is connected to Business Central, select the tables and fields needed for the report. Financial drill-down reports will typically begin with the and may incorporate related tables such as Customer Ledger Entries or Vendor Ledger Entries.
Selecting only the fields required for the analysis can also help keep data refreshes efficient. For example, a P&L report might use Account Number, Posting Date, Amount, and Document Number.
Related tables can provide additional context. Joining the G/L Entry table to the Item Ledger Entry table, for example, can show both the accounting entry and the inventory movement associated with it.
How Do You Build a Business Central Summary Report in Excel?
After selecting the data, structure the Excel report around the aggregated figures you want to analyze. Excel's standard SUM, COUNT, and AVERAGE functions can be used with Business Central data, along with the add-in's built-in aggregation methods.
Group the information according to the dimensions that matter to the analysis. Depending on the report, that could include account category, cost centre, project code, or time period. A typical management report might use monthly columns with rows grouped by account or department.
The summary layout also matters when drill-down functionality is involved. Each cell should represent a single aggregated value that can later be traced to its underlying transactions. Avoid merged cells or complicated nested formulas that could interfere with drill-down navigation.
How Does Transaction Drill-Down in Excel Work in Business Central?
Drill-down connects an aggregated Excel value to the Business Central transactions that make up that amount. The Exsion Excel Add-in for Business Central includes a "Show Details" ("Details weergeven" in the Dutch UI) feature that provides this functionality.
When you position your cursor on a summary cell, the add-in's task pane displays the filters applied to that particular value. This can show which accounts, date ranges, and dimension values contributed to the total.
For customized drill-down paths, you can also define the fields displayed in the transaction list. Depending on the purpose of the investigation, you might include Document Number, Posting Date, Description, and Amount.
How Do You View Transaction-Level Detail in Business Central from Excel?
Once drill-down is configured, select a summary cell and click the "Drilldown" button in the Exsion365 add-in ribbon. The result is a new report showing the individual transactions that make up the selected total.
Depending on your settings, the results can open in a new browser tab or an Excel sheet. Each row represents a posted Business Central transaction and includes the detail fields selected for the drill-down report.
Because the results are standard Excel data, you can use familiar Excel filters and sorting tools to investigate the transactions. This makes it easier to identify unusual entries, spot patterns, or isolate records for further review.
How Do You Refresh and Share Business Central Excel Reports?
A report is only useful if the figures are current. Exsion365 provides a "Refresh Data" ("Gegevens vernieuwen" in the Dutch UI) function that pulls the latest Business Central information into the existing Excel report.
The one-click refresh updates the summary figures while maintaining the drill-down connections. There is no need to rebuild the report or export the data from Business Central again.
When a report needs to be shared with colleagues who do not have the add-in, the "Formulas to values" feature can replace the live Business Central connections with static values. This creates a standard Excel file that others can open. It is useful to retain a copy with the original formulas so the live report can be refreshed again later.
What Is the Difference Between Drill-Down and Drill-Through?
Drill-down and drill-through both provide additional detail, but they work differently.
Drill-down moves vertically through a data hierarchy while maintaining the same report context. For example, a user might move from annual totals to quarterly, monthly, and daily figures.
Drill-through moves horizontally into a different report. Selecting a revenue figure might open a separate sales order report showing the orders responsible for that revenue. The destination report has its own layout and purpose.
For Business Central finance teams, transaction-level drill-down can be particularly useful when the question is why a specific number changed. If travel expenses increased, for example, the finance team can examine the actual posted entries behind the total rather than moving to another summary report.
Why Use Excel for Business Central Financial Analysis?
Business Central provides financial reporting capabilities, but those reports operate within the Business Central interface. For finance professionals who already use Excel for analysis, an Excel-based approach can keep the reporting process within a familiar working environment.
Excel provides the formulas, formatting, filtering, and analysis tools that financial controllers already know. Adding Business Central data to that environment means users can work with BC information without learning an entirely different reporting interface.
An Excel-based approach can also combine Business Central information with data from other sources in the same workbook. For example, a drill-down report could combine general ledger entries with budget figures from a planning system or headcount information from HR.
How Does Exsion365 Support Drill-Down Reporting for Business Central?
Exsion365 connects Excel directly to Dynamics 365 Business Central data without requiring exports or intermediate steps. The Exsion Excel Add-in provides access to the tables and fields in the Business Central database while allowing users to build reports using familiar Excel functionality.
Users can define data sources, apply filters, create aggregations, and format reports in Excel. Exsion365 provides the connection to Business Central while the user maintains control over how the information is presented.
Drilling into transaction-level detail is also designed to be straightforward. Select a summary cell, choose Drilldown, and review the posted entries contributing to the selected figure.
Exsion365 also supports multi-company reporting. Organizations managing multiple Business Central entities can build consolidated reports and drill into transactions associated with individual companies.
Frequently Asked Questions About Transaction Details in Excel for Business Central
Q: Can I drill down to transactions across multiple Business Central companies?
A: Yes. Exsion365 supports multi-company reporting, allowing users to create a consolidated summary and drill into transactions from individual entities. The drill-down results identify the company associated with each transaction.
Q: Do I need special permissions to use drill-down in Excel for Business Central?
A: Drill-down access follows existing Business Central user permissions. If you can view a specific table or record in Business Central, you can access that information through the Excel Add-in. Exsion365 does not bypass Business Central security settings.
Q: How quickly does Exsion365 return transaction-level detail?
A: Exsion365 typically returns transaction lists within seconds, including for large datasets. Actual performance depends on the Business Central environment and the number of records being retrieved.
Q: Can I save drill-down results for audit documentation?
A: Yes. Drill-down results appear as standard Excel data and can be saved, printed, or copied into audit workpapers. The "Formulas to values" feature can convert live connections into static data for archiving.
Q: What happens if Business Central data changes after I drill down?
A: The drill-down results represent the data available when the drill-down was performed. If transactions are subsequently posted or modified, those changes will not appear until the summary report is refreshed and the drill-down is performed again. This snapshot behavior can also help document the information reviewed at a particular point in time.
What Is the Next Step for Excel-Based Business Central Reporting?
For finance teams that rely on Excel but need more direct access to Dynamics 365 Business Central data, drill-down reporting provides a practical way to connect summary analysis with the transactions behind it.
Reviewing how your current Business Central reports are built, where data exports occur, and how users investigate individual transactions can help determine whether an Excel-based reporting approach with Exsion365 fits your needs. A demonstration using your own Business Central reporting requirements can provide a practical way to evaluate the approach.
Would you like to know more about drilling down to transaction details in Excel with D365 BC? our experts at Exsion365 and ask for your free trial.
By Exsion365, exsion365.com
