Deployments
UAT_PCR_Deployment
Reports
Report Name: Status Update on Key CMT Data Dimensions - Case Intake
Overview

  • Queries: 6

    Layouts: 1

  • Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model
Queries
  • Queries 6
    Expressions:
  • Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model
  • Query
    Main Query
    Data Items
    5
    Business Unit
    [Presentation].[VW_STG_DEPARTMENT].[CODE]
    Department Name
    [Presentation].[VW_STG_DEPARTMENT].[NAME_1]
    Count of Intake
    count([Presentation].[Case].[Case Count] for [Business Unit])
    Begin Date
    IF (?Date_Option_p? = '0' ) THEN
    ( ?ParamStartDate?)
    ELSE
    ( _add_months (_first_of_month (current_date),-12)
    )
    End Date
    IF (?Date_Option_p? = '0' ) THEN
    (?ParamEndDate? )
    ELSE
    (_last_of_month (_add_months (current_date, -1) )
    )
    • Detail Filters
      2
    • Required
      [Presentation].[Work Type].[WORK_TYPE_CODE] <> ('WT-058')
    • Required
      (cast(_add_hours ([Presentation].[Case].[DATE_OPEN], -5), date) between [Begin Date] and [End Date]
      and cast(_add_hours ([Presentation].[Case].[DATE_BF], -5), date) is null)
      or
      (
      cast(_add_hours ([Presentation].[Case].[DATE_BF], -5), date) between [Begin Date] and [End Date]
      )
  • Query
    Load Last Date Query
    Data Items
    1
    Latest Valid Data Load Date
    [Presentation].[Data Load Archive].[Latest Valid Data Load Date]
  • Query
    Department_Prompt Query
    Data Items
    3
    Business Unit
    [Presentation].[VW_STG_DEPARTMENT].[CODE]
    Running Count
    cast(running-count ([Business Unit]), varchar(2))
    Departmental Name
    [Running Count]+' . '+[Business Unit] + ' - '+[Presentation].[VW_STG_DEPARTMENT].[NAME_1]
    • Detail Filters
      1
    • Required
      [Business Unit] in ('IAN','PEN','PCH','JUS','DND','CSD','DOE','DFO','EXT','DUS','PCO','SVC','GSS','TBD')
  • Query
    Departmental Population Query
    Data Items
    2
    DEPT_CODE
    [Presentation].[VW_CMT_DEPARTMENT_POPULATION].[(en-zw) DEPT_CODE]
    POPULATION
    [Presentation].[VW_CMT_DEPARTMENT_POPULATION].[(en-zw) POPULATION]
    Aggregate
    total total
  • Query
    Department For Title Query
    Data Items
    3
    Business Unit
    [Presentation].[VW_STG_DEPARTMENT].[CODE]
    Departmental Name
    [Business Unit] + ' - '+[Presentation].[VW_STG_DEPARTMENT].[NAME_1]
    Link
    1
    • Detail Filters
      2
    • Required
      [Business Unit] = (?Department_p?)
    • Required
      [Business Unit] in ('IAN','PEN','PCH','JUS','DND','CSD','DOE','DFO','EXT','DUS','PCO','SVC','GSS','TBD')
  • Query
    Final Query
    Join 
    [Main Query].[Business Unit] = [Departmental Population Query].[DEPT_CODE]
    refQuery
    Main Query
    1:1
    refQuery
    Departmental Population Query
    1:1
    Data Items
    8
    Business Unit
    [Main Query].[Business Unit]
    Departmental Name
    [Main Query].[Department Name]
    Count of Intake
    [Main Query].[Count of Intake]
    Department Population
    [Departmental Population Query].[POPULATION]
    Cases Created Per Active Employee
    [Count of Intake]/[Department Population]
    Rank by Cases Created
    rank([Count of Intake])
    Rank by Cases Created per Active Employee
    rank([Cases Created Per Active Employee])
    Link
    1
    • Detail Filters
      1
    • Required
      [Department Population] > 500
Layouts