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.

Units of measure and reporting with the Sales cube Deployment Configuration keys Tables and views Measures Calculated measures Key performance indicators Security

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