Bncollege Dbr

view: bncollege_dbr {
  sql_table_name: DBR ;;

  dimension: dbr_no {
    primary_key: yes
    sql: ${TABLE}.DBR_NO ;;
  }

  dimension: college_status {
    label: "BNCOLLEGE STATUS"
  }

  dimension: college_active {
    label: "BNCOLLEGE ACTIVE"
    type: yesno
    sql: ${TABLE}.DBR_STATUS not in ('CON','RTP','SKE','PIF','SIF','APR') ;;
  }

  dimension: college_concession {
    label: "BNCOLLEGE CONCESSION"
    type: yesno
    sql: ${TABLE}.DBR_STATUS in ('CON','RTP','SKE') ;;
  }

  dimension: college_resolved {
    label: "BNCOLLEGE RESOLVED"
    type: yesno
    sql: ${TABLE}.DBR_STATUS in ('PIF','SIF','APR') ;;
  }

  dimension: dbr_adv_costs {
    type: number
    sql: ${TABLE}.DBR_ADV_COSTS ;;
  }

  dimension: dbr_agency {
    sql: ${TABLE}.DBR_AGENCY ;;
  }

  dimension: dbr_assign_amt {
    type: number
    sql: ${TABLE}.DBR_ASSIGN_AMT ;;
  }

  dimension: dbr_batch_no {
    sql: ${TABLE}.DBR_BATCH_NO ;;
  }

  dimension: dbr_batch_seq {
    sql: ${TABLE}.DBR_BATCH_SEQ ;;
  }

  dimension: dbr_category {
    sql: ${TABLE}.DBR_CATEGORY ;;
  }

  dimension: dbr_cl_codes_1 {
    sql: ${TABLE}.DBR_CL_CODES_1 ;;
  }

  dimension: dbr_cl_codes_2 {
    sql: ${TABLE}.DBR_CL_CODES_2 ;;
  }

  dimension: dbr_cl_codes_3 {
    sql: ${TABLE}.DBR_CL_CODES_3 ;;
  }

  dimension: dbr_cl_codes_4 {
    sql: ${TABLE}.DBR_CL_CODES_4 ;;
  }

  dimension: dbr_cl_dates_4 {
    type: number
    hidden: yes
    sql: ${TABLE}.DBR_CL_DATES_4 ;;
  }

  dimension: dbr_cl_misc_1 {
    sql: ${TABLE}.DBR_CL_MISC_1 ;;
  }

  dimension: dbr_cl_misc_2 {
    sql: ${TABLE}.DBR_CL_MISC_2 ;;
  }

  dimension: dbr_cl_misc_3 {
    sql: ${TABLE}.DBR_CL_MISC_3 ;;
  }

  dimension: dbr_cl_misc_4 {
    sql: ${TABLE}.DBR_CL_MISC_4 ;;
  }

  dimension: dbr_class {
    sql: ${TABLE}.DBR_CLASS ;;
  }

  dimension: dbr_class_desc {
    label: "ACCOUNT CLASS"
  }

  dimension: dbr_cli_ref_no {
    sql: ${TABLE}.DBR_CLI_REF_NO ;;
  }

  dimension: dbr_client {
    sql: ${TABLE}.DBR_CLIENT ;;
  }

  dimension: dbr_client_int_amt {
    type: number
    sql: ${TABLE}.DBR_CLIENT_INT_AMT ;;
  }

  dimension: dbr_com_rate {
    type: number
    sql: ${TABLE}.DBR_COM_RATE ;;
  }

  dimension: dbr_com_rule {
    type: number
    sql: ${TABLE}.DBR_COM_RULE ;;
  }

  dimension: dbr_desk {
    sql: ${TABLE}.DBR_DESK ;;
  }

  dimension: dbr_int_due {
    type: number
    sql: ${TABLE}.DBR_INT_DUE ;;
  }

  dimension: dbr_int_rate {
    type: number
    sql: ${TABLE}.DBR_INT_RATE ;;
  }

  dimension: dbr_int_tot {
    type: number
    sql: ${TABLE}.DBR_INT_TOT ;;
  }

  dimension: dbr_name1 {
    sql: ${TABLE}.DBR_NAME1 ;;
  }

  dimension: dbr_next_ltr_no {
    type: number
    sql: ${TABLE}.DBR_NEXT_LTR_NO ;;
  }

  dimension: dbr_other_due {
    type: number
    sql: ${TABLE}.DBR_OTHER_DUE ;;
  }

  dimension: dbr_other_tot {
    type: number
    sql: ${TABLE}.DBR_OTHER_TOT ;;
  }

  dimension: dbr_owner {
    sql: ${TABLE}.DBR_OWNER ;;
  }

  dimension: dbr_principal_due {
    type: number
    sql: ${TABLE}.DBR_PRINCIPAL_DUE ;;
  }

  dimension: dbr_principal_recvd {
    type: number
    sql: ${TABLE}.DBR_PRINCIPAL_RECVD ;;
  }

  dimension: dbr_priority {
    sql: ${TABLE}.DBR_PRIORITY ;;
  }

  dimension: dbr_recvd_tot {
    type: number
    sql: ${TABLE}.DBR_RECVD_TOT ;;
  }

  dimension: dbr_status {
    sql: ${TABLE}.DBR_STATUS ;;
  }

  dimension: dbr_tie_series {
    sql: ${TABLE}.DBR_TIE_SERIES ;;
  }

  dimension: dbr_time_zone {
    sql: ${TABLE}.DBR_TIME_ZONE ;;
  }

  dimension_group: dbr_assign_date_o {
    label: "PLACEMENT"
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.DBR_ASSIGN_DATE_O ;;
  }

  dimension_group: dbr_cl_dates_1_o {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.DBR_CL_DATES_1_O ;;
  }

  dimension_group: dbr_cl_dates_2_o {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.DBR_CL_DATES_2_O ;;
  }

  dimension_group: dbr_cl_dates_3_o {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.DBR_CL_DATES_3_O ;;
  }

  dimension_group: dbr_cl_dates_4_o {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.DBR_CL_DATES_4_O ;;
  }

  dimension_group: dbr_close_date_o {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.DBR_CLOSE_DATE_O ;;
  }

  dimension_group: dbr_int_thru_date_o {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.DBR_INT_THRU_DATE_O ;;
  }

  dimension_group: dbr_last_chg_date_o {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.DBR_LAST_CHG_DATE_O ;;
  }

  dimension_group: dbr_last_letter_o {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.DBR_LAST_LETTER_O ;;
  }

  dimension_group: dbr_last_pay_date_o {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.DBR_LAST_PAY_DATE_O ;;
  }

  dimension_group: dbr_last_pdc_date_o {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.DBR_LAST_PDC_DATE_O ;;
  }

  dimension_group: dbr_last_trust_date_o {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.DBR_LAST_TRUST_DATE_O ;;
  }

  dimension_group: dbr_last_worked_o {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.DBR_LAST_WORKED_O ;;
  }

  dimension_group: dbr_next_ltr_date_o {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.DBR_NEXT_LTR_DATE_O ;;
  }

  dimension_group: dbr_review_date_o {
    type: time
    timeframes: [date, week, month]
    sql: ${TABLE}.DBR_REVIEW_DATE_O ;;
  }

  measure: total_assign_amt {
    label: "TOTAL PLACED"
    type: sum
    sql: ${dbr_assign_amt} ;;
  }

  measure: count {
    label: "ACCOUNTS PLACED"
    type: count
  }

  measure: total_dbr_assign_amt {
    label: "ASSIGNED AMOUNT"
    type: sum
    sql: ${dbr_assign_amt} ;;
  }

  measure: total_dbr_principal_due {
    label: "BALANCE"
    type: sum
    sql: ${dbr_principal_due} ;;
  }

  measure: total_dbr_pmt {
    label: "TOTAL PAYMENT AMOUNT"
    type: sum
    sql: ${dbr_pmt_facts.total_pmt} ;;
  }

  measure: total_pmt_pct {
    label: "PAID PERCENT"
    type: number
    sql: ${total_dbr_pmt} / NULLIF(${total_dbr_assign_amt},0) * 100 ;;
  }

  measure: count_active {
    type: count
    filters: [dbr_class_desc: "Active"]
  }

  measure: percent_active {
    type: number
    sql: 100.0 * ${count_active}/ NULLIF(${count},0) ;;
  }
}

- view: bncollege_dbr
  sql_table_name: DBR
  fields:
  - dimension: dbr_no
    primary_key: true
    sql: ${TABLE}.DBR_NO
  - dimension: college_status
    label: BNCOLLEGE STATUS
  - dimension: college_active
    type: yesno
    label: BNCOLLEGE ACTIVE
    sql: ${TABLE}.DBR_STATUS not in ('CON','RTP','SKE','PIF','SIF','APR')
  - dimension: college_concession
    type: yesno
    label: BNCOLLEGE CONCESSION
    sql: ${TABLE}.DBR_STATUS in ('CON','RTP','SKE')
  - dimension: college_resolved
    type: yesno
    label: BNCOLLEGE RESOLVED
    sql: ${TABLE}.DBR_STATUS in ('PIF','SIF','APR')
  - dimension: dbr_adv_costs
    type: number
    sql: ${TABLE}.DBR_ADV_COSTS
  - dimension: dbr_agency
    sql: ${TABLE}.DBR_AGENCY
  - dimension: dbr_assign_amt
    type: number
    sql: ${TABLE}.DBR_ASSIGN_AMT
  - dimension: dbr_batch_no
    sql: ${TABLE}.DBR_BATCH_NO
  - dimension: dbr_batch_seq
    sql: ${TABLE}.DBR_BATCH_SEQ
  - dimension: dbr_category
    sql: ${TABLE}.DBR_CATEGORY
  - dimension: dbr_cl_codes_1
    sql: ${TABLE}.DBR_CL_CODES_1
  - dimension: dbr_cl_codes_2
    sql: ${TABLE}.DBR_CL_CODES_2
  - dimension: dbr_cl_codes_3
    sql: ${TABLE}.DBR_CL_CODES_3
  - dimension: dbr_cl_codes_4
    sql: ${TABLE}.DBR_CL_CODES_4
  - dimension: dbr_cl_dates_4
    type: number
    hidden: true
    sql: ${TABLE}.DBR_CL_DATES_4
  - dimension: dbr_cl_misc_1
    sql: ${TABLE}.DBR_CL_MISC_1
  - dimension: dbr_cl_misc_2
    sql: ${TABLE}.DBR_CL_MISC_2
  - dimension: dbr_cl_misc_3
    sql: ${TABLE}.DBR_CL_MISC_3
  - dimension: dbr_cl_misc_4
    sql: ${TABLE}.DBR_CL_MISC_4
  - dimension: dbr_class
    sql: ${TABLE}.DBR_CLASS
  - dimension: dbr_class_desc
    label: ACCOUNT CLASS
  - dimension: dbr_cli_ref_no
    sql: ${TABLE}.DBR_CLI_REF_NO
  - dimension: dbr_client
    sql: ${TABLE}.DBR_CLIENT
  - dimension: dbr_client_int_amt
    type: number
    sql: ${TABLE}.DBR_CLIENT_INT_AMT
  - dimension: dbr_com_rate
    type: number
    sql: ${TABLE}.DBR_COM_RATE
  - dimension: dbr_com_rule
    type: number
    sql: ${TABLE}.DBR_COM_RULE
  - dimension: dbr_desk
    sql: ${TABLE}.DBR_DESK
  - dimension: dbr_int_due
    type: number
    sql: ${TABLE}.DBR_INT_DUE
  - dimension: dbr_int_rate
    type: number
    sql: ${TABLE}.DBR_INT_RATE
  - dimension: dbr_int_tot
    type: number
    sql: ${TABLE}.DBR_INT_TOT
  - dimension: dbr_name1
    sql: ${TABLE}.DBR_NAME1
  - dimension: dbr_next_ltr_no
    type: number
    sql: ${TABLE}.DBR_NEXT_LTR_NO
  - dimension: dbr_other_due
    type: number
    sql: ${TABLE}.DBR_OTHER_DUE
  - dimension: dbr_other_tot
    type: number
    sql: ${TABLE}.DBR_OTHER_TOT
  - dimension: dbr_owner
    sql: ${TABLE}.DBR_OWNER
  - dimension: dbr_principal_due
    type: number
    sql: ${TABLE}.DBR_PRINCIPAL_DUE
  - dimension: dbr_principal_recvd
    type: number
    sql: ${TABLE}.DBR_PRINCIPAL_RECVD
  - dimension: dbr_priority
    sql: ${TABLE}.DBR_PRIORITY
  - dimension: dbr_recvd_tot
    type: number
    sql: ${TABLE}.DBR_RECVD_TOT
  - dimension: dbr_status
    sql: ${TABLE}.DBR_STATUS
  - dimension: dbr_tie_series
    sql: ${TABLE}.DBR_TIE_SERIES
  - dimension: dbr_time_zone
    sql: ${TABLE}.DBR_TIME_ZONE
  - dimension_group: dbr_assign_date_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_ASSIGN_DATE_O
  - dimension_group: dbr_cl_dates_1_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_CL_DATES_1_O
  - dimension_group: dbr_cl_dates_2_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_CL_DATES_2_O
  - dimension_group: dbr_cl_dates_3_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_CL_DATES_3_O
  - dimension_group: dbr_cl_dates_4_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_CL_DATES_4_O
  - dimension_group: dbr_close_date_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_CLOSE_DATE_O
  - dimension_group: dbr_int_thru_date_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_INT_THRU_DATE_O
  - dimension_group: dbr_last_chg_date_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_LAST_CHG_DATE_O
  - dimension_group: dbr_last_letter_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_LAST_LETTER_O
  - dimension_group: dbr_last_pay_date_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_LAST_PAY_DATE_O
  - dimension_group: dbr_last_pdc_date_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_LAST_PDC_DATE_O
  - dimension_group: dbr_last_trust_date_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_LAST_TRUST_DATE_O
  - dimension_group: dbr_last_worked_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_LAST_WORKED_O
  - dimension_group: dbr_next_ltr_date_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_NEXT_LTR_DATE_O
  - dimension_group: dbr_review_date_o
    type: time
    timeframes:
    - date
    - week
    - month
    sql: ${TABLE}.DBR_REVIEW_DATE_O
  - measure: total_assign_amt
    type: sum
    label: TOTAL PLACED
    sql: ${dbr_assign_amt}
  - measure: count
    type: count
    label: ACCOUNTS PLACED
  - measure: total_dbr_assign_amt
    type: sum
    label: ASSIGNED AMOUNT
    sql: ${dbr_assign_amt}
  - measure: total_dbr_principal_due
    type: sum
    label: BALANCE
    sql: ${dbr_principal_due}
  - measure: total_dbr_pmt
    type: sum
    label: TOTAL PAYMENT AMOUNT
    sql: ${dbr_pmt_facts.total_pmt}
  - measure: total_pmt_pct
    type: number
    label: PAID PERCENT
    sql: ${total_dbr_pmt} / NULLIF(${total_dbr_assign_amt},0) * 100
  - measure: count_active
    type: count
    filters:
    - dbr_class_desc: Active
  - measure: percent_active
    type: number
    sql: 100.0 * ${count_active}/ NULLIF(${count},0)