Sales cube (SalesCube) for Microsoft Dynamics AX 2012 R2 and R3

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 R3, Microsoft Dynamics AX 2012 R2

The Sales cube for Microsoft Dynamics AX is used to report on shipped and invoiced sales order lines. This article provides details about the cube.

Reference

Units of measure and reporting with the Sales cube

Deployment

Configuration keys

Tables and views

Measures

Calculated measures

Key performance indicators

Security

Resources

Analytics in Microsoft Dynamics AX

Cube and KPI reference for Microsoft Dynamics AX 2012 R2 and R3

Cube and KPI reference for Microsoft Dynamics AX 2012 and Microsoft Dynamics AX 2012 Feature Pack

Units of measure and reporting with the Sales 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 sales order unit of measure, slicing on the Units dimension separates the quantities by unit of measure.

Deployment

The Sales cube is included in the Dynamics AX project. For information about how deploy the Dynamics AX project—and the cubes that it contains—see Deploy the default cubes.

Configuration keys

The following configuration keys are required to use all features of the Sales cube:

  • General ledger (LedgerBasic)

  • Trade (LogisticsBasic)

  • Charges (Markup)

  • Commission (Commission)

  • Price/discount (PriceDisc)

  • Retail headquarters (RetailHeadquarters)

Tables and views

The Sales cube uses data from the following tables and views:

  • CustInvoiceJour table

  • MCRSourceCodeSetup table

  • RetailHour table

  • CustInvoiceTransExpanded view

  • CustPackingSlipTransExpanded view

  • CustTableCube view

  • InventTableExpanded view

  • MCRSourceSalesSummary view

  • PdsRebateExpanded view

  • RetailCategoryExpanded view

  • RetailChannelView view

  • RetailOMHierarchyView view

  • RetailTerminalView view

  • SalesLineExpanded view

Note

The MCRSourceCodeSetup table and the MCRSourceSalesSummary and PdsRebateExpanded views are used with this cube only in Microsoft Dynamics AX 2012 R3.

Measures

The Sales cube includes the following measure groups.

Customer packing slip lines

This measure group is based on the CustPackingSlipTransExpanded view and includes the following measures.

Measure

Measure field name

Aggregation

Description

Associated dimensions

Customer packing slip lines count

Not applicable

Count

The number of packing slip lines.

Company

Units

Released products

Customer

Retail category

Warehouses

Geographic location

Sales category

Styles

Colors

Configurations

Sizes

Customer packing slip lines

Date

Customer (customer - invoice account)

Sales category (sales category – historic)

Geographic location (delivery location)

Units (sales units)

Packing slip quantity – inventory unit

CustPackingSlipTransExpanded.InventQty

Sum

The quantity per packing slip line, in storage unit of measure.

Days late confirmed ship date

CustPackingSlipTransExpanded.DaysDelayedConfirmedDate

Sum

The number of days (per packing slip line) from the confirmed ship date to the packing slip date.

If the packing slip date is before the confirmed ship date (that is—it is not delayed), the measure is 0.

Days late requested ship date

CustPackingSlipTransExpanded.DaysDelayedRequestedDate

Sum

The number of days (per packing slip line) from the requested ship date to the packing slip date.

If the packing slip date is before the requested ship date (that is—it is not delayed), the measure is 0.

Packing slip quantity – sales unit

CustPackingSlipTransExpanded.Qty

Sum

The quantity per packing slip line, in sold unit of measure.

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

The number of released products.

Currency

Company

Released products

Styles

Colors

Configurations

Sizes

Customers

This measure group is based on the CustTableCube view and includes the following measures.

Measure

Measure field name

Aggregation

Description

Associated dimensions

Customers count

Not applicable

Count

The number of customer records.

Currency

Company

Customer

Warehouses

Worker

Customer (customer – invoice account)

Sales order lines

This measure group is based on the SalesLineExpanded view and includes the following measures.

Measure

Measure field name

Aggregation

Description

Associated dimensions

Sales order lines count

Not applicable

Count

The number of sales order lines.

Company

Units

Released products

Customer

Retail category

Warehouses

Sales category

Colors

Configurations

Sizes

Sales order lines

Date (confirmed ship date on 1st packing slip)

Date (requested ship date on 1st packing slip)

Sales category (sales category – historic)

Units (sales units)

Customer invoice lines

This measure group is based on the CustInvoiceTransExpanded view and includes the following measures.

Measure

Measure field name

Aggregation

Description

Associated dimensions

Customer invoice lines count

Not applicable

Count

The number of invoice transactions, also known as invoice lines.

Currency

Company

Units

Released products

Customer

POS terminal

Retail category

Retail channel

Organization unit

Warehouses

Geographic location

Sales category

Worker

Styles

Colors

Configurations

Sizes

Customer invoice transaction

Date

Date (due date)

Customer (customer – invoice account)

Date (exchange rate date)

Sales category (sales category – historic)

Geographic location (delivery location)

Units (sales units)

Commision line amount – accounting currency

CustInvoiceTransExpanded.CommishAmountMST

Sum

The commission allocated per invoiced line.

Customer invoice quantity – sales unit

CustInvoiceTransExpanded.Qty

Sum

The quantity invoiced per sold unit of measure.

Quantity delivered without packing slip – sales unit

CustInvoiceTransExpanded.QtyPhysical

Sum

The quantity that is delivered directly with the invoice without a preceding packing slip.

This quantity is 0 (zero) if a packing slip has been created for the item. If no packing slip has been created, this field contains the quantity sold in selling unit of measure.

Sales tax line amount – accounting currency

CustInvoiceTransExpanded.TaxAmountMST

Sum

The invoiced tax amount per invoice line.

Cost of goods sold – accounting currency

CustInvoiceTransExpanded.COGS

Sum

The cost of goods sold (COGS) for the particular invoiced item.

The COGS value is based on the corresponding inventory transaction. This measure may need an inventory closing where a potential adjustment may occur.

Customer invoice quantity – inventory unit

CustInvoiceTransExpanded.InventQty

Sum

The quantity invoiced per storage unit of measure.

Customer invoice line amount – accounting currency

CustInvoiceTransExpanded.LineAmountMST

Sum

The invoiced amount per line, in the accounting currency, excluding tax.

Sales tax included in customer invoice line amount – accounting currency

CustInvoiceTransExpanded.LineAmountTaxMST

Sum

The value in this field is the same as the TaxAmountMST whenever the tax is included in the price on the invoice.

The value in this field is 0 (zero) if the price on the invoice does not include tax.

Customer invoices

This measure group is based on the CustInvoiceJour table and includes the following measures.

Measure

Measure field name

Aggregation

Description

Associated dimenisons

Customer invoices count

Not applicable

Count

The number of invoices.

Currency

Company

Customer

Warehouses

Geographic location

Worker

Customer invoices

Date

Date (due date)

Customer (customer – invoice account)

Geographic location (delivery location)

Customer invoice amount – accounting currency

CustInvoiceJour.InvoiceAmountMST

Sum

The invoiced amounts, in the accounting currency.

Customer invoice charges – accounting currency

CustInvoiceJour.SumMarkupMST

Sum

The charges, such as transportation fees, that are allocated to the invoice header, in the accounting currency.

Customer invoice discount – accounting currency

CustInvoiceJour.EndDiscMST

Sum

The total discount, in the accounting currency, that is given on the invoice. 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

Sales rebate

This measure group is based on the PDSRebateExpanded view and includes the following measures.

Note

This measure group is available only in Microsoft Dynamics AX 2012 R3.

Measure

Measure field name

Aggregation

Description

Associated dimensions

Sales rebate count

Not applicable

Count

Total number of Open and Approved rebate claims.

Currency

Company

Customer

Released products

Sales rebate

Worker

Date (Process date – Date)

Date (Calculation date – Date)

Starting rebate amount

PDSRebateExpanded.PDSStartingRebateAmt

Sum

Total dollar value of Open and Approved rebate claims.

Calculated measures

The Sales cube contains the following calculated measures.

Calculated measure

Aggregation

Associated measure group

Description

All sales order lines

Sum

Sales order lines

All sales order lines that have a status of delivered or invoiced, and have at least one packing slip.

% Sales order lines shipped in full

Sum

Sales order lines

The percent shipped in full of sales order lines with at least one related packing slip and not of status Open order.

% Sales order lines not shipped in full

Sum

Sales order lines

The percent not shipped in full of sales order lines with at least one related packing slip and not of status Open order.

Average days late (confirmed ship date)

Average

Customer packing slip lines

The average difference from the confirmed ship date to the packing slip receipt date.

Average days late (requested ship date)

Average

Customer packing slip lines

The average difference from the requested ship date to the packing slip receipt date.

Customer contribution margin - accounting currency

Sum

Customer invoice lines

The amount remaining after unit variable costs has been deducted from the unit revenue.

Customer contribution margin percentage

Average

Customer invoice lines

The contribution margin divided by total revenue, expressed as a percentage.

Gross profit margin

Sum

Undefined

The amount remaining after the cost of goods sold (COGS) has been deducted from the total sales for an item or a given quantity of inventory.

Gross profit margin percentage

Average

Undefined

The gross profit margin divided by the total sales revenue, expressed as a percentage. The gross profit margin represents the percent of total sales revenue that a retailer retains after incurring the direct costs associated with producing the goods and services sold. The higher the percentage, the more the retailer retains on each dollar of sales to service its other costs and obligations.

Key performance indicators

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

Security

The Sales cube can be accessed by users assigned to the following Microsoft SQL Server Analysis Services roles.

  • Accounting manager

  • Accounts receivable manager

  • Chief executive officer

  • Chief financial officer

  • Compliance manager

  • Financial controller

  • Retail merchandising manager

  • Retail operations manager

  • Retail store manager

  • Sales manager

  • Warehouse manager