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. Note The Units dimension should not be used on this measure because it uses the purchase units and not inventory units.
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). Note The Units dimension should not be used on this measure because it uses the purchase units and not inventory units.
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.

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. Note 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.
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. Note The Units dimension should not be used on this measure since it uses the purchase units and not inventory units.

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