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