Order Items

view: order_items {
  sql_table_name: GENERIC_ORDER_ITEMS ;;

  dimension: id {
    type: int
    primary_key: yes
    sql: ${TABLE}.id ;;
  }

  dimension: inventory_item_id {
    type: int
    hidden: yes
    sql: ${TABLE}.inventory_item_id ;;
  }

  dimension: return_date {
    type: date
    sql: ${TABLE}.returned_at ;;
  }

  dimension: returned {
    type: yesno
    sql: ${TABLE}.returned_at IS NOT NULL ;;
  }

  dimension: order_id {
    type: int
    hidden: yes
    sql: ${TABLE}.order_id ;;
  }

  dimension: sale_price {
    description: "The sale price reflects the price that the item was sold at."
    type: number
    sql: ${TABLE}.sale_price ;;
  }

  dimension: gross_margin {
    type: number
    sql: ${sale_price} - ${inventory_items.cost} ;;
  }

  dimension: item_gross_margin_percentage {
    type: number
    sql: 100.0 * ${gross_margin}/NULLIF(${sale_price}, 0) ;;
  }

  dimension: item_gross_margin_percentage_tier {
    type: tier
    sql: ${item_gross_margin_percentage} ;;
  }

  measure: count {
    type: count
    drill_fields: [detail]
  }

  measure: total_gross_margin {
    type: sum
    value_format: "$#,##0.00"
    sql: ${gross_margin} ;;
  }

  measure: total_sale_price {
    type: sum
    value_format: "$#,##0.00"
    sql: ${sale_price} ;;
  }

  measure: average_sale_price {
    type: average
    value_format: "$#,##0.00"
    sql: ${sale_price} ;;
  }

  measure: average_gross_margin {
    type: average
    sql: ${gross_margin} ;;
  }
}

- view: order_items
  sql_table_name: GENERIC_ORDER_ITEMS
  fields:
  - dimension: id
    type: int
    primary_key: true
    sql: ${TABLE}.id
  - dimension: inventory_item_id
    type: int
    hidden: true
    sql: ${TABLE}.inventory_item_id
  - dimension: return_date
    type: date
    sql: ${TABLE}.returned_at
  - dimension: returned
    type: yesno
    sql: ${TABLE}.returned_at IS NOT NULL
  - dimension: order_id
    type: int
    hidden: true
    sql: ${TABLE}.order_id
  - dimension: sale_price
    type: number
    description: The sale price reflects the price that the item was sold at.
    sql: ${TABLE}.sale_price
  - dimension: gross_margin
    type: number
    sql: ${sale_price} - ${inventory_items.cost}
  - dimension: item_gross_margin_percentage
    type: number
    sql: 100.0 * ${gross_margin}/NULLIF(${sale_price}, 0)
  - dimension: item_gross_margin_percentage_tier
    type: tier
    sql: ${item_gross_margin_percentage}
  - measure: count
    type: count
    drill_fields:
    - detail
  - measure: total_gross_margin
    type: sum
    value_format: '$#,##0.00'
    sql: ${gross_margin}
  - measure: total_sale_price
    type: sum
    value_format: '$#,##0.00'
    sql: ${sale_price}
  - measure: average_sale_price
    type: average
    value_format: '$#,##0.00'
    sql: ${sale_price}
  - measure: average_gross_margin
    type: average
    sql: ${gross_margin}