Ad-hoc analyses in purchasing

This article explains how to analyze purchasing data from list pages and queries using the Data Analysis feature. The feature lets you analyze data directly from the page, without having to run a report or open another application, such as Excel. Data Analysis provides an interactive and versatile way to calculate, summarize, and examine data. Instead of running reports using options and filters, you can add multiple tabs that represent different tasks or views on the data. Some examples are "My vendors," or "Purchasing statistics," or any other view you can imagine. Learn more in Analyze list and query data with analysis mode.

Use the following list pages for ad-hoc analysis of purchase processes:

Ad-hoc analysis scenarios for purchasing

Use the Data Analysis feature for quick fact checking and ad-hoc analysis:

  • If you don't want to run a report.
  • If there isn't a report for your specific needs.
  • If you want to quickly iterate to get a good overview on a part of your business.

The following sections provide examples of purchasing scenarios in Business Central.

AreaTo...Open this page in analysis modeUsing these fields
GRNI overviewGet a Goods Received, Not Invoiced (GRNI) overview across vendors.Purchase LinesType, Amt. Rcd. Not Invoiced (LCY) (filter on these fields), Buy-from Vendor No., Document No., No., and Amt. Rcd. Not Invoiced (LCY)

NOTE: You must personalize the page to add Amt. Rcd. Not Invoiced (LCY). Learn more in Personalize your workspace.
Finance (Accounts Payable)See what you owe your vendors, maybe broken down into time intervals for when amounts are due.Vendor Ledger EntriesVendor Name, Document Type, Document No., Due Date Year, Due Date Month, and Remaining Amount.
Finance (Accounts Payable)Get an overview of vendor ledger entries that have been marked On Hold.Vendor Ledger EntriesVendor Name, Document Type, Document No., On Hold, and Remaining Amount.

Note

Vendor Name is shown when Copy Vendor Name to Entries is enabled in Purchases & Payables Setup. For entries without a copied name, group by Vendor No. instead. Remaining Amount is in the entry's currency. Filter by Currency Code to avoid combining currencies, or use Remaining Amt. (LCY) to compare amounts in local currency.

Example: goods received, not invoiced (GRNI) overview

To create a goods received, not invoiced (GRNI) overview across vendors, follow these steps:

  1. Open the Purchase Lines list page.
  2. Personalize the page to add the Amt. Rcd. Not Invoiced (LCY) field. To personalize the page, choose Settings, and then Personalize.
  3. Choose Enter analysis mode to turn on analysis mode.
  4. Go to the Columns menu and remove all columns (select the box next to the Search field on the right).
  5. In the Analysis Filters menu (located below the Columns menu on the right), set the following filters:
    • Type = Item
    • Amt. Rcd. Not Invoiced (LCY) > 0.
  6. Drag the Buy-from Vendor No., Document No., and No. fields to the Row Groups area. Drag the fields in that order.
  7. Add the Amt. Rcd. Not Invoiced (LCY) field to include it on the overview.
  8. To do the analysis for a given year or quarter, apply a filter on Expected Receipt Date in the Analysis Filters menu. The menu is on the right of the page, just below the Columns menu.
  9. Rename your analysis tab to Goods Received, Not Invoiced (GRNI), or something that describes this analysis.

Note

This filter limits the current purchase lines to the expected receipt period. It doesn't show GRNI as of a historical date.

Example: finance (accounts payable)

To see what you owe your vendors, maybe broken down into time intervals for when amounts are due, follow these steps:

  1. Open the Vendor Ledger Entries list page, and turn on analysis mode.
  2. Go to the Columns menu and remove all columns (select the box next to the Search field on the right).
  3. Turn on the Pivot Mode toggle (located above the Search field on the right).
  4. Drag the Vendor Name, Document Type, and Document No. fields to the Row Groups area, and then drag the Remaining Amount field to the Values area.
  5. Drag the Due Date Year and Due Date Month fields to the Column Labels area. Drag the fields in that order.
  6. To do the analysis for a given year or quarter, apply a filter in the Analysis Filters menu (located below the Columns menu on the right).
  7. Rename your analysis tab to Aged Payable Accounts by Month, or something that describes this analysis.

The following image shows the result of these steps.

[Example of how to do data analysis on the Vendor Ledger Entries page.]

Example: finance (accounts payable) - vendor ledger entries on hold

To see vendor ledger entries on hold, follow these steps:

  1. Open the Vendor Ledger Entries list page, and turn on analysis mode.
  2. Go to the Columns menu and remove all columns (select the box next to the Search field on the right).
  3. Turn on the Pivot Mode toggle (located above the Search field on the right).
  4. Drag the Vendor Name, Document Type, and Document No. fields to the Row Groups area, and then drag the Remaining Amount field to the Values area.
  5. Drag the On Hold field to the Column Labels area.
  6. Rename your analysis tab to Payments On Hold, or something that describes this analysis.

The following image shows the result of these steps.

[Example of how to do data analysis on the Vendor Ledger Entries page to find On hold entries.]

Data foundation for ad-hoc analysis on purchasing

When you post a purchase document, Business Central can update the vendor's account, general ledger (G/L), item ledger entries, and resource ledger entries, depending on the posting options and line types:

  • When you invoice a purchase, entries are created in the G/L Entry table and the vendor's account in the Vendor Ledger Entry table, including a G/L entry in the relevant payables account. In addition, posting the purchase might result in a value-added tax (VAT) entry and a G/L entry for the discount amount.

  • For each purchase line, as applicable, entries are created in the:

    • Item Ledger Entry table, if the purchase line is of the Item type.
    • G/L Entry table, if the purchase line is of the G/L Account type.
    • Res. Ledger Entry table, if the purchase line is of the Resource type.
  • Posted purchase receipts are recorded in the Purch. Rcpt. Header table, and posted purchase invoices are recorded in the Purch. Inv. Header table. Receiving and invoicing can be separate posting operations.

Learn more in Posting purchases.

Posting purchases
Analyze list and query data with analysis mode
Analytics, business intelligence, and reporting overview
Purchasing overview
Work with Business Central

Start a free trial!

Find free e-learning modules for Business Central here