Deployments
UAT_PCR_Deployment
Reports
Report Name: Status Update on Key CMT Data Dimensions - Timeliness of Data Entry In Days
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
    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
    Load Last Date Query
    Data Items
    1
    Latest Valid Data Load Date
    [Presentation].[Data Load Archive].[Latest Valid Data Load Date]
  • Query
    Main Query
    Data Items
    14
    Business Unit
    [Presentation].[VW_STG_DEPARTMENT].[CODE]
    Department Name
    [Presentation].[VW_STG_DEPARTMENT].[NAME_1]
    Total PAR Case Count
    total(count(Case
    when ([Presentation].[Case].[Created by Category] = 'PAR' or [Presentation].[Case].[Created by Category] = 'DIP') then 1
    else null
    end) for [Business Unit])
    Total Worklist Case Count
    total(count(Case
    when ([Presentation].[Case].[Created by Category] = 'Worklist' or [Presentation].[Case].[Created by Category] = 'Liste de travail') then 1
    else null
    end) for [Business Unit])
    PAR Days
    _round (total(Case
    when ([Presentation].[Case].[Created by Category] = 'PAR' or [Presentation].[Case].[Created by Category] = 'DIP') then (_days_between(cast(_add_hours([Presentation].[Case].[DATE_RECEIVED], -5),date) ,cast(_add_hours([Presentation].[Case].[DATE_EFFECTIVE_FROM], -5),date) ) ) else null
    end for [Business Unit]) ,0) / [Total PAR Case Count]
    Worklist Days
    _round (total(Case
    when ([Presentation].[Case].[Created by Category] = 'Worklist' or [Presentation].[Case].[Created by Category] = 'Liste de travail') then (_days_between(cast(_add_hours([Presentation].[Case].[DATE_RECEIVED], -5),date) ,cast(_add_hours([Presentation].[Case].[DATE_EFFECTIVE_FROM], -5),date) ) )
    else null
    end for [Business Unit]), 0) / [Total Worklist Case Count]
    Total OnTime or Delay Days
    total(_days_between(cast(_add_hours([Presentation].[Case].[DATE_RECEIVED], -5),date) ,cast(cast(_add_hours([Presentation].[Case].[DATE_EFFECTIVE_FROM], -5),date) ,date)) for [Business Unit])
    Total Case Count
    total(count([Business Unit]))
    Combined Days
    round( [Total OnTime or Delay Days] /[Total Case Count] , 0 )
    Rank by PAR
    rank ([PAR Days])
    Rank by Worklist
    rank ([Worklist Days])
    Rank Combined
    rank ([Combined Days])
    Begin Date
    IF (?Date_Option_p? = '0' ) THEN
    ( #prompt('ParamStartDate', 'date', 'current_date')# )
    ELSE
    ( _add_months (_first_of_month (current_date),-12)
    )
    End Date
    IF (?Date_Option_p? = '0' ) THEN
    (#prompt('ParamEndDate', 'date', 'current_date')# )
    ELSE
    (_last_of_month (_add_months (current_date, -1) )
    )
    • Detail Filters
      6
    • Required
      cast(_add_hours([Presentation].[Case].[DATE_EFFECTIVE_FROM], -5),date) <= cast('2020-12-31',date)
      and
      cast(_add_hours([Presentation].[Case].[DATE_EFFECTIVE_FROM], -5),date) is not missing
      and
      cast(_add_hours([Presentation].[Case].[DATE_RECEIVED], -5),date) between [Begin Date] and [End Date]
    • Required
      [Presentation].[Case].[Created by Category] not in ('Manual', 'Manuel')
    • Required
      [Presentation].[Case].[DEPARTMENT] is not null
    • Required
      [Presentation].[Sub Type].[TRANSACTION_TYPE] is not null
      and
      [Presentation].[Sub Type].[TRANSACTION_TYPE] in ('01: Promotions / Promotion', '02: Benefit Enrollment for New Hires / Employé nouvellement embauché - avantages sociaux', '03: Terminations / Cessation d''emploi', '04: Disability / Cas d''invalidité', '05: Parental leave / Congé parental', '06: Return from leave / Retour de congé', '07: Acting / Intérim', '08: Employee Transfer / Mutation', '09: Other / Autres')
    • Required
      [Presentation].[VW_STG_DEPARTMENT].[CODE] in ('AGR', 'AHS', 'CAP', 'CDC', 'CES', 'CGC', 'COP', 'CSD', 'DFO', 'DND', 'DOE', 'DUS', 'DVA', 'ESO', 'EXT', 'FIN', 'GSS', 'IAN', 'ICA', 'IMC', 'IRB', 'JUS', 'NED', 'NFB', 'NHW', 'NPB', 'PCH', 'PCO', 'PEN', 'PPD', 'PSC', 'PSP', 'PXR', 'RCM', 'RSN', 'RTC', 'SIR', 'SVC', 'TBD')
    • Required
      [Presentation].[Sub Type].[SUB_TYPE_CODE] not in ('ST-024', 'ST-037', 'ST-041', 'ST-042', 'ST-050', 'ST-071', 'ST-072', 'ST-082', 'ST-083', 'ST-086', 'ST-089', 'ST-092', 'ST-093', 'ST-094', 'ST-095', 'ST-097', 'ST-098', 'ST-100', 'ST-118', 'ST-121', 'ST-122', 'ST-123', 'ST-124', 'ST-125', 'ST-132', 'ST-147', 'ST-149', 'ST-150', 'ST-153', 'ST-154', 'ST-155', 'ST-161', 'ST-162', 'ST-163', 'ST-167', 'ST-169', 'ST-170', 'ST-171', 'ST-182', 'ST-183', 'ST-188', 'ST-189', 'ST-191', 'ST-192', 'ST-193', 'ST-194', 'ST-195', 'ST-196', 'ST-197', 'ST-198', 'ST-199', 'ST-200', 'ST-201', 'ST-209', 'ST-210', 'ST-211', 'ST-212', 'ST-213', 'ST-214', 'ST-215', 'ST-216', 'ST-217', 'ST-222', 'ST-223', 'ST-224', 'ST-225', 'ST-232', 'ST-233', 'ST-234', 'ST-235', 'ST-236', 'ST-240', 'ST-241', 'ST-247', 'ST-262', 'ST-268', 'ST-269', 'ST-273', 'ST-280', 'ST-306', 'ST-504', 'ST-330', 'ST-505', 'ST-508', 'ST-509', 'ST-517', 'ST-518', 'ST-525', 'ST-526', 'ST-527', 'ST-538', 'ST-540', 'ST-611')
  • 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
    14
    Business Unit
    [Main Query].[Business Unit]
    Department Name
    [Main Query].[Department Name]
    Departmental Population
    [Departmental Population Query].[POPULATION]
    Total PAR Case Count
    [Main Query].[Total PAR Case Count]
    Total Worklist Case Count
    [Main Query].[Total Worklist Case Count]
    PAR Days
    [Main Query].[PAR Days]
    Worklist Days
    [Main Query].[Worklist Days]
    Total Case Count
    [Main Query].[Total Case Count]
    Combined Days
    [Main Query].[Combined Days]
    Rank by PAR
    [Main Query].[Rank by PAR]
    Rank by Worklist
    [Main Query].[Rank by Worklist]
    Rank Combined
    [Main Query].[Rank Combined]
    Total OnTime or Delay Days
    [Main Query].[Total OnTime or Delay Days]
    Link
    1
    • Detail Filters
      1
    • Required
      [Departmental Population] > 500
Layouts