Deployments
UAT_PCR_Deployment
Reports
Report Name: Status Update on Key CMT Data Dimensions - Timeliness of Data Entry by Transaction Type For Bahra
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
    Department_Prompt Query
    Data Items
    4
    Business Unit
    [Presentation].[VW_STG_DEPARTMENT].[CODE]
    Departmental Name
    [Running Count]+' . '+[Business Unit] + ' - '+[Presentation].[VW_STG_DEPARTMENT].[NAME_1]
    Running Count
    cast(running-count ([Business Unit]), varchar(2))
    Department Count
    count([Business Unit])
    • 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
    Staging For Last 12 Month Query
    Data Items
    10
    Received Date
    cast(_add_hours([Presentation].[Case].[DATE_RECEIVED], -5),date)
    Begin Date
    _add_months (_first_of_month (current_date),-12)
    End Date
    _last_of_month (_add_months (current_date, -1))
    Transaction Type
    [Presentation].[Sub Type].[TRANSACTION_TYPE]
    Department Name
    [Presentation].[VW_STG_DEPARTMENT].[NAME_1]
    Business Unit
    [Presentation].[VW_STG_DEPARTMENT].[CODE]
    Case Count
    [Presentation].[Case].[Case Count]
    Aggregate
    count count
    Created by Category
    [Presentation].[Case].[Created by Category]
    Date Effective From
    cast(_add_hours([Presentation].[Case].[DATE_EFFECTIVE_FROM], -5),date)
    Date Received
    cast(_add_hours([Presentation].[Case].[DATE_RECEIVED], -5),date)
    • Detail Filters
      6
    • Optional
      [Business Unit] in (?Department_p?)
    • Required
      ([Received Date] between [Begin Date] and [End Date])
      and [Date Effective From] <= cast('2022-12-31',date)
      and [Date Effective From] is not null
    • Required
      [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')
    • Required
      [Created by Category] not in ('Manual', 'Manuel')
  • Query
    DateDim_For Last 12 Month
    Data Items
    7
    Full Date
    cast([Presentation].[Date].[FullDate], date)
    Begin Date
    _add_months (_first_of_month (current_date),-12)
    End Date
    _last_of_month (_add_months (current_date, -1))
    Month Name Short
    [Presentation].[Date].[(en-zw) MonthName_Short]
    Calendar Year
    [Presentation].[Date].[CalendarYear]
    MonthName_Year
    trim(both ' ', [Month Name Short])+'-'+substring (cast([Calendar Year], varchar(4)), 3,2)
    Calendar Year Month
    [Presentation].[Date].[CalendarYearMonth]
    • Detail Filters
      1
    • Required
      [Full Date] between [Begin Date] and [End Date]
      and
      [Full Date] <= 2022-12-31
  • Query
    Last 12 Month Query
    Join 
    [Staging For Last 12 Month Query].[Received Date] = [DateDim_For Last 12 Month].[Full Date]
    refQuery
    Staging For Last 12 Month Query
    1:1
    refQuery
    DateDim_For Last 12 Month
    1:1
    Data Items
    13
    Transaction Type
    [Staging For Last 12 Month Query].[Transaction Type]
    Business Unit
    [Staging For Last 12 Month Query].[Business Unit]
    Department Name
    [Staging For Last 12 Month Query].[Department Name]
    Case Count
    [Staging For Last 12 Month Query].[Case Count]
    Created by Category
    [Staging For Last 12 Month Query].[Created by Category]
    MonthName_Year
    [DateDim_For Last 12 Month].[MonthName_Year]
    Calendar Year Month
    [DateDim_For Last 12 Month].[Calendar Year Month]
    PAR Ontime Count
    total(
    CASE WHEN
    (
    _days_between([Staging For Last 12 Month Query].[Date Received], [Staging For Last 12 Month Query].[Date Effective From]) <= 0
    and
    ([Created by Category] = 'PAR' or [Created by Category] = 'DIP')
    )
    THEN [Case Count]
    ELSE 0
    END
    )
    Worklist Ontime Count
    total(
    CASE WHEN
    (
    _days_between([Staging For Last 12 Month Query].[Date Received], [Staging For Last 12 Month Query].[Date Effective From]) <= 0
    and
    ([Created by Category] = 'Worklist' or [Created by Category] = 'Liste de travail')
    )
    THEN [Case Count]
    ELSE 0
    END
    )
    Total PAR Case Count
    total(Case
    when ([Created by Category] = 'PAR' or [Created by Category] = 'DIP') then [Case Count]
    else null
    end)
    PAR Monthly Rate
    [PAR Ontime Count] / [Total PAR Case Count]
    Aggregate
    average average
    Total Worklist Case Count
    total(Case
    when ([Created by Category] = 'Worklist' or [Created by Category] = 'Liste de travail') then [Case Count]
    else null
    end)
    Worklist Monthly Rate
    [Worklist Ontime Count] / [Total Worklist Case Count]
    Aggregate
    average average
  • Query
    Department For Burst Query
    Data Items
    2
    Business Unit
    [Presentation].[VW_STG_DEPARTMENT].[CODE]
    Departmental Name
    [Business Unit] + ' - '+[Presentation].[VW_STG_DEPARTMENT].[NAME_1]
    • Detail Filters
      2
    • Optional
      [Business Unit] in (?Department_p?)
    • Required
      [Business Unit] in ('IAN','PEN','PCH','JUS','DND','CSD','DOE','DFO','EXT','DUS','PCO','SVC','GSS','TBD')
  • Query
    Burst Query
    Data Items
    3
    Business Unit
    [Presentation].[VW_STG_DEPARTMENT].[CODE]
    Departmental Name
    [Business Unit] + ' - '+[Presentation].[VW_STG_DEPARTMENT].[NAME_1]
    Bursing Key
    'CAMID("::Everyone")'
    • Detail Filters
      1
    • Required
      [Business Unit] in ('IAN','PEN','PCH','JUS','DND','CSD','DOE','DFO','EXT','DUS','PCO','SVC','GSS','TBD')
Layouts