Deployments
UAT_PCR_Deployment
Reports
Report Name: Pay Centre Output (Closures & Cancellations)
Overview

  • Queries: 7

    Layouts: 1

  • Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model
Queries
  • Queries 7
    Expressions:
  • Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model
  • Query
    Query-Main
    Data Items
    35
    Case Number
    [Presentation].[Case].[ID]
    Aggregate
    summarize summarize
    Case Count
    [Presentation].[Case].[Case Count]
    Aggregate
    count count
    Processing Centre
    [Presentation].[Case].[PROCESSING_CENTRE]
    Age
    [Presentation].[Case].[CMT_AGE]
    Date Open
    cast(_add_hours ( [Presentation].[Case].[DATE_OPEN],-5 ),varchar(19))
    Date Open-for header
    [Presentation].[Case].[DATE_OPEN]
    BF Date
    cast(_add_hours ( [Presentation].[Case].[DATE_BF],-5 ),varchar(19))
    BF Date-for header
    [Presentation].[Case].[DATE_BF]
    Date Closed
    cast(_add_hours ( [Presentation].[Case].[DATE_CLOSED],-5 ),varchar(19))
    Date Closed-for header
    [Presentation].[Case].[DATE_CLOSED]
    Date Cancelled
    cast(_add_hours ( [Presentation].[Case].[Date Cancelled],-5 ),varchar(19))
    Date Cancelled-for header
    [Presentation].[Case].[Date Cancelled]
    PRI
    [Presentation].[Case].[PRI]
    Work Type Name
    [Presentation].[Work Type].[NAME]
    Sub Type Name
    [Presentation].[Sub Type].[NAME]
    Department
    [Presentation].[VW_STG_DEPARTMENT].[CODE]
    Classification
    [Presentation].[Case].[CLASSIFICATION]
    Team Name-conv-1
    replace ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name], 'Unassigned / Non attribué', 'Team Unassigned / Non attribué')
    Team Name-conv-2
    replace ([Team Name-conv-1], 'Compensation-Advisor-', 'Team ')
    Team Name-for header
    [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]
    Assigned To
    [Presentation].[Case].[ASSIGNEE_CURR]
    EX / Non EX
    [Presentation].[Case].[SUBJECT]
    Action
    Case
    when [Date Closed] is not null then 'Closed'
    when [Date Cancelled] is not null then 'Cancelled'
    else ' '
    end
    Automated-Manual
    Case
    when [Presentation].[Case].[Auto Process Indicator] is null
    then 'Manual'
    else 'Automated'
    end
    Closed-Cancelled Date
    cast(_add_hours ([Presentation].[Case].[Date Closed or Date Cancelled],-5 ), date)
    Previous-MTH
    _add_months (current_date,-1)
    Prev-MTH-start
    _first_of_month ([Previous-MTH])
    Prev-MTH-end
    _last_of_month ([Previous-MTH])
    Month
    datename ({mm}, [Previous-MTH])
    Previous Month calc
    Case #sq($[Language_lookup]{$runLocale})#
    When 'EN' then
    [Month]
    When 'FR' then
    Case
    when [Month] = 'January' then 'janvier'
    when [Month] = 'February' then 'février'
    when [Month] = 'March' then 'mars'
    when [Month] = 'April' then 'avril'
    when [Month] = 'May' then 'mai'
    when [Month] = 'June' then 'juin'
    when [Month] = 'July' then 'juillet'
    when [Month] = 'August' then 'août'
    when [Month] = 'September' then 'septembre'
    when [Month] = 'October' then 'octobre'
    when [Month] = 'November' then 'novembre'
    when [Month] = 'December' then 'décembre'
    end
    end
    Todays Date
    current_date
    Team-Sort-1
    substring([Team Name-conv-2],6)
    Team-Sort-2
    case
    when ( [Team-Sort-1] = 'OGD' ) then '200'
    when ( [Team-Sort-1] = 'Unassigned / Non attribué') then '300'
    else ([Team-Sort-1])
    end
    Team-Sort-3
    cast([Team-Sort-2],Integer)
    Year
    Case
    when [Month] = 'December'
    then _year (current_date) -1
    else _year (current_date)
    End
    • Detail Filters
      3
    • Required
      [Presentation].[Case].[DBID] <> 0
    • Required
      [Presentation].[State].[NAME] in ('Cancelled', 'Closed')
    • Required
      [Closed-Cancelled Date] >= [Prev-MTH-start] and [Closed-Cancelled Date] <= [Prev-MTH-end]
  • Query
    Load Date Query
    Data Items
    1
    Latest Valid Data Load Date
    [Presentation].[Data Load Archive].[Latest Valid Data Load Date]
  • Query
    Query-Mth-cnt
    Data Items
    6
    Case Number
    count([Presentation].[Case].[ID] for report)
    Closed-Cancelled Date
    cast(_add_hours ([Presentation].[Case].[Date Closed or Date Cancelled],-5 ), date)
    Previous-MTH
    _add_months (current_date,-1)
    Prev-MTH-start
    _first_of_month ([Previous-MTH])
    Prev-MTH-end
    _last_of_month ([Previous-MTH])
    Team Name
    [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]
    • Detail Filters
      4
    • Required
      [Presentation].[Case].[DBID] <> 0
    • Required
      [Closed-Cancelled Date] >= [Prev-MTH-start] and [Closed-Cancelled Date] <= [Prev-MTH-end]
    • Disabled
      [Presentation].[Compensation Advisor]
    • Required
      [Presentation].[State].[NAME] in ('Closed', 'Cancelled')
  • Query
    Query-Closed-Manual
    Data Items
    7
    Case Number
    count([Presentation].[Case].[ID] for report)
    Automated-Manual
    Case
    when [Presentation].[Case].[Auto Process Indicator] is null
    then 'Manual'
    else 'Automated'
    end
    Closed-Cancelled Date
    cast(_add_hours ([Presentation].[Case].[Date Closed or Date Cancelled],-5 ), date)
    Previous-MTH
    _add_months (current_date,-1)
    Prev-MTH-start
    _first_of_month ([Previous-MTH])
    Prev-MTH-end
    _last_of_month ([Previous-MTH])
    Team Name
    [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]
    • Detail Filters
      5
    • Required
      [Presentation].[Case].[DBID] <> 0
    • Required
      [Closed-Cancelled Date] >= [Prev-MTH-start] and [Closed-Cancelled Date] <= [Prev-MTH-end]
    • Disabled
      [Presentation].[Compensation Advisor]
    • Required
      [Presentation].[State].[NAME] in ('Closed')
    • Required
      [Automated-Manual] = 'Manual'
  • Query
    Query-Closed-Auto
    Data Items
    7
    Case Number
    count([Presentation].[Case].[ID] for report)
    Automated-Manual
    Case
    when [Presentation].[Case].[Auto Process Indicator] is null
    then 'Manual'
    else 'Automated'
    end
    Closed-Cancelled Date
    cast(_add_hours ([Presentation].[Case].[Date Closed or Date Cancelled],-5 ), date)
    Previous-MTH
    _add_months (current_date,-1)
    Prev-MTH-start
    _first_of_month ([Previous-MTH])
    Prev-MTH-end
    _last_of_month ([Previous-MTH])
    Team Name
    [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]
    • Detail Filters
      5
    • Required
      [Presentation].[Case].[DBID] <> 0
    • Required
      [Closed-Cancelled Date] >= [Prev-MTH-start] and [Closed-Cancelled Date] <= [Prev-MTH-end]
    • Disabled
      [Presentation].[Compensation Advisor]
    • Required
      [Presentation].[State].[NAME] in ('Closed')
    • Required
      [Automated-Manual] = 'Automated'
  • Query
    Query-Cancelled-Manual
    Data Items
    7
    Case Number
    count([Presentation].[Case].[ID] for report)
    Automated-Manual
    Case
    when [Presentation].[Case].[Auto Process Indicator] is null
    then 'Manual'
    else 'Automated'
    end
    Closed-Cancelled Date
    cast(_add_hours ([Presentation].[Case].[Date Closed or Date Cancelled],-5 ), date)
    Previous-MTH
    _add_months (current_date,-1)
    Prev-MTH-start
    _first_of_month ([Previous-MTH])
    Prev-MTH-end
    _last_of_month ([Previous-MTH])
    Team Name
    [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]
    • Detail Filters
      5
    • Required
      [Presentation].[Case].[DBID] <> 0
    • Required
      [Closed-Cancelled Date] >= [Prev-MTH-start] and [Closed-Cancelled Date] <= [Prev-MTH-end]
    • Disabled
      [Presentation].[Compensation Advisor]
    • Required
      [Presentation].[State].[NAME] in ('Cancelled')
    • Required
      [Automated-Manual] = 'Manual'
  • Query
    Query-Cancelled-Auto
    Data Items
    7
    Case Number
    count([Presentation].[Case].[ID] for report)
    Automated-Manual
    Case
    when [Presentation].[Case].[Auto Process Indicator] is null
    then 'Manual'
    else 'Automated'
    end
    Closed-Cancelled Date
    cast(_add_hours ([Presentation].[Case].[Date Closed or Date Cancelled],-5 ), date)
    Previous-MTH
    _add_months (current_date,-1)
    Prev-MTH-start
    _first_of_month ([Previous-MTH])
    Prev-MTH-end
    _last_of_month ([Previous-MTH])
    Team Name
    [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]
    • Detail Filters
      5
    • Required
      [Presentation].[Case].[DBID] <> 0
    • Required
      [Closed-Cancelled Date] >= [Prev-MTH-start] and [Closed-Cancelled Date] <= [Prev-MTH-end]
    • Disabled
      [Presentation].[Compensation Advisor]
    • Required
      [Presentation].[State].[NAME] in ('Cancelled')
    • Required
      [Automated-Manual] = 'Automated'
Layouts