Ad-hoc data analysis

Path: Analytics, business intelligence, and reporting > Ad-hoc data analysis · tier official · system reporting · narrative reviewed (checked by Opus)

Overview

Ad-hoc data analysis is about exploring Business Central data straight from list pages and queries, instead of building or running a report. The tools range from basic ones (sort, search, filter, saved list views) to data analysis mode and Excel-based options.

Start with the overview page, then read the page on sorting, searching and filtering, which sets the base for everything else. Next, look at saving and personalizing list views, and at data analysis mode for grouping and pivoting. For work outside Business Central, use the Excel pages: Open in Excel and Edit in Excel for list data, and the Excel and XML export for report datasets. That export suits developers and advanced users who troubleshoot reports. A functional-area page links to analysis resources for specific areas.

Key points

• Sorting, searching and filtering are the base tools. Filters support operators (>, <, =, <>, |, .., &, *, ?) and tokens such as %me, %user, %mycustomers, %myitems and %myvendors.
• Search comes in a modern word-matching mode and a legacy exact-matching mode.
• List views save filter setups. You can rename, remove, personalize and bookmark them on the Role Center, and they are reusable across devices.
• Data analysis mode works on list page and query data. It has a Columns pane, an Analysis Filters pane, row groups, pivot mode and date hierarchies. You can add fields from related tables and keep several analysis views.
• Analysis views can be bookmarked to the Role Center.
• Open in Excel and Edit in Excel let you export list data, or change it and publish it back to Business Central. They support filters, the Excel add-in and multiple environments.
• Report datasets can be exported as data-only Excel workbooks or XML files. The Excel export includes a report metadata worksheet and filter information.
• A page of links points to ad-hoc analysis resources by functional area.

Learn pages

• Ad-hoc data analysis: Provides an overview of features that support ad-hoc data analyses in Business Central.
• Ad-hoc data analysis by functional area: Provides an overview of functional area specific landing pages for ad-hoc data analysis in Business Central.
• Analyze list page and query data using data analysis: Learn how to use the analysis mode in Business Central to analyze data.
• Analyzing report data with Excel and XML: Learn how to use Excel and XML to analyze a report dataset.
• Save and personalize List Views: Learn how to create your own views for filtered lists and how to save, rename, and manage those views.
• Sort, search, and filter data in lists, reports, or XMLports: Learn to work efficiently in lists by searching across your data, sorting columns, and refining results using filter symbols and keyboard shortcuts.
• Viewing and editing in Excel from Business Central: Learn how to open pages in Microsoft Excel from Business Central for better data analysis.

Videos and posts

Linked by a Haiku matcher with a grounding quote from the item’s summary (link/topics.ts); machine-generated.

• Analysis Mode killed half the Excel exports on your client’s shared drive (community post): “lets users group, filter, and pivot data on Business Central list pages”
• Dynamics GP to Business Central – The Arrival of Smartlist Features via “Add Fields from Related Tables in Analysis Mode” (community post): “Add fields from related tables in Analysis mode after activating it”
• How Do I: Analyze List Pages and Data Using Analysis Mode in Business Central (community post): “Analysis Mode is a built-in Business Central feature that lets users interactively explore list data”
• How Do I: Replace Reports with Analysis Mode in Business Central (community post): “Analysis Mode works best for answering questions rather than producing”
• What’s New: Enhanced Analysis Mode (2026 release wave 1) (video): “analysis mode; analysis views; al extensions; page extensions”
• What’s New: Server and Database - New Reporting Capabilities (2023 release wave 2) (video): “Data Analysis on Queries; Analysis views on queries”
• Deploy Analysis Views with AL Code in Business Central 2026 Wave 1 (No Manual Setup) (video): “Ad hoc analysis mode; Analysis view dimensions and pivoting”
• Business Central v29: System Audit Fields Now Available in Analysis Mode (video): “System audit fields in profile customization; Audit fields in analysis mode”
• What’s Cooking in Business Central: Wanna Filter on Totals in Analysis Mode? (video): “analysis mode; data analysis; filtering; totals; aggregated columns”
• What’s Cooking in Business Central: Wanna Export Data to Excel in Analysis Mode? (video): “Excel Export via Right-Click; Excel Export via Data Analysis Tab Menu”
• Introducing: Analyze Data on Lists and Queries (2023 release wave 2) (video): “Analyze data on list pages; Analyze data on query objects”
• What’s New: Data Analysis (2025 release wave 1) (video): “analysis mode; related tables; data analysis; excel export; list pages”
• What’s New: Data Analysis (2025 release wave 2) (video): “data analysis; analysis mode; copilot; analysis assist; pivot; date hierarchy”
• What’s new: Data Analysis (2026 release wave 2) (video): “System fields in analysis mode; Bookmark analysis tab”

Business Central pages and reports

The pages, reports and queries this hub’s Learn pages name (ms.search.form, API reference), joined to the code by exact object id (data/index/docs-objects.json); a page shows the table it runs on.

• Page 16 “Chart of Accounts” · on Table 15 “G/L Account”
• Page 22 “Customer List” · captioned “Customers” · on Table 18 “Customer”
• Page 25 “Customer Ledger Entries” · on Table 21 “Cust. Ledger Entry”
• Page 26 “Vendor Card” · on Table 23 “Vendor”
• Page 27 “Vendor List” · captioned “Vendors” · on Table 23 “Vendor”
• Page 31 “Item List” · captioned “Items” · on Table 27 “Item”
• Page 143 “Posted Sales Invoices” · on Table 112 “Sales Invoice Header”
• Page 144 “Posted Sales Credit Memos” · on Table 114 “Sales Cr.Memo Header”
• Page 456 “No. Series” · on Table 308 “No. Series”
• Page 457 “No. Series Lines” · on Table 309 “No. Series Line”
• Page 458 “No. Series Relationships” · on Table 310 “No. Series Relationship”
• Page 459 “Sales & Receivables Setup” · on Table 311 “Sales & Receivables Setup”
• Page 460 “Purchases & Payables Setup” · on Table 312 “Purchases & Payables Setup”
• Page 461 “Inventory Setup” · on Table 313 “Inventory Setup”
• Page 1480 “Excel Centralized Depl. Wizard” · captioned “Excel Add-in Centralized Deployment” · on Table 1480 “Edit in Excel Settings”
• Page 9300 “Sales Quotes” · on Table 36 “Sales Header”
• Page 9301 “Sales Invoice List” · captioned “Sales Invoices” · on Table 36 “Sales Header”
• Page 9303 “Blanket Sales Orders” · on Table 36 “Sales Header”
• Page 9304 “Sales Return Order List” · captioned “Sales Return Orders” · on Table 36 “Sales Header”
• Page 9305 “Sales Order List” · captioned “Sales Orders” · on Table 36 “Sales Header”
• Page 9306 “Purchase Quotes” · on Table 38 “Purchase Header”
• Page 9307 “Purchase Order List” · captioned “Purchase Orders” · on Table 38 “Purchase Header”
• Page 9309 “Purchase Credit Memos” · on Table 38 “Purchase Header”
• Page 9310 “Blanket Purchase Orders” · on Table 38 “Purchase Header”
• Page 9311 “Purchase Return Order List” · captioned “Purchase Return Orders” · on Table 38 “Purchase Header”

Source: Microsoft Learn (CC BY 4.0). Descriptions are Learn’s own.