Purchase cube (PurchCube) for Microsoft Dynamics AX 2012 R2 and R3
Important
This content is archived and is not being updated. For the latest documentation, see Microsoft Dynamics 365 product documentation. For the latest release plans, see Dynamics 365 and Microsoft Power Platform release plans.
Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2
The Purchase cube for Microsoft Dynamics AX is used to report on purchase transactions. This article provides details about the cube.
.png "Reference")
Units of measure and reporting with the Purchase cube Deployment Configuration keys Tables and views Measures Calculated measures Key performance indicators Security |
.png "Resources")
Analytics in Microsoft Dynamics AX Cube and KPI reference for Microsoft Dynamics AX 2012 R2 and R3 Cube and KPI reference for Microsoft Dynamics AX 2012 and Microsoft Dynamics AX 2012 Feature Pack |
Units of measure and reporting with the Purchase cube
When working with Microsoft SQL Server Reporting Services reports that display a quantity of an item, the appropriate unit of measure must be incorporated into the report to make sure that the quantity is correct, instead of just presenting a SUM value.
For the Quantity measures in the purchase unit of measure, slicing on the Purchase Units dimension separates the quantities by unit of measure. Quantity measures in inventory unit of measure can be sliced by the Unit dimension, which represents the inventory unit.
Deployment
The Purchase cube is included in the Dynamics AX project. For information about how deploy the Dynamics AX project—and the cubes that it contains—see Deploy the default cubes.
Configuration keys
The following configuration keys are required to use all features of the Purchase cube:
General ledger (LedgerBasic)
Trade (LogisticsBasic)
Tables and views
The Purchase cube uses data from the following tables and views:
DlvMode table
InventBuyerGroup table
VendInvoiceJour table
InventTableExpanded view
PurchLineExpanded view
TAMVendRebateExpanded view
VendInvoiceTransExpanded view
VendPackingSlipTransStage view
VendTableCube view
Note
The TAMVendRebateExpanded view is used with this cube only in Microsoft Dynamics AX 2012 R3.
Measures
The Purchase cube includes the following measure groups.
Released products
This measure group is based on the InventTableExpanded view and includes the following measures.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
Released products count |
Not applicable |
Count |
The number of released products in all organizations. |
Currency Company Released products Buyer groups Vendor Styles Colors Configurations Sizes |
Purchase lines
This measure group is based on the PurchLineExpanded view and includes the measures in the following table.
From the measures it is possible to read the cycle time in calendar days that it takes for a purchase to be fulfilled. The measure is based on events on a purchase line. The events on a purchase line included in this context are:
Confirming the purchase order the first time; the delivery date on the purchase line is the requested date
Adding the confirmed delivery date after the purchase order has been confirmed
Registering the first product receipt (fully received or partially received)
Registering the last product receipt (first product receipt = last product receipt if there is only one)
Only purchase order lines where the products are received by products receipts and have status received or invoiced are included in the cube measures.
| Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
| Purchase lines count |
Not applicable |
Count |
The number of purchase lines that are included in the measures in the measure group. This includes lines where the products are received by products receipts and have status Received or Invoiced. |
Currency Company Units Released products Mode of delivery Vendor Warehouses Procurement category Colors Configurations Sizes Purchase lines Date (date of first purchase order confirmation) Date (requested delivery date on first confirmation) Date (receipt date of last delivery) Procurement category (procurement category – historic) Units (purchase units) |
| Purchase line total days from first confirmation to received |
PurchLineExpanded.DaysConfirmedToReceived |
Sum |
The total number of days on all purchase lines from the date where the first confirmation happened to the date where the last product receipt was registered (product receipt date). This measure is primarily used to generate the corresponding average calculated measure. |
|
| Purchase line total days from first requested delivery date to received |
PurchLineExpanded.DaysExpectedToReceived |
Sum |
The total number of days on all purchase lines from the delivery date requested on the first confirmation to the date where the last product receipt was registered (product receipt date). This measure is primarily used to generate the corresponding average calculated measure. |
|
| Purchase line total number of product receipts |
PurchLineExpanded.CountProductReceipts |
Sum |
The total number of product receipts that have been registered on the purchase lines. |
|
| Purchase line total number of confirmations by purchase line |
PurchLineExpanded.CountPurchaseConfirmations |
Sum |
The total number of confirmations of the purchase order that the purchase line is related to. This indicates how many changes there have been in the purchase order lifecycle. Note that the number is per purchase line. For example, if there are 2 purchase lines on a purchase order and the purchase order has been confirmed 2 times then the measure shows 4. |
|
| Purchase line total days from first confirmation to first receipt |
PurchLineExpanded.DaysConfirmedToFirstReceived |
Sum |
The total number of days on all purchase lines from the date where the first confirmation happened to the date where the first product receipt was registered (product receipt date). This measure is primarily used to generate the corresponding average calculated measure. |
|
| Purchase line total days from first receipt to received |
PurchLineExpanded.DaysPartialToFullReceipt |
Sum |
The total number of days on all purchase lines from the date where the first product receipt was registered to the date where the last product receipt was registered (product receipt date). This measure is primarily used to generate the corresponding average calculated measure. |
|
| Purchase line total days from first confirmation to delivery date is confirmed |
PurchLineExpanded.DaysConfirmedToFirstResponse |
Sum |
The total number of days on all purchase lines from the date where the first confirmation happened to the date where the confirmed delivery date is added to the purchase line. This measure is primarily used to generate the corresponding average calculated measure. |
Product receipt lines
This measure group is based on the VendPackingSlipTransStage view and includes the following measures.
| Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
| Product receipt lines count |
Not applicable |
Count |
The number of product receipt lines. |
Company Units Released products Buyer groups Vendor Warehouses Procurement category Worker Colors Configurations Sizes Product receipt lines Date (expected delivery date) Vendor (vendor – invoice account) Date Procurement category (procurement category – historic) Units (purchase units) |
| Days late |
VendPackingSlipTransStage.DaysDelayed |
Sum |
The number of days that the product receipt is delayed seen as the difference between the product receipt date that is given when posting the product receipt (date-physical) and the actual expected date. The measure has a value if the product receipt is posted after the expected date. The expected date is the Confirmed delivery date that is given on the purchase order line. If the Confirmed delivery date on the purchase order line is blank, then the measure is calculated as if the date is January 1, 1900. |
|
| Days early |
VendPackingSlipTransStage.DaysEarly |
Sum |
The number of days that the product receipt is early, seen as the difference between the product receipt date that is given when posting the product receipt (date-physical) and the actual expected date. The measure has a value when the product receipt is posted before the expected date. The expected date is the Confirmed delivery date that is given on the purchase order line. |
|
| Product receipt lines quantity - inventory unit |
VendPackingSlipTransStage.InventQty |
Sum |
The number of items received on a product receipt, in an inventory unit of measure. |
|
| Product receipt lines amount - accounting currency |
VendPackingSlipTransStage.ValueMST |
Sum |
The cost of the items, which appears only after the invoice is posted. For product receipts that have been posted, the value is 0 (zero) until the corresponding invoice is posted. |
Vendor invoice
This measure group is based on the VendInvoiceJour table and includes the following measures.
| Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
| Vendor invoice count |
Not applicable |
Count |
The number of invoices. |
Currency Company Mode of delivery Buyer groups Vendor Vendor invoice journal Vendor (vendor – invoice account) Date Date (due date) |
| Vendor invoice amount – accounting currency |
VendInvoiceJour.InvoiceAmountMST |
Sum |
The invoiced amounts, in the accounting currency. |
|
| Vendor invoice charges amount – accounting currency |
VendInvoiceJour.SumMarkupMST |
Sum |
The miscellaneous charges, such as transportation fees, that are allocated to the order, in the accounting currency. |
|
| Vendor invoice discount – accounting currency |
VendInvoiceJour.EndDiscMST |
Sum |
The total discount, in the accounting currency, that is given on the order (the line discount is not included). |
Vendors
This measure group is based on the VendTableCube view and includes the following measures.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
Vendors count |
Not applicable |
Count |
The number of vendor accounts. |
Currency Company Vendor Warehouses Vendor (vendor – invoice account) |
Vendor invoice lines
This measure group is based on the VendInvoiceTransExpanded view and includes the following measures.
| Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|
|---|---|---|---|---|---|
| Vendor invoice lines count |
Not applicable |
Count |
The number of invoiced purchase lines that has been posted. Every partial invoice of a purchase line counts as one. |
Currency Company Units Released products Buyer groups Vendor Warehouses Procurement category Styles Colors Configurations Sizes Vendor invoice lines Vendor (vendor – invoice account) Date Date (exchange rate date) Procurement category (procurement category – historic) Units (purchase units) |
|
| Purchase and return quantity – purchase unit |
VendInvoiceTransExpanded.Qty |
Sum |
The number of items that has been purchased or returned in a unit of measure used on the purchase order. Use the Purchase Units dimension to identify the unit of measure. A returned quantity appears as a negative number. |
||
| Purchase quantity – inventory unit |
VendInvoiceTransExpanded.PurchaseQty |
Sum |
The number of items that has been purchased, in an inventory unit of measure, where items appear as a positive value on the purchase order.
|
||
| Vendor invoice line amount returns not included – accounting currency |
VendInvoiceTransExpanded.PurchaseAmountMST |
Sum |
The invoiced amount of the purchased items where items appear as a positive quantity on the purchase order. |
||
| Return quantity – inventory unit |
VendInvoiceTransExpanded.ReturnQty |
Sum |
The number of items that has been returned to the vendor in an inventory unit of measure (or appears as a negative quantity on the invoiced purchase order).
|
||
| Vendor invoice line amount returns – accounting currency |
VendInvoiceTransExpanded.ReturnAmountMST |
Sum |
The invoiced amount of the returned items where items appear as a negative quantity on the purchase order. |
||
| Vendor invoice line amount – accounting currency |
VendInvoiceTransExpanded.LineAmountMST |
Sum |
The invoiced amount per line, in the company currency. |
.gif "Note")
Note Exchange rates by day
This measure group is based on the BIExchangeRateView view and includes the following measures.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
Exchange rate |
BIExchangeRateView.CrossRate |
Max |
The exchange rate. |
Currency Date (exchange rate date) Analysis currency |
Vendor Rebate
This measure group is based on the TAMVendRebateExpanded view and includes the following measures.
Note
This measure group is available only in Microsoft Dynamics AX 2012 R3.
Measure |
Measure field name |
Aggregation |
Description |
Associated dimensions |
|---|---|---|---|---|
Vendor rebate count |
Not applicable |
Count |
Total number of Open and Approved rebate claims. |
Currency Company Released Products Vendor Vendor Rebate Date (Calculation date – Date) |
Vendor rebate amount |
TAMVendRebateExpanded.StartingRebateAmt |
Sum |
Total dollar value of Open and Approved rebate claims. |
Calculated measures
The Purchase cube contains the following calculated measures.
Calculated measure |
Aggregation |
Associated measure group |
Description |
|---|---|---|---|
Average days late |
Average |
Product receipt lines |
The average difference from expected delivery to actual product receipt for all late product receipts. |
Average days early |
Average |
Product receipt lines |
The average difference from expected delivery to actual product receipt for all early product receipt. |
Vendor performance on product receipts - percent late |
Average |
Product receipt lines |
The percent of all product receipts where the product receipt lines are received late. |
Vendor performance on product receipts - percent on time |
Average |
Product receipt lines |
The percent of all product receipts where the product receipt lines are received on time. |
Vendor performance on product receipts - percent early |
Average |
Product receipt lines |
The percent of all product receipts where the product receipt lines are received early. |
Purchase line average days from first confirmation to received |
Average |
Purchase lines |
The average difference from first confirmation to the last product receipt registered. |
Purchase line average days late from first requested delivery date to received |
Average |
Purchase lines |
The average difference from the requested delivery date of first confirmation to the last product receipt registered for purchase lines with late receipt. |
Purchase line average days early from first requested delivery date to received |
Average |
Purchase lines |
The average difference from the requested delivery date of first confirmation to the last product receipt registered for purchase lines with early receipt. |
Purchase line average days from first confirmation to first receipt |
Average |
Purchase lines |
The average difference from first confirmation to the first product receipt registered. |
Purchase line average days from first receipt to received |
Average |
Purchase lines |
The average difference from the first product receipt registered to the last product receipt registered. |
Purchase line average days from first confirmation to delivery date is confirmed |
Average |
Purchase lines |
The average difference from first confirmation to addition of the confirmed delivery date to the purchase line. |
Purchase line average number of confirmations |
Average |
Purchase lines |
The average number of confirmed purchase orders per related purchase line. |
Purchase line average number of product receipts |
Average |
Purchase lines |
The average number of product receipts registered on the purchase lines. |
Key performance indicators
The Purchase cube does not include any key performance indicators (KPIs).
Security
The Purchase cube can be accessed by users assigned to the following Microsoft SQL Server Analysis Services roles.
Accountant
Accounting manager
Accounting payable manager
Accounting supervisor
Accounts payable centralized payments clerk
Accounts payable clerk
Accounts payable manager
Buying agent
Chief executive officer
Chief financial officer
Compliance manager
Financial controller
Project manager
Purchasing manager