Sales cube (SalesCube) 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 Sales cube to report on sales transactions, specifically with regard to posting sales order invoices and sales order packing slips.

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.

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)

• Product dimension – size (EcoResProductSize)

• Product dimension – color (EcoResProductColor)

• Product dimension – configuration (Config)

Tables and views

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

• CustInvoiceJour table

• RetailHour table

• CustInvoiceTransExpanded view

• CustPackingSlipTransExpanded view

• CustTableCube view

• InventTableExpanded view

• LogisticsPostalAddressExpanded view

• RetailCategoryExpanded view

• RetailChannelView view

• RetailOMHierarchyView view

• RetailTerminalView view

Measures

The Sales cube includes the following measure groups.

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

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 Geographic location Hour of day Retail channel POS terminal Customer Warehouses Worker Customer invoices Customer (customer – invoice account) Date Date (due date) 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 miscellaneous charges, such as transportation fees, that are allocated to the order, in the accounting currency.
Customer invoice discount – accounting currency	CustInvoiceJour.EndDiscMST	Sum	The total discount, in the accounting currency, that is given on the order. The line discount is not included.

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 Released products Geographic location Retail category Customer Warehouses Sales category Styles Colors Configurations Sizes Units Customer packing slip lines Customer (customer invoice account) Date Sales category (sales category – historic) Geographic location (delivery location)
Packing slip quantity – inventory unit	CustPackingSlipTransExpanded.InventQty	Sum	Quantity of items per packing slip line in storage unit of measure.
Days late confirmed ship date	CustPackingSlipTransExpanded.DaysDelayedConfirmedDate	Sum	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	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	Quantity of items per packing slip line in sold unit of measure.

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	Number of invoice transactions, also known as invoice lines.	Currency Company Released products Geographic location Hour of day Retail category Retail channel Organization unit POS terminal Customer Warehouses Worker Sales category Styles Colors Configurations Sizes Units Customer invoice transaction Customer (customer – invoice account) Date Date (exchange rate date) Sales category (sales category – historic) Geographic location (delivery location)
Commision line amount – accounting currency	CustInvoiceTransExpanded.CommishAmountMST	Sum	Commission allocated per invoiced sales line.
Customer invoice quantity – sales unit	CustInvoiceTransExpanded.Qty	Sum	Quantity invoiced per sold unit of measure.
Quantity delivered without packing slip – sales unit	CustInvoiceTransExpanded.QtyPhysical	Sum	Quantity that is delivered with the invoice. 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	Invoiced tax amount per line.
Cost of goods sold – accounting currency	CustInvoiceTransExpanded.COGS	Sum	The cost of goods sold (COGS) for the particular invoiced items. 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	Quantity invoiced per storage unit of measure.
Customer invoice line amount – accounting currency	CustInvoiceTransExpanded.LineAmountMST	Sum	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.

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)

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 Sales cube contains the following calculated measures.

Calculated measure	Aggregation	Description
Average days late (requested ship date)	Average	The Days late requested ship date measure divided by the number of all packing slip lines. Note To view the average of the delayed days for only delayed packing slips, slice by the Customer packing slip lines.Delayed requested ship date dimension and view the delayed packing slips.
Average days late (confirmed ship date)	Average	The Days late confirmed ship date measure divided by the number of all packing slip lines. Note To view the average of the delayed days for only delayed packing slips, slice by the Customer packing slip lines.Delayed confirmed ship date dimension and view the delayed packing slips.
Gross profit margin	Sum	Gross profit margin is the Sales amount – COGS – Sales tax.
Gross profit margin percentage	Average	Gross profit margin percentage is Gross profit margin divided by Sales amount.

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