Purchase cube (PurchCube) for Microsoft Dynamics AX 2012 and Microsoft Dynamics AX 2012 Feature Pack
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 Feature Pack, Microsoft Dynamics AX 2012
Use the Purchase cube to report on purchase transactions.
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 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.
Configuration keys
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)
Tables and views
The Purchase cube uses data from the following tables and views:
InventBuyerGroup table
VendInvoiceJour table
InventTableExpanded view
LogisticsPostalAddressExpanded view
VendInvoiceTransExpanded view
VendPackingSlipTransExpanded view
VendTableCube view
Measures
The Purchase cube includes the following measure groups.
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 |
Number of vendor accounts. |
Currency Company Vendor Warehouses Vendor (vendor – invoice account) |
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 |
Number of released products in all organizations. |
Currency Company Vendor Released products Buyer groups Styles Colors Configurations Sizes |
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 |
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. |
.gif "Note")
Note Product receipt lines
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.
|
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 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). |
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 |
Calculated measures
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. Note To view the average of the delayed days for delayed product receipts, slice by the Product receipt lines - delayed dimension and view the delayed product receipts. |
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 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