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

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.

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)

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

The Sales cube includes the following measure groups.

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

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.

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.

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.

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)

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

NoteNote

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.

NoteNote

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 amountCOGSSales tax.

Gross profit margin percentage

Average

Gross profit margin percentage is Gross profit margin divided by Sales amount.

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

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


Announcements: To see known issues and recent fixes, use Issue search in Microsoft Dynamics Lifecycle Services (LCS).

Community Additions

ADD
Show: