Sales module
Sales invoice lines
This unified layer is applied to the pipeline containing data for sales invoice line transactions. Cost of Sales, Sales Invoice Amount and Quantity can be mapped here, as well as any statuses. The Sales invoice lines unified layer also provides the opportunity to perform cross-ledger reporting with Sales budgets.
Columns table
Column Name | Data Type | Description Key | Key |
A/R Invoice | Text (50) | The unique identifier code associated with the sales invoice header. | Primary key |
Account code | Text (50) | The unique identifier code associated with the account against which the general ledger transaction is recorded. | |
Budget amount | Date | The sales budget amount, used to compare with the actual sales amounts. | |
Commission amount | Numeric | Numeric column that shows the commission amount associated with the sale. | Additional key |
Company | Text (50) | The unique identifier code associated with the company. | |
Cost of sales amount | Numeric | Numeric column showing the cost of sale for the invoice line item. | |
Country | Text (50) | The unique identifier of the country or region associated with the sale. This should be the bill-to country of the customer. | |
Customer | Text (50) | The unique identifier of the customer associated with the sale. | |
Customer group | Text (50) | The unique identifier of the customer associated with the sale. The unique identifier of the group that the customer belongs to. | |
Customer key | Text (100) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Department | Department Text (50) | The unique identifier code for the department. | |
Employee key | Text (100) | ) For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Freight amount | Numeric value | Numeric value representing the amount charged for freight on the invoice. | |
Gross profit amount | Numeric | Numeric value for gross profit amount, usually calculated as Sales less COGS. | |
Invoice amt incl tax | Numeric | Numeric value representing the total amount the item was sold for, including tax charges. | |
Invoice date | Date | The date associated with the sales invoice. | |
Invoice discount amount | Numeric | Numeric value indicating the total discount amount for this invoice line. | |
Invoice line amount | Numeric | The sales amount associated with the invoice line, excluding tax and freight. | |
Invoiced Qty | Numeric | This represents the number of items that are on the sales invoice line. | |
Item group | Text (50) | The unique identifier code of the group that the item belongs to. | |
Item key | Text (100) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Item No. | Text (50) | The unique identifier code associated with the item that has been sold. | |
Line status | Text (50) | The unique identifier code shows the current status of the sales invoice line. For example, this could be the code representing values such as 'Open', 'Closed', 'Pending' or 'Canceled'. | |
Payment term | Text (50) | Unique identifier code of the terms of payment of the invoice. | |
Return amount | Numeric | Numeric column that shows the amount of a returned invoice line. | |
Returned qty | Numeric | Numeric column that shows the quantity of a returned invoice line. | |
Salesperson | Text (50) | Unique identifier code of the employee or seller associated with the sales invoice. | |
Segment 1 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 1 refers to the first segment in the list. | |
Segment 2 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 2 refers to the second segment in the list. | |
Segment 3 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 3 refers to the third segment in the list. | |
Segment 4 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 4 refers to the forth segment in the list. | |
Segment 5 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 5 refers to the fifth segment in the list. | |
Shipping type | Text (50) | The unique identifier code associated with the delivery method of the sales invoice. For example, this could be the codes representing 'Air', 'Sea', 'Rail' etc, or even 'Express', 'Standard', 'DHL', 'UPS' etc. | |
Storage location | Text (50) | The unique identifier code of the Storage location associated with the sales invoice. | |
Storage location key | Text (100) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Tax amount | Numeric | Numeric column showing the tax amount associated with the invoice line. |
Relationships
Name | Related layer | Mapping |
Company | Company | Company -> Company |
Customers | Customers | Customer key -> Customer key |
Employee | Employee | Purchaser key -> Sales employee key |
Items | Items | Item key -> Item key |
Storage location | Storage location | Storage location key-> Storage location key |
Note
If Sales Actual and Budget information is stored in different tables in the source, it is necessary to combine them into one table to make use of this unified layer. Ensure that the tables have at least the following columns in common before combining them (for example, using a union), to ensure the correct granularity: Company, Date, Amount.
Credit memos and return transactions should be included in the pipeline that is mapped to this layer.
It is assumed that all amount columns mapped to this unified layer are already currency converted to the Main currency. If amounts are not currency converted when they are mapped, cross-company reporting may not work as expected.
Sales order lines
The Sales order lines unified layer is applied to the Sales order lines pipeline, which contains information about sales orders such as order amount, quantity and dates. Delivery performance metrics can be derived from the columns in this unified layer.
Columns table
Column Name | Data Type | Description | Key |
Amount | Numeric | The sales order amount associated with the order line. | |
Amount not delivered | Numeric | Numeric column showing the currency amount associated with an order that has not yet been delivered, ie the outstanding amount | |
Backorder amount | Numeric | Numeric column indicating the amount on an order that cannot be fulfilled yet. | |
Backorder Qty | Numeric | Numeric column indicating the quantity of items on an order that cannot be fulfilled yet. | |
Company | Text (50) | The unique identifier code associated with the company. | Additional key |
Country | Text (50) | Unique identifier code of the country or region to which the order is being shipped. This should be the ship-to country or region associated with the customer. | |
Customer | Text (50) | The unique identifier code of the customer associated with the order. | |
Customer group | Text (50) | The unique identifier code of the group that the customer belongs to. | |
Customer key | Text (100) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Days to ship bucket | Integer | A column that shows the number of days past due for days between 150 days early and 150 days late. It can be used to relate to the aging buckets pipeline. | |
Days to ship difference | Integer | Numeric column showing the number of days between actual ship date and the expected ship date if it has been shipped, or between today's date and the expected ship date if it has not been delivered. This is used to indicate whether the order was late or not. | |
Delivered Qty | Numeric | The quantity that has left the warehouse for delivery. | |
Delivery fulfilment | Text (50) | This column indicates whether the purchase order has been delivered in full, partially or not at all. It is used to calculate the Perfect order index (POI) metric. | |
Delivery precision | Text (50) | This column indicates whether the purchase order has been delivered in on-time, late, early, or not at all. It is used to calculate the Perfect order index (POI) metric. | |
Delivery type key | Text (100) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Department | Text | (50) The unique identifier code for the department. | |
Discount amount | Numeric | The discount amount that is associated with the sale order line. | |
Employee key | Text (100) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Item | Text (50) | The unique identifier code associated with the item that has been sold. | |
Item group | Text (50) | The unique identifier code for the group that the item belongs to. | |
Item key | Text (100) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Line status | Text (50) | The unique identifier code that shows the current status of the sales order line. For example, this could be the code representing values such as 'Ordered', 'In Progress', 'Complete' etc. | |
Order date | Date | Date column representing the order date. | |
Order Qty | Numeric | The number of items that have been ordered in the sales order line. | |
Payment term | Text (50) | The unique identifier code for the terms of payment for this sales order. | |
Sales Order | Text (50) | The unique identifier code of the sales order header record that this line belongs to. | Primary key |
Sales order line count | Integer | A column that counts the number of sales order lines. If the finest granularity of this table is sales order lines, this will just be the row count. | |
Salesperson | Text (50) | The unique identifier code of the employee associated with the sales order. | |
Segment 1 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 1 refers to the first segment in the list. | |
Segment 2 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 2 refers to the second segment in the list. | |
Segment 3 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 3 refers to the third segment in the list. | |
Segment 4 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 4 refers to the forth segment in the list. | |
Segment 5 | Text (50) | Segments (also known as Financial Dimensions, or Dimensions), are a way of categorizing financial data across your organization. For example, segments may refer to Divisions, Regions or Departments. In some ERP systems, the segments are concatenated with one another and with the account code to provide a new unique code per segment combination. Segment 5 refers to the fifth segment in the list. | |
Shipment date | Date | Date column representing the date on which delivery took place. In the context of sales, this means the date that the item left the warehouse. | |
Shipping type | Text (50) | The unique identifier code associated with the delivery method of the sales order. For example, this could be the codes representing 'Air', 'Sea', 'Rail' etc, or even 'Express', 'Standard', 'DHL', 'UPS' etc. | |
Storage location | Text (50) | The unique identifier code of the storage location associated with the sales order. | |
Storage location key | Text (100) | For use in Tableau/Power BI relationship mappings. This is usually made up of the concatenation of foreign key columns with the company column of a measure group pipeline. If the dimension that the measure group is relating to is not a per company dimension, a concatenation with a blank value will be used. | Concatenated key |
Tax amount | Numeric | Numeric column showing the tax amount associated with the order line. | |
Tax amount not delivered | Numeric | Numeric column showing the outstanding tax amount that is associated with the order line. | |
Unit | Text (50) | Unique identifier code of the unit of measure associated with the quantity sold. | |
Unit price | Numeric | The price per unit of the item that has been sold. |
Relationships
Name | Related layer | Mapping |
Company | Company | Company -> Company |
Customers | Customers | Customer key -> Customer key |
Employee | Employee | Purchaser key -> Sales employee key |
Items | Items | Item key-> Item key |
Storage location | Storage locations | Storage location key-> Storage location key |
Note
It is assumed that all amount columns mapped to this unified layer are already currency converted to the Main currency. If amounts are not currency converted when they are mapped, cross-company reporting may not work as expected.