Export (0) Print
Expand All

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

Updated: July 22, 2014

Applies To: Microsoft Dynamics AX 2012 R3, Microsoft Dynamics AX 2012 R2

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

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)

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

NoteNote

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

The Sales cube includes the following measure groups.

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.

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

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.

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.

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

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

NoteNote

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.

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.

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

  • Warehouse manager


Announcements: To see known issues and recent fixes, use Issue search in Microsoft Dynamics Lifecycle Services (LCS).
Was this page helpful?
(1500 characters remaining)
Thank you for your feedback

Community Additions

ADD
Show:
© 2014 Microsoft