Deployments
Reports
C10 - Customer Profiles
Report Name: Customers' Profiles
Overview

  • Queries: 10

    Layouts: 1

  • Model: /Packages/packageBI Data_WB/ name: 2018-12-31T17:22:05.121Z
Queries
  • Queries 10
    Expressions:
  • Model: /Packages/packageBI Data_WB/ name: 2018-12-31T17:22:05.121Z
  • Query
    QueryBranch
    • queryHints
    • useForParameterInfo: false
    Data Items
    7
    BRANCHID
    [SI_BI_Datawarehouse].[d_branch].[BRANCHID]
    DESCRIPTION
    [SI_BI_Datawarehouse].[d_branch].[DESCRIPTION]
    BRANCHNAME
    [SI_BI_Datawarehouse].[d_branch].[BRANCHNAME]
    Institution
    CASE [INSTITUTIONID]
    WHEN 1 THEN 'Savings'
    WHEN 2 THEN 'Bank'
    END
    BranchInstit
    [DESCRIPTION]||' - '||[Institution]
    INSTITUTIONID
    [SI_BI_Datawarehouse].[d_branch].[INSTITUTIONID]
    REGION
    [SI_BI_Datawarehouse].[d_branch].[REGION]
    • Detail Filters
      4
    • Optional
      [BRANCHNAME] = ?pBranchName?
    • Optional
      [BranchInstit]=?pBranchInstitution?
    • Optional
      [BRANCHID] in ?pCurrentBranchID?
    • Optional
      [INSTITUTIONID] in ?pInstitutionID?
  • Query
    Query_Customer
    Join 
    [Query_Customer_Primary].[CUSTOMERID] = [Query_Custodian].[CUSTOMERID]
    refQuery
    Query_Customer_Primary
    1:1
    refQuery
    Query_Custodian
    1:1
    • queryHints
    • useForParameterInfo: false
    Data Items
    47
    CUSTOMERID
    [Query_Customer_Primary].[CUSTOMERID]
    Member #
    [Query_Customer_Primary].[Member #]
    label
    Member #
    INSTITUTIONID
    [Query_Customer_Primary].[INSTITUTIONID]
    INSTITUTION
    [Query_Customer_Primary].[INSTITUTION]
    CURRENTBRANCHID
    [Query_Customer_Primary].[CURRENTBRANCHID]
    CUSTFIRSTNAME
    [Query_Customer_Primary].[CUSTFIRSTNAME]
    CUSTLASTNAME
    [Query_Customer_Primary].[CUSTLASTNAME]
    addr1
    [Query_Customer_Primary].[addr1]
    ADDRESSTYPE
    [Query_Customer_Primary].[ADDRESSTYPE]
    Address
    [Query_Customer_Primary].[Address]
    ADDRESSLINE4
    [Query_Customer_Primary].[ADDRESSLINE4]
    ADDRUNITNO
    [Query_Customer_Primary].[ADDRUNITNO]
    CITY
    [Query_Customer_Primary].[CITY]
    COUNTRYDESC
    [Query_Customer_Primary].[COUNTRYDESC]
    POSTALCODE
    [Query_Customer_Primary].[POSTALCODE]
    CUSTASSIGNEDTO
    [Query_Customer_Primary].[CUSTASSIGNEDTO]
    BRANCH_CUSTODIAN
    [Query_Custodian].[BRANCH_CUSTODIAN]
    CUSTODIAN
    [Query_Custodian].[CUSTODIAN]
    CUSTODIAN_1
    [Query_Custodian].[CUSTODIAN_1]
    CUSTLANGUAGEPREF
    [Query_Customer_Primary].[CUSTLANGUAGEPREF]
    CUSTDATEOFBIRTH
    [Query_Customer_Primary].[CUSTDATEOFBIRTH]
    PHONETYPE
    [Query_Customer_Primary].[PHONETYPE]
    PHONEAREACODE
    [Query_Customer_Primary].[PHONEAREACODE]
    PHONENUMBER
    [Query_Customer_Primary].[PHONENUMBER]
    PHONEEXTENSION
    [Query_Customer_Primary].[PHONEEXTENSION]
    CUSTDECEASEDDATE
    [Query_Customer_Primary].[CUSTDECEASEDDATE]
    CUSTJOINEDDATE
    [Query_Customer_Primary].[CUSTJOINEDDATE]
    CUSTCLOSEDATE
    [Query_Customer_Primary].[CUSTCLOSEDATE]
    CUSTGENDER
    [Query_Customer_Primary].[CUSTGENDER]
    CUSTBOOKSTATUS
    [Query_Customer_Primary].[CUSTBOOKSTATUS]
    CUSTOPENCLOSED
    [Query_Customer_Primary].[CUSTOPENCLOSED]
    CUSTBENEFIT
    [Query_Customer_Primary].[CUSTBENEFIT]
    CUSTTYPE
    [Query_Customer_Primary].[CUSTTYPE]
    STATEDESC
    [Query_Customer_Primary].[STATEDESC]
    CUSTSTATUS
    [Query_Customer_Primary].[CUSTSTATUS]
    CUSTEMPLOYEE
    [Query_Customer_Primary].[CUSTEMPLOYEE]
    HISTORY_DATE
    [Query_Customer_Primary].[HISTORY_DATE]
    HISTORY_YEAR
    [Query_Customer_Primary].[HISTORY_YEAR]
    HISTORY_MONTH
    [Query_Customer_Primary].[HISTORY_MONTH]
    CRD
    [Query_Customer_Primary].[CRD]
    TB
    [Query_Customer_Primary].[TB]
    IB
    [Query_Customer_Primary].[IB]
    MAIL_OPT
    [Query_Customer_Primary].[MAIL_OPT]
    BENEFITCHNGDATE
    [Query_Customer_Primary].[BENEFITCHNGDATE]
    BRANCHNAME
    [Query_Customer_Primary].[BRANCHNAME]
    DESCRIPTION
    [Query_Customer_Primary].[BRANCHNAME]
    MEMBER_REGION
    [Query_Customer_Primary].[MEMBER_REGION]
  • Query
    Balance_Query
    • queryHints
    • useForParameterInfo: false
    Data Items
    15
    CUSTOMERID
    [SI_BI_Datawarehouse].[f_sales_history].[CUSTOMERID]
    Loan Balance
    case
    when ([SI_BI_Datawarehouse].[f_sales_history].[PRODUCT_GRP]in ('LOAN','COMLOAN'))
    then (
    total([SI_BI_Datawarehouse].[f_sales_history].[ACCTBALANCE] for [CUSTOMERID],[SI_BI_Datawarehouse].[f_sales_history].[PRODUCT_GRP]))
    else 0
    end
    Aggregate
    total total
    Mort Balance
    case
    when ([SI_BI_Datawarehouse].[f_sales_history].[PRODUCT_GRP]in ('MORTGAGE','COMMORTGAGE'))
    then (
    total([SI_BI_Datawarehouse].[f_sales_history].[ACCTBALANCE] for [CUSTOMERID],[SI_BI_Datawarehouse].[f_sales_history].[PRODUCT_GRP]))
    else 0
    end
    Aggregate
    total total
    LOC Balance
    case
    when ([SI_BI_Datawarehouse].[f_sales_history].[PRODUCT_GRP]in ('LOC','COMLOC'))
    then (
    total([SI_BI_Datawarehouse].[f_sales_history].[ACCTBALANCE] for [CUSTOMERID],[SI_BI_Datawarehouse].[f_sales_history].[PRODUCT_GRP]))
    else 0
    end
    Aggregate
    total total
    Chequing Balance
    case
    when ([SI_BI_Datawarehouse].[f_sales_history].[PRODUCT_GRP]='CHEQUING')
    then (
    total([SI_BI_Datawarehouse].[f_sales_history].[ACCTBALANCE] for [CUSTOMERID],[SI_BI_Datawarehouse].[f_sales_history].[PRODUCT_GRP]))
    else 0
    end
    Aggregate
    total total
    Savings Balance
    case
    when ([SI_BI_Datawarehouse].[f_sales_history].[PRODUCT_GRP]in ('SAVINGS','SFL'))
    then (
    total([SI_BI_Datawarehouse].[f_sales_history].[ACCTBALANCE] for [CUSTOMERID],[SI_BI_Datawarehouse].[f_sales_history].[PRODUCT_GRP]))
    else 0
    end
    Aggregate
    total total
    Share Balance
    case
    when ([SI_BI_Datawarehouse].[f_sales_history].[PRODUCT_GRP]='SHARE')
    then (
    total([SI_BI_Datawarehouse].[f_sales_history].[ACCTBALANCE] for [CUSTOMERID],[SI_BI_Datawarehouse].[f_sales_history].[PRODUCT_GRP]))
    else 0
    end
    Aggregate
    total total
    Term Balance
    case
    when ([SI_BI_Datawarehouse].[f_sales_history].[PRODUCT_GRP]='TERMS')
    then (
    total([SI_BI_Datawarehouse].[f_sales_history].[ACCTBALANCE] for [CUSTOMERID],[SI_BI_Datawarehouse].[f_sales_history].[PRODUCT_GRP]))
    else 0
    end
    Aggregate
    total total
    RSP Balance
    case
    when ([SI_BI_Datawarehouse].[f_sales_history].[PRODUCT_GRP]='RRSP')
    then (
    total([SI_BI_Datawarehouse].[f_sales_history].[ACCTBALANCE] for [CUSTOMERID],[SI_BI_Datawarehouse].[f_sales_history].[PRODUCT_GRP]))
    else 0
    end
    Aggregate
    total total
    RIF Balance
    case
    when ([SI_BI_Datawarehouse].[f_sales_history].[PRODUCT_GRP]='RRIF')
    then (
    total([SI_BI_Datawarehouse].[f_sales_history].[ACCTBALANCE] for [CUSTOMERID],[SI_BI_Datawarehouse].[f_sales_history].[PRODUCT_GRP]))
    else 0
    end
    Aggregate
    total total
    TFSA Balance
    case
    when ([SI_BI_Datawarehouse].[f_sales_history].[PRODUCT_GRP]='TFSA')
    then (
    total([SI_BI_Datawarehouse].[f_sales_history].[ACCTBALANCE] for [CUSTOMERID],[SI_BI_Datawarehouse].[f_sales_history].[PRODUCT_GRP]))
    else 0
    end
    Aggregate
    total total
    Approved LOC Limit
    [SI_BI_Datawarehouse].[f_sales_history].[CREDITLIMIT]
    Aggregate
    total total
    label
    Approved LOC Limit
    INSTITUTIONID
    [SI_BI_Datawarehouse].[f_sales_history].[INSTITUTIONID]
    DESCRIPTION
    [SI_BI_Datawarehouse].[d_branch].[DESCRIPTION]
    BRANCHNAME
    [SI_BI_Datawarehouse].[d_branch].[BRANCHNAME]
    • Detail Filters
      7
    • Disabled
      [SI_BI_Datawarehouse].[f_sales_history].[PRODUCT_GRP]='CHEQUING'
    • Required
      cast([SI_BI_Datawarehouse].[f_sales_history].[HISTORY_DATE],date) =_last_of_month (_add_months(current_date,-1))
    • Optional
      [SI_BI_Datawarehouse].[f_sales_history].[CURRENTBRANCHID] = ?pCurrentBranchID?
    • Optional
      case [INSTITUTIONID]
      when 1 then ([DESCRIPTION]||' - '||'Alterna Savings')
      when 2 then ([DESCRIPTION]||' - '||'Alterna Bank')
      end
      =
      ?pBranchInstitution?
    • Optional
      [BRANCHNAME]=?pBranchName?
    • Optional
      [INSTITUTIONID] in ?pInstitutionID?
    • Required
      [SI_BI_Datawarehouse].[d_customer].[CUSTBENEFIT] in ('Business ','Nonprof','Regular')

  • Query
    QueryCustomer_Fin
    Join 
    [Query_Customer].[CUSTOMERID] = [Balance_Query].[CUSTOMERID]
    refQuery
    Query_Customer
    1:1
    refQuery
    Balance_Query
    0:1
    • queryHints
    • useForParameterInfo: false
    Data Items
    49
    Member #
    [Query_Customer].[Member #]
    INSTITUTION
    [Query_Customer].[INSTITUTION]
    MEMBER_REGION
    [Query_Customer].[MEMBER_REGION]
    CUSTFIRSTNAME
    [Query_Customer].[CUSTFIRSTNAME]
    CUSTLASTNAME
    [Query_Customer].[CUSTLASTNAME]
    CUSTOMERID
    [Query_Customer].[CUSTOMERID]
    Address Type
    [Query_Customer].[ADDRESSTYPE]
    label
    Address Type
    Address
    [Query_Customer].[Address]
    City
    [Query_Customer].[CITY]
    label
    City
    Province
    [Query_Customer].[STATEDESC]
    label
    Province
    Country
    [Query_Customer].[COUNTRYDESC]
    label
    Country
    Postal Code
    [Query_Customer].[POSTALCODE]
    label
    Postal Code
    Custodian
    [Query_Customer].[CUSTODIAN]
    Language
    [Query_Customer].[CUSTLANGUAGEPREF]
    label
    Language
    DOB
    cast([Query_Customer].[CUSTDATEOFBIRTH],date)
    label
    DOB
    Phone Type
    [Query_Customer].[PHONETYPE]
    label
    Phone Type
    Area Code
    [Query_Customer].[PHONEAREACODE]
    label
    Area Code
    Number
    [Query_Customer].[PHONENUMBER]
    label
    Number
    Ext.
    [Query_Customer].[PHONEEXTENSION]
    label
    Ext.
    DOD
    cast([Query_Customer].[CUSTDECEASEDDATE],date)
    label
    DOD
    Gender
    [Query_Customer].[CUSTGENDER]
    label
    Gender
    Join Date
    cast([Query_Customer].[CUSTJOINEDDATE],date)
    label
    Join Date
    Close Date
    cast([Query_Customer].[CUSTCLOSEDATE],date)
    label
    Close Date
    Book Status
    [Query_Customer].[CUSTBOOKSTATUS]
    label
    Book Status
    Benefit
    [Query_Customer].[CUSTBENEFIT]
    label
    Benefit
    Customer Type
    [Query_Customer].[CUSTTYPE]
    label
    Customer Type
    Status
    [Query_Customer].[CUSTSTATUS]
    label
    Status
    Is Employee
    [Query_Customer].[CUSTEMPLOYEE]
    DESCRIPTION
    CASE [Query_Customer].[BRANCHNAME]
    when 'admin' then 'Administration Office'
    when 'Bay' then 'Bay Street Branch'
    when 'Bells' then 'Bells Corners Branch'
    when 'Billng' then 'Billings Bridge Branch'
    when 'Bolton' then 'Bolton Branch'
    when 'Bramal' then 'Bramalea Branch'
    when 'Centre' then 'Centretown Community Branch'
    when 'Danfth' then 'Danforth Branch'
    when 'Gtneau' then 'Gatineau Branch'
    when 'Hull' then 'Hull Branch'
    when 'Kngstn' then 'Kingston Branch'
    when 'Lkshor' then 'Lakeshore Branch'
    when 'Merivl' then 'Merivale Road Branch'
    when 'Nation' then 'National Branch'
    when 'NrthBa' then 'North Bay Branch'
    when 'Online' then 'Online Branch'
    when 'Orlean' then 'Orléans Branch'
    when 'Pembrk' then 'Pembroke Branch'
    when 'PDV' then 'Place De Ville Branch'
    when 'Ryrson' then 'Ryerson Branch'
    when 'Scarbo' then 'Scarborough Branch'
    when 'Sheprd' then 'Shepard Branch'
    when 'SthKey' then 'South Keys Branch'
    when 'StLaur' then 'St. Laurent Branch'
    when 'Street' then 'Streetsville Branch'
    when 'Tunney' then 'Tunney''s Pasture Branch'
    when 'Westbro' then 'Westboro Community Banking Centre'
    when 'York' then 'York Branch'
    when 'Drydn' then 'Dryden Branch'
    when 'Duttn' then 'Dutton Branch'
    when 'Fort' then 'Fort Frances Branch'
    when 'Ignac' then 'Ignace Branch'
    when 'Petrb' then 'Peterborough Community Savings'
    when 'Rainy' then 'Rainy River Branch'
    when 'Sioux' then 'Sioux Lookout Branch'
    when 'Tbay' then 'Thunder Bay Branch'
    when 'Thame' then 'Thamesville Branch'
    when 'Wards' then 'Wardsville Branch'
    when 'Drydn' then 'Dryden Branch'
    when 'Duttn' then 'Dutton Branch'
    when 'Fort' then 'Fort Frances Branch'
    when 'Ignac' then 'Ignace Branch'
    when 'Petrb' then 'Peterborough Community Savings'
    when 'Rainy' then 'Rainy River Branch'
    when 'Sioux' then 'Sioux Lookout Branch'
    when 'Tbay' then 'Thunder Bay Branch'
    when 'Thame' then 'Thamesville Branch'
    when 'Wards' then 'Wardsville Branch'
    when 'Drydn' then 'Dryden Branch'
    when 'Duttn' then 'Dutton Branch'
    when 'Fort' then 'Fort Frances Branch'
    when 'Ignac' then 'Ignace Branch'
    when 'Petrb' then 'Peterborough Community Savings'
    when 'Rainy' then 'Rainy River Branch'
    when 'Sioux' then 'Sioux Lookout Branch'
    when 'Tbay' then 'Thunder Bay Branch'
    when 'Thame' then 'Thamesville Branch'
    when 'Wards' then 'Wardsville Branch'
    when 'Drydn' then 'Dryden Branch'
    when 'Duttn' then 'Dutton Branch'
    when 'Fort' then 'Fort Frances Branch'
    when 'Ignac' then 'Ignace Branch'
    when 'Petrb' then 'Peterborough Community Savings'
    when 'Rainy' then 'Rainy River Branch'
    when 'Sioux' then 'Sioux Lookout Branch'
    when 'Tbay' then 'Thunder Bay Branch'
    when 'Thame' then 'Thamesville Branch'
    when 'Wards' then 'Wardsville Branch'
    when 'WFG' then 'WFG Affinity Services'
    when 'ComSrv' then 'Commercial Services Branch'
    when 'CFF' then 'Affinity'
    else [Query_Customer].[BRANCHNAME]
    end
    CRD
    [Query_Customer].[CRD]
    TB
    [Query_Customer].[TB]
    IB
    [Query_Customer].[IB]
    MAIL_OPT
    [Query_Customer].[MAIL_OPT]
    BENEFITCHNGDATE
    cast([Query_Customer].[BENEFITCHNGDATE],date)
    Loan Balance
    [Balance_Query].[Loan Balance]
    Mort Balance
    [Balance_Query].[Mort Balance]
    LOC Balance
    [Balance_Query].[LOC Balance]
    Approved LOC Limit
    [Balance_Query].[Approved LOC Limit]
    Chequing Balance
    [Balance_Query].[Chequing Balance]
    Savings Balance
    [Balance_Query].[Savings Balance]
    Share Balance
    [Balance_Query].[Share Balance]
    Term Balance
    [Balance_Query].[Term Balance]
    RSP Balance
    [Balance_Query].[RSP Balance]
    RIF Balance
    [Balance_Query].[RIF Balance]
    TFSA Balance
    [Balance_Query].[TFSA Balance]
    Total Credit
    [Loan Balance]+[Mort Balance]+[LOC Balance]
    Aggregate
    calculated calculated
    Total Demand
    [Chequing Balance]+[Savings Balance]
    Aggregate
    calculated calculated
    Total Deposit
    [Chequing Balance]+[Savings Balance]+[Term Balance]+[RSP Balance]+[RIF Balance]+[TFSA Balance]
    Aggregate
    calculated calculated
    Total FUM
    [Loan Balance]+[Mort Balance]+[LOC Balance]+[Chequing Balance]+[Savings Balance]+[Term Balance]+[RSP Balance]+[RIF Balance]+[TFSA Balance]
    Aggregate
    calculated calculated
    • Detail Filters
      1
    • Disabled
      [CUSTOMERID]=60006642
  • Query
    Query_Custodian
    Join 
    [Query_d_Customer].[CUSTASSIGNEDTO] = [Query_d_User].[CUSTASSIGNEDTO]
    refQuery
    Query_d_Customer
    1:1
    refQuery
    Query_d_User
    0:1
    • queryHints
    • useForParameterInfo: false
    Data Items
    9
    CURRENTBRANCHID
    [Query_d_Customer].[CURRENTBRANCHID]
    BRANCH_CUSTODIAN
    IF
    ([CUSTASSIGNEDTO] in ('1','2') or[CUSTASSIGNEDTO] is null )
    THEN
    ([CURRENTBRANCHID] || '-Missing')
    ELSE
    ([CURRENTBRANCHID]|| '-' || cast([CUSTASSIGNEDTO], varchar(10)))
    CUSTODIAN
    IF( [CUSTASSIGNEDTO] in ('1','2') or [CUSTASSIGNEDTO] is null )
    THEN ('Missing') ELSE ((([USERFIRSTNAME]|| ' ')) + [USERLASTNAME])
    CUSTODIAN_1
    IF ([CUSTASSIGNEDTO] in ('1','2') or [CUSTASSIGNEDTO] is null )
    THEN( 'Missing') ELSE (SUBSTRING([USERFIRSTNAME], 1, 1) )
    CUSTOMERID
    [Query_d_Customer].[CUSTOMERID]
    CUSTASSIGNEDTO
    [Query_d_Customer].[CUSTASSIGNEDTO]
    USERLASTNAME
    [Query_d_User].[USERLASTNAME]
    USERFIRSTNAME
    [Query_d_User].[USERFIRSTNAME]
    MEMBER_REGION
    [Query_d_Customer].[MEMBER_REGION]
    • Detail Filters
      2
    • Optional
      [CUSTODIAN_1]=?pCustodian1?
    • Optional
      [BRANCH_CUSTODIAN]=?pCustodian?
  • Query
    Query_Customer_Primary
    • queryHints
    • useForParameterInfo: false
    Data Items
    46
    CUSTOMERID
    [SI_BI_Datawarehouse].[f_customer_hist].[CUSTOMERID]
    Member #
    substring(cast([SI_BI_Datawarehouse].[f_customer_hist].[CUSTOMERID],varchar(50)),1,char_length(cast([SI_BI_Datawarehouse].[f_customer_hist].[CUSTOMERID],varchar(50)))-1)
    label
    Member #
    INSTITUTIONID
    [SI_BI_Datawarehouse].[f_customer_hist].[INSTITUTIONID]
    INSTITUTION
    case [SI_BI_Datawarehouse].[f_customer_hist].[INSTITUTIONID]
    when (1) then ('Alterna Savings')
    when (2) then ('Alterna Bank')
    end
    CURRENTBRANCHID
    [SI_BI_Datawarehouse].[f_customer_hist].[CURRENTBRANCHID]
    CUSTFIRSTNAME
    [SI_BI_Datawarehouse].[f_customer_hist].[CUSTFIRSTNAME]
    CUSTLASTNAME
    [SI_BI_Datawarehouse].[f_customer_hist].[CUSTLASTNAME]
    addr1
    IF(char_length([ADDRESSLINE1])>1)
    THEN ([ADDRESSLINE1]||', '||[ADDRESSLINE2])
    ELSE ([ADDRESSLINE2])
    ADDRESSTYPE
    [SI_BI_Datawarehouse].[f_customer_hist].[ADDRESSTYPE]
    Address
    CASE
    WHEN (char_length([ADDRESSLINE3])>1) then ([addr1]||' '||[ADDRESSLINE3])
    ELSE ([addr1])
    end
    ADDRESSLINE1
    [SI_BI_Datawarehouse].[f_customer_hist].[ADDRESSLINE1]
    ADDRESSLINE2
    [SI_BI_Datawarehouse].[f_customer_hist].[ADDRESSLINE2]
    ADDRESSLINE3
    [SI_BI_Datawarehouse].[f_customer_hist].[ADDRESSLINE3]
    ADDRESSLINE4
    [SI_BI_Datawarehouse].[f_customer_hist].[ADDRESSLINE4]
    ADDRUNITNO
    [SI_BI_Datawarehouse].[f_customer_hist].[ADDRUNITNO]
    CITY
    [SI_BI_Datawarehouse].[f_customer_hist].[CITY]
    COUNTRYDESC
    [SI_BI_Datawarehouse].[f_customer_hist].[COUNTRYDESC]
    POSTALCODE
    [SI_BI_Datawarehouse].[f_customer_hist].[POSTALCODE]
    CUSTASSIGNEDTO
    [SI_BI_Datawarehouse].[f_customer_hist].[CUSTASSIGNEDTO]
    CUSTLANGUAGEPREF
    [SI_BI_Datawarehouse].[f_customer_hist].[CUSTLANGUAGEPREF]
    CUSTDATEOFBIRTH
    [SI_BI_Datawarehouse].[f_customer_hist].[CUSTDATEOFBIRTH]
    PHONETYPE
    [SI_BI_Datawarehouse].[f_customer_hist].[PHONETYPE]
    PHONEAREACODE
    [SI_BI_Datawarehouse].[f_customer_hist].[PHONEAREACODE]
    PHONENUMBER
    [SI_BI_Datawarehouse].[f_customer_hist].[PHONENUMBER]
    PHONEEXTENSION
    [SI_BI_Datawarehouse].[f_customer_hist].[PHONEEXTENSION]
    CUSTDECEASEDDATE
    cast([SI_BI_Datawarehouse].[f_customer_hist].[CUSTDECEASEDDATE],date)
    CUSTJOINEDDATE
    [SI_BI_Datawarehouse].[f_customer_hist].[CUSTJOINEDDATE]
    CUSTCLOSEDATE
    [SI_BI_Datawarehouse].[f_customer_hist].[CUSTCLOSEDATE]
    CUSTGENDER
    [SI_BI_Datawarehouse].[f_customer_hist].[CUSTGENDER]
    CUSTBOOKSTATUS
    [SI_BI_Datawarehouse].[f_customer_hist].[CUSTBOOKSTATUS]
    CUSTOPENCLOSED
    [SI_BI_Datawarehouse].[f_customer_hist].[CUSTOPENCLOSED]
    CUSTBENEFIT
    [SI_BI_Datawarehouse].[f_customer_hist].[CUSTBENEFIT]
    CUSTTYPE
    [SI_BI_Datawarehouse].[f_customer_hist].[CUSTTYPE]
    STATEDESC
    CASE [SI_BI_Datawarehouse].[f_customer_hist].[STATEDESC]
    WHEN 'AB' THEN 'Alberta'
    WHEN 'BC' THEN 'British Columbia'
    WHEN 'MB' THEN 'Manitoba'
    WHEN 'NB' THEN 'New Brunswick'
    WHEN 'NL' THEN 'Newfoundland and Labrador'
    WHEN 'NT' THEN 'Northwest Territories'
    WHEN 'NS' THEN 'Nova Scotia'
    WHEN 'NU' THEN 'Nunavut (Territory) '
    WHEN 'ON' THEN 'Ontario'
    WHEN 'PE' THEN 'Prince Edward Island'
    WHEN 'QC' THEN 'Quebec'
    WHEN 'SK' THEN 'Saskatchewan'
    WHEN 'YT' THEN 'Yukon'
    WHEN 'AL' THEN 'Alabama'
    WHEN 'AK' THEN 'Alaska'
    WHEN 'AZ' THEN 'Arizona'
    WHEN 'AR' THEN 'Arkansas'
    WHEN 'CA' THEN 'California'
    WHEN 'CO' THEN 'Colorado'
    WHEN 'CT' THEN 'Connecticut'
    WHEN 'DE' THEN 'Delaware'
    WHEN 'FL' THEN 'Florida'
    WHEN 'GA' THEN 'Georgia'
    WHEN 'HI' THEN 'Hawaii'
    WHEN 'ID' THEN 'Idaho'
    WHEN 'IL' THEN 'Illinois'
    WHEN 'IN' THEN 'Indiana'
    WHEN 'IA' THEN 'Iowa'
    WHEN 'KS' THEN 'Kansas'
    WHEN 'KY' THEN 'Kentucky'
    WHEN 'LA' THEN 'Louisiana'
    WHEN 'ME' THEN 'Maine'
    WHEN 'MD' THEN 'Maryland'
    WHEN 'MA' THEN 'Massachusetts'
    WHEN 'MI' THEN 'Michigan'
    WHEN 'MN' THEN 'Minnesota'
    WHEN 'MS' THEN 'Mississippi'
    WHEN 'MO' THEN 'Missouri'
    WHEN 'MT' THEN 'Montana'
    WHEN 'NE' THEN 'Nebraska'
    WHEN 'NV' THEN 'Nevada'
    WHEN 'NH' THEN 'New Hampshire'
    WHEN 'NJ' THEN 'new Jersey'
    WHEN 'NM' THEN 'New Mexico'
    WHEN 'NY' THEN 'New York'
    WHEN 'NC' THEN 'North Carolina'
    WHEN 'ND' THEN 'North Dakota'
    WHEN 'OH' THEN 'Ohio'
    WHEN 'OK' THEN 'Oklahoma'
    WHEN 'OR' THEN 'Oregon'
    WHEN 'PA' THEN 'Pennsylvania'
    WHEN 'RI' THEN 'Rhode Island'
    WHEN 'SC' THEN 'South Carolina'
    WHEN 'SD' THEN 'South Dakota'
    WHEN 'TN' THEN 'Tennessee'
    WHEN 'TX' THEN 'Texas'
    WHEN 'UT' THEN 'Utah'
    WHEN 'VT' THEN 'Vermont'
    WHEN 'VA' THEN 'Virginia'
    WHEN 'WA' THEN 'Washington'
    WHEN 'WV' THEN 'West Virginia'
    WHEN 'WI' THEN 'Wisonsin'
    WHEN 'WY' THEN 'Wyoming'
    WHEN 'AS' THEN 'American Samoa'
    WHEN 'DC' THEN 'District of Columbia'
    WHEN 'FM' THEN 'Federated States of Micronesia'
    WHEN 'GU' THEN 'Guam'
    WHEN 'MH' THEN 'Marshall Islands'
    WHEN 'MP' THEN 'Northern Mariana Islands'
    WHEN 'PW' THEN 'Palau'
    WHEN 'PR' THEN 'Puerto Rico'
    WHEN 'VI' THEN 'Virgin Islands'
    WHEN 'AE' THEN 'Armed Forces Europe, the Middle East, Canada'
    WHEN 'AA' THEN 'Armed Forces Americas'
    WHEN 'AP' THEN 'Armed Forces pacific'
    ELSE [SI_BI_Datawarehouse].[f_customer_hist].[STATEDESC]
    END
    CUSTSTATUS
    [SI_BI_Datawarehouse].[f_customer_hist].[CUSTSTATUS]
    CUSTEMPLOYEE
    [SI_BI_Datawarehouse].[f_customer_hist].[CUSTEMPLOYEE]
    HISTORY_DATE
    [SI_BI_Datawarehouse].[f_customer_hist].[HISTORY_DATE]
    HISTORY_YEAR
    [SI_BI_Datawarehouse].[f_customer_hist].[HISTORY_YEAR]
    HISTORY_MONTH
    [SI_BI_Datawarehouse].[f_customer_hist].[HISTORY_MONTH]
    CRD
    [SI_BI_Datawarehouse].[f_customer_hist].[CRD]
    TB
    [SI_BI_Datawarehouse].[f_customer_hist].[TB]
    IB
    [SI_BI_Datawarehouse].[f_customer_hist].[IB]
    MAIL_OPT
    [SI_BI_Datawarehouse].[f_customer_hist].[MAIL_OPT]
    BENEFITCHNGDATE
    [SI_BI_Datawarehouse].[f_customer_hist].[BENEFITCHNGDATE]
    BRANCHNAME
    [SI_BI_Datawarehouse].[f_customer_hist].[BRANCHNAME]
    MEMBER_REGION
    [SI_BI_Datawarehouse].[f_customer_hist].[MEMBER_REGION]
    • Detail Filters
      23
    • Optional
      [CURRENTBRANCHID] = ?pCurrentBranchID?
    • Optional
      [INSTITUTIONID] in ?pInstitutionID?
    • Optional
      [SI_BI_Datawarehouse].[f_customer_hist].[CUSTOPENCLOSED]= ?pCustOpenClosed?
    • Optional
      [SI_BI_Datawarehouse].[f_customer_hist].[CUSTMTDSTATUS]=?pCustMTDStatus?
    • Disabled
      [CUSTBENEFIT] in ?pCust_Benefit?
    • Required
      [CUSTBENEFIT] in ('Business ','Nonprof','Regular')

    • Optional
      [SI_BI_Datawarehouse].[f_customer_hist].[BRANCHNAME]=?pBranchName?
    • Optional
      [CUSTTYPE] in ?pCustType?
    • Optional
      [CUSTSTATUS] in ?pMemberStatus?
    • Optional
      case when ([SI_BI_Datawarehouse].[f_customer_hist].[CUSTAGE] between 0
      and 4) then '0-4 years' when ([SI_BI_Datawarehouse].[f_customer_hist].[CUSTAGE] between 5
      and 9) then '05-9 years' when ([SI_BI_Datawarehouse].[f_customer_hist].[CUSTAGE] between 10
      and 14) then '10-14 years' when ([SI_BI_Datawarehouse].[f_customer_hist].[CUSTAGE] between 15
      and 19) then '15-19 years' when ([SI_BI_Datawarehouse].[f_customer_hist].[CUSTAGE] between 20
      and 24) then '20-24 years' when ([SI_BI_Datawarehouse].[f_customer_hist].[CUSTAGE] between 25
      and 29) then '25-29 years' when ([SI_BI_Datawarehouse].[f_customer_hist].[CUSTAGE] between 30
      and 34) then '30-34 years' when ([SI_BI_Datawarehouse].[f_customer_hist].[CUSTAGE] between 35
      and 39) then '35-39 years' when ([SI_BI_Datawarehouse].[f_customer_hist].[CUSTAGE] between 40
      and 44) then '40-44 years' when ([SI_BI_Datawarehouse].[f_customer_hist].[CUSTAGE] between 45
      and 49) then '45-49 years' when ([SI_BI_Datawarehouse].[f_customer_hist].[CUSTAGE] between 50
      and 54) then '50-54 years' when ([SI_BI_Datawarehouse].[f_customer_hist].[CUSTAGE] between 55
      and 59) then '55-59 years' when ([SI_BI_Datawarehouse].[f_customer_hist].[CUSTAGE] between 60
      and 64) then '60-64 years' when ([SI_BI_Datawarehouse].[f_customer_hist].[CUSTAGE] between 65
      and 69) then '65-69 years' when ([SI_BI_Datawarehouse].[f_customer_hist].[CUSTAGE] between 70
      and 74) then '70-74 years' when ([SI_BI_Datawarehouse].[f_customer_hist].[CUSTAGE] between 75
      and 79) then '75-79 years' when ([SI_BI_Datawarehouse].[f_customer_hist].[CUSTAGE] between 80
      and 84) then '80-84 years' when ([SI_BI_Datawarehouse].[f_customer_hist].[CUSTAGE] between 85
      and 89) then '85-89 years' when ([SI_BI_Datawarehouse].[f_customer_hist].[CUSTAGE] > 89) then '90+ years' else 'UNKNOWN' end =?pAGE_Range?
    • Optional
      [CUSTLANGUAGEPREF]=?pLanguage?
    • Optional
      [CUSTGENDER]=?pGender?
    • Required
      cast([HISTORY_DATE],date) =_last_of_month (_add_months(current_date,-1))
    • Optional
      [POSTALCODE]=?pPostalCode?
    • Optional
      [STATEDESC] in ?pState?
    • Optional
      [COUNTRYDESC] in ?pCountry?
    • Required
      [SI_BI_Datawarehouse].[f_customer_hist].[CUSTBOOKSTATUS] = 'CUSTOMER' and
      [SI_BI_Datawarehouse].[f_customer_hist].[CUSTDELETED]='N'
    • Optional
      case when [SI_BI_Datawarehouse].[f_customer_hist].[COUNTRYDESC] = 'Canada' then 'Canada' when [SI_BI_Datawarehouse].[f_customer_hist].[COUNTRYDESC]= 'United States' then 'United States' else 'International' END =?pCountryZone?
    • Optional
      substring([SI_BI_Datawarehouse].[f_customer_hist].[POSTALCODE], 1, 1) in ?pPostal_Code_1?
    • Optional
      substring([SI_BI_Datawarehouse].[f_customer_hist].[POSTALCODE], 1, 2) in ?pPostal_Code_2?
    • Optional
      substring([SI_BI_Datawarehouse].[f_customer_hist].[POSTALCODE], 1, 3) in ?pPostal_Code_3?
    • Optional
      substring([SI_BI_Datawarehouse].[f_customer_hist].[POSTALCODE], 1, 4) in ?pPostal_Code_4?
    • Optional
      substring([SI_BI_Datawarehouse].[f_customer_hist].[POSTALCODE], 1, 5) in ?pPostal_Code_5?
  • Query
    Query_d_Customer
    • queryHints
    • useForParameterInfo: false
    Data Items
    5
    CURRENTBRANCHID
    [SI_BI_Datawarehouse].[d_customer].[CURRENTBRANCHID]
    BRANCH_CUSTODIAN
    IF
    ([CUSTASSIGNEDTO] in ('1','2') or[CUSTASSIGNEDTO] is null )
    THEN
    ([CURRENTBRANCHID] || '-Missing')
    ELSE
    ([CURRENTBRANCHID]|| '-' || cast([CUSTASSIGNEDTO], varchar(10)))
    CUSTOMERID
    [SI_BI_Datawarehouse].[d_customer].[CUSTOMERID]
    CUSTASSIGNEDTO
    [SI_BI_Datawarehouse].[d_customer].[CUSTASSIGNEDTO]
    MEMBER_REGION
    [SI_BI_Datawarehouse].[d_customer].[MEMBER_REGION]
    • Detail Filters
      2
    • Optional
      [CURRENTBRANCHID] in ?pCurrentBranchID?
    • Required
      [SI_BI_Datawarehouse].[d_customer].[CUSTBENEFIT] in ('Business ','Nonprof','Regular')

  • Query
    Query_d_User
    • queryHints
    • useForParameterInfo: false
    Data Items
    4
    CURRENTBRANCHID
    [SI_BI_Datawarehouse].[d_user].[BRANCHID]
    CUSTASSIGNEDTO
    [SI_BI_Datawarehouse].[d_user].[CUSTASSIGNEDTO]
    USERLASTNAME
    [SI_BI_Datawarehouse].[d_user].[USERLASTNAME]
    USERFIRSTNAME
    [SI_BI_Datawarehouse].[d_user].[USERFIRSTNAME]
    • Detail Filters
      1
    • Required
      [CUSTASSIGNEDTO] is not null
  • Query
    Query1
    Data Items
    2
    DESCRIPTION
    [SI_BI_Datawarehouse].[d_branch].[DESCRIPTION]
    BRANCHNAME
    [SI_BI_Datawarehouse].[d_branch].[BRANCHNAME]
  • Query
    Query2
    Data Items
    1
    CUSTBENEFIT
    [SI_BI_Datawarehouse].[d_customer].[CUSTBENEFIT]
    • Detail Filters
      1
    • Required
      [SI_BI_Datawarehouse].[d_customer].[CUSTBENEFIT] in ('Business ','Nonprof','Regular')

Layouts