Purchase cube (PurchCube) for Microsoft Dynamics AX 2012 and Microsoft Dynamics AX 2012 Feature Pack [AX 2012]
Updated: July 3, 2012
Applies To: Microsoft Dynamics AX 2012 Feature Pack, Microsoft Dynamics AX 2012
Use the Purchase cube to report on purchase transactions.
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 example, the InventTrans.QTY field is expressed in the inventory unit of measure. The inventory unit of measure is stored in the InventTableModule table in Microsoft Dynamics AX for each item number. Each item number has three records with different values that represent the unit of measure (ModuleType): Sales, Inventory, and Purchase. For this purpose, use the UnitID where the ModuleType equals Inventory.
For the Quantity measure that uses the purchase unit of measure, slicing on the Units dimension separates the quantities by unit of measure.
The following configuration keys are required to use all features of the Purchase cube:
-
General ledger (LedgerBasic)
-
Trade (LogisticsBasic)
-
Charges (Markup)
-
Price/discount (PriceDisc)
-
Product dimension – size (EcoResProductSize)
-
Product dimension – color (EcoResProductColor)
-
Product dimension – configuration (Config)
The Purchase cube includes the following measure groups.
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 | Number of released products in all organizations. | Currency Company Vendor Released products Buyer groups Styles Colors Configurations Sizes |
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 | Number of invoiced purchase lines that has been posted. Every partial invoice of a purchase line counts as one. | Currency Company Vendor Released products Buyer groups Warehouses Procurement category Styles Colors Configurations Sizes Units Vendor invoice lines Date Vendor (vendor – invoice account) Date (exchange rate date) Procurement category - historic | |
| Purchase and return quantity – purchase unit | VendInvoiceTransExpanded.Qty | Sum | Number of items that has been purchased or returned in a unit of measure used on the purchase order. Use the Units dimension to identify the unit of measure. A returned quantity appears as a negative number. | ||
| Purchase quantity – inventory unit | VendInvoiceTransExpanded.PurchaseQty | Sum | 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 | Invoiced amount of the purchased items where items appear as a positive quantity on the purchase order. | ||
| Return quantity – inventory unit | VendInvoiceTransExpanded.ReturnQty | Sum | 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 | 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 | Invoiced amount per line, in the company currency. |
This measure group is based on the VendPackingSlipTransExpanded 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 Vendor Released products Warehouses Worker Procurement category Styles Colors Configurations Sizes Units Product receipt lines Date Vendor (vendor – invoice account) Procurement category | |
| Days late | VendPackingSlipTransExpanded.DaysDelayed | Sum | 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. If the product receipt is posted before the expected date, then the days late is 0. The measure can be sliced by the attribute Delayed on the Product receipt lines dimension; this separates the product receipts in delayed and not delayed. This measure is used for the Average days late calculated measure. The expected date is the Confirmed delivery date that is given on the purchase order line.
| ||
| Product receipt lines amount – accounting currency | VendPackingSlipTransExpanded.ValueMST | Sum | 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. | ||
| Product receipt lines quantity – inventory unit | VendPackingSlipTransExpanded.InventQty | Sum | Number of items received on a product receipt, in an inventory unit of measure.
|
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 Vendor Buyer groups Vendor invoice journal Date Vendor (vendor – invoice account) 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). |
The Purchase cube contains the following calculated measures.
| Calculated measure | Aggregation | Description | ||
|---|---|---|---|---|
| Average days late | Average | The Days late measure divided by the number of all product receipt lines.
|
The Purchase cube can be accessed by users assigned to the following Microsoft SQL Server Analysis Services roles.
-
Accountant
-
Accounting 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
Announcements: To see known issues and recent fixes, use Issue search in Microsoft Dynamics Lifecycle Services (LCS).

Note