Deployments
UAT_PCR_Deployment
Reports
Report Name: Departmental Timeliness Report
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
    All Transactions
    • queryHints
    • crossProductAllowed: allow
    Data Items
    8
    Group Sort Index
    2
    TRANSACTION_TYPE
    [Presentation].[Sub Type].[TRANSACTION_TYPE]
    Month
    cast(year( [Presentation].[Case].[DATE_RECEIVED] ),varchar(4))
    + '-'+
    right('00'+cast(month( [Presentation].[Case].[DATE_RECEIVED] ),varchar(2)),2)
    Department
    'All/Tous'
    CODE
    'All/Tous'
    On Time Count
    if (cast([Presentation].[Case].[DATE_RECEIVED], date)<=cast([Presentation].[Case].[DATE_EFFECTIVE_FROM], date)) then (1) else (0)
    Aggregate
    total total
    Rollup
    total total
    Case Count
    [Presentation].[Case].[Case Count]
    Aggregate
    count count
    Rollup
    total total
    Total Days of Delay
    _days_between([Presentation].[Case].[DATE_RECEIVED],[Presentation].[Case].[DATE_EFFECTIVE_FROM])
    Aggregate
    total total
    Rollup
    average average
    • Detail Filters
      6
    • 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].[Case].[DATE_EFFECTIVE_FROM] <=2022-12-31 and [Presentation].[Case].[DATE_EFFECTIVE_FROM] is not null
    • 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? = 'PAR' and [Presentation].[Case].[Created by Category] in ('PAR', 'DIP')
      or
      ?Created By? = 'Worklist' and [Presentation].[Case].[Created by Category] in ('Worklist', 'Liste de travail')
    • Required
      [Presentation].[Case].[DATE_RECEIVED] between _first_of_month(_add_months(current_date, -18)) and
      _last_of_month(_add_months(current_date, -1))
    • Required
      [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')
  • Query
    Dept. Transactions
    • queryHints
    • crossProductAllowed: allow
    Data Items
    8
    Group Sort Index
    1
    TRANSACTION_TYPE
    [Presentation].[Sub Type].[TRANSACTION_TYPE]
    Month
    cast(year( [Presentation].[Case].[DATE_RECEIVED] ),varchar(4))
    + '-'+
    right('00'+cast(month( [Presentation].[Case].[DATE_RECEIVED] ),varchar(2)),2)
    Department
    [Presentation].[VW_STG_DEPARTMENT].[NAME_1]
    CODE
    [Presentation].[VW_STG_DEPARTMENT].[CODE]
    On Time Count
    if (cast([Presentation].[Case].[DATE_RECEIVED], date)<=cast([Presentation].[Case].[DATE_EFFECTIVE_FROM], date)) then (1) else (0)
    Aggregate
    total total
    Rollup
    total total
    Case Count
    [Presentation].[Case].[Case Count]
    Aggregate
    count count
    Rollup
    total total
    Total Days of Delay
    _days_between([Presentation].[Case].[DATE_RECEIVED],[Presentation].[Case].[DATE_EFFECTIVE_FROM])
    Aggregate
    total total
    Rollup
    average average
    • Detail Filters
      7
    • Disabled
      [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].[Case].[DATE_EFFECTIVE_FROM] <=2022-12-31 and [Presentation].[Case].[DATE_EFFECTIVE_FROM] is not null
    • 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? = 'PAR' and [Presentation].[Case].[Created by Category] in ('PAR', 'DIP')
      or
      ?Created By? = 'Worklist' and [Presentation].[Case].[Created by Category] in ('Worklist', 'Liste de travail')
    • Required
      [Presentation].[Case].[DATE_RECEIVED] between _first_of_month(_add_months(current_date, -18)) and
      _last_of_month(_add_months(current_date, -1))
    • Required
      [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] = ?Department?
  • Query
    Timeliness Query
    Join
    Query Reference
    All Transactions
    Query Reference
    Dept. Transactions
    • Projected Data Items
    • Group Sort Index
    • TRANSACTION_TYPE
    • Month
    • Department
    • CODE
    • On Time Count
    • Case Count
    • Total Days of Delay
    Data Items
    10
    Group Sort Index
    [Union2].[Group Sort Index]
    TRANSACTION_TYPE
    [Union2].[TRANSACTION_TYPE]
    Month
    [Union2].[Month]
    Group
    [Union2].[CODE]
    Department
    [Union2].[Department]
    On Time Count
    [Union2].[On Time Count]
    Aggregate
    total total
    Case Count
    [Union2].[Case Count]
    Aggregate
    total total
    Total Days of Delay
    [Union2].[Total Days of Delay]
    Aggregate
    total total
    Days Delay per Case
    total( [Union2].[Total Days of Delay] ) / total( [Union2].[Case Count] )
    Aggregate
    calculated calculated
    Rollup
    calculated calculated
    Percent of Base
    total( [Union2].[On Time Count] ) / total( [Union2].[Case Count] )
    Aggregate
    calculated calculated
    Rollup
    calculated calculated
  • Query
    Trx Type Master Query
    Data Items
    1
    Transaction Type
    [Presentation].[Sub Type].[TRANSACTION_TYPE]
    • Detail Filters
      1
    • Required
      [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')
  • Query
    pq_Created_By
    Data Items
    1
    CREATED_BY
    [Presentation].[Case].[CREATED_BY]
    • Detail Filters
      1
    • Required
      [Presentation].[Case].[CREATED_BY] in ( 'CRCD / DCRC', 'Worklist / Liste de travail')
  • Query
    pq_Department
    Data Items
    2
    DEPARTMENT
    [Presentation].[VW_STG_DEPARTMENT].[CODE]
    DEPT_NAME
    [DEPARTMENT] +' - '+ [Presentation].[VW_STG_DEPARTMENT].[NAME_1]
    sort
    sort: ascending ascending
    • Detail Filters
      1
    • Disabled
      [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')
Layouts