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.

NoteNote

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).

NoteNote

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.

NoteNote

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.

NoteNote

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