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 uses data from the following tables and views:

  • InventBuyerGroup table

  • VendInvoiceJour table

  • InventTableExpanded view

  • LogisticsPostalAddressExpanded view

  • VendInvoiceTransExpanded view

  • VendPackingSlipTransExpanded view

  • VendTableCube view

The Purchase cube includes the following measure groups.

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)

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.

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.

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.

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

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

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.

NoteNote

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.

The Purchase cube does not include any key performance indicators (KPIs).

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

Community Additions

ADD
Show: