Payroll Check

view: payroll_check {
  sql_table_name: FactPayrollCheck ;;

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

  dimension: check_voucher_no {
    type: int
    sql: ${TABLE}.BKCheckVoucherNo ;;
  }

  dimension: company_id {
    sql: ${TABLE}.BKCompanyID ;;
  }

  dimension: division_id {
    sql: ${TABLE}.BKDivisionID ;;
  }

  dimension: bkearnings_code {
    sql: ${TABLE}.BKEarningsCode ;;
  }

  dimension: bkemployee_id {
    sql: ${TABLE}.BKEmployeeID ;;
  }

  dimension: bkjob_id {
    sql: ${TABLE}.BKJobID ;;
  }

  dimension: bkpayroll_dept_id {
    sql: ${TABLE}.BKPayrollDeptID ;;
  }

  dimension: bkpayroll_process_id {
    type: int
    sql: ${TABLE}.BKPayrollProcessID ;;
  }

  dimension: check_date_key {
    type: int
    sql: ${TABLE}.CheckDateKey ;;
  }

  dimension: company_key {
    type: int
    sql: ${TABLE}.CompanyKey ;;
  }

  dimension: days_in_line {
    type: int
    sql: ${TABLE}.daysInLine ;;
  }

  dimension: division_name {
    sql: ${TABLE}.DivisionName ;;
  }

  dimension: earnings_code_key {
    type: int
    sql: ${TABLE}.EarningsCodeKey ;;
  }

  dimension: earnings_code_type {
    sql: ${TABLE}.EarningsCodeType ;;
  }

  dimension: employee_key {
    type: int
    sql: ${TABLE}.EmployeeKey ;;
  }

  dimension: end_period_date_key {
    type: int
    sql: ${TABLE}.EndPeriodDateKey ;;
  }

  dimension: first_name {
    sql: ${TABLE}.FirstName ;;
  }

  dimension: is_bonus_line {
    sql: ${TABLE}.isBonusLine ;;
  }

  dimension: job_name {
    sql: ${TABLE}.JobName ;;
  }

  dimension: last_name {
    sql: ${TABLE}.LastName ;;
  }

  dimension: full_name {
    sql: concat(${first_name}, ${last_name}) ;;
  }

  dimension: line_amount {
    sql: ${TABLE}.LineAmount ;;
  }

  dimension: line_hours {
    type: number
    sql: ${TABLE}.LineHours ;;
  }

  dimension: net_check_amount {
    sql: ${TABLE}.NetCheckAmount ;;
  }

  dimension: payroll_dept_key {
    type: int
    sql: ${TABLE}.PayrollDeptKey ;;
  }

  dimension: start_period_date_key {
    type: int
    sql: ${TABLE}.StartPeriodDateKey ;;
  }

  measure: total_employer_cost {
    type: sum
    filters: [earnings_code.is_employer_cost: "True"]
    drill_fields: [employee.drill_path*]
    sql: ${line_amount} ;;
  }

  measure: percent_of_total_employer_cost {
    type: percent_of_total
    sql: ${total_employer_cost} ;;
  }

  measure: total_hours {
    type: sum
    filters: [earnings_code.is_employer_cost: "True"]
    sql: ${line_hours} ;;
  }

  measure: total_ot_hours {
    type: sum
    filters: [earnings_code.is_ot: "True"]
    filters: [earnings_code.is_employer_cost: "True"]
    sql: ${line_hours} ;;
  }

  measure: percent_ot_hours {
    type: number
    sql: 100.0 * ${total_ot_hours} / ${total_hours} ;;
  }

  measure: average_dollars_per_hour {
    label: "Dollars per Hour"
    type: number
    sql: ${total_employer_cost} / nullif(${total_hours},0) ;;
  }

  measure: total_vacation_hours {
    type: sum
    filters: [earnings_code.earnings_code: "VAC"]
    sql: ${line_hours} ;;
  }

  measure: count {
    type: count
    drill_fields: [division_name, job_name, first_name, last_name]
  }

  measure: count_distinct {
    type: count_distinct
    sql: ${full_name} ;;
  }
}

- view: payroll_check
  sql_table_name: FactPayrollCheck
  fields:
  - dimension: check_line_id
    type: int
    primary_key: true
    sql: ${TABLE}.BKCheckLineID
  - dimension: check_voucher_no
    type: int
    sql: ${TABLE}.BKCheckVoucherNo
  - dimension: company_id
    sql: ${TABLE}.BKCompanyID
  - dimension: division_id
    sql: ${TABLE}.BKDivisionID
  - dimension: bkearnings_code
    sql: ${TABLE}.BKEarningsCode
  - dimension: bkemployee_id
    sql: ${TABLE}.BKEmployeeID
  - dimension: bkjob_id
    sql: ${TABLE}.BKJobID
  - dimension: bkpayroll_dept_id
    sql: ${TABLE}.BKPayrollDeptID
  - dimension: bkpayroll_process_id
    type: int
    sql: ${TABLE}.BKPayrollProcessID
  - dimension: check_date_key
    type: int
    sql: ${TABLE}.CheckDateKey
  - dimension: company_key
    type: int
    sql: ${TABLE}.CompanyKey
  - dimension: days_in_line
    type: int
    sql: ${TABLE}.daysInLine
  - dimension: division_name
    sql: ${TABLE}.DivisionName
  - dimension: earnings_code_key
    type: int
    sql: ${TABLE}.EarningsCodeKey
  - dimension: earnings_code_type
    sql: ${TABLE}.EarningsCodeType
  - dimension: employee_key
    type: int
    sql: ${TABLE}.EmployeeKey
  - dimension: end_period_date_key
    type: int
    sql: ${TABLE}.EndPeriodDateKey
  - dimension: first_name
    sql: ${TABLE}.FirstName
  - dimension: is_bonus_line
    sql: ${TABLE}.isBonusLine
  - dimension: job_name
    sql: ${TABLE}.JobName
  - dimension: last_name
    sql: ${TABLE}.LastName
  - dimension: full_name
    sql: concat(${first_name}, ${last_name})
  - dimension: line_amount
    sql: ${TABLE}.LineAmount
  - dimension: line_hours
    type: number
    sql: ${TABLE}.LineHours
  - dimension: net_check_amount
    sql: ${TABLE}.NetCheckAmount
  - dimension: payroll_dept_key
    type: int
    sql: ${TABLE}.PayrollDeptKey
  - dimension: start_period_date_key
    type: int
    sql: ${TABLE}.StartPeriodDateKey
  - measure: total_employer_cost
    type: sum
    drill_fields:
    - employee.drill_path*
    filters:
    - earnings_code.is_employer_cost: True
    sql: ${line_amount}
  - measure: percent_of_total_employer_cost
    type: percent_of_total
    sql: ${total_employer_cost}
  - measure: total_hours
    type: sum
    filters:
    - earnings_code.is_employer_cost: True
    sql: ${line_hours}
  - measure: total_ot_hours
    type: sum
    filters:
    - earnings_code.is_ot: True
    - earnings_code.is_employer_cost: True
    sql: ${line_hours}
  - measure: percent_ot_hours
    type: number
    sql: 100.0 * ${total_ot_hours} / ${total_hours}
  - measure: average_dollars_per_hour
    type: number
    label: Dollars per Hour
    sql: ${total_employer_cost} / nullif(${total_hours},0)
  - measure: total_vacation_hours
    type: sum
    filters:
    - earnings_code.earnings_code: VAC
    sql: ${line_hours}
  - measure: count
    type: count
    drill_fields:
    - division_name
    - job_name
    - first_name
    - last_name
  - measure: count_distinct
    type: count_distinct
    sql: ${full_name}