Deployments
UAT_PCR_Deployment
Reports
Report Name: Collective Agreement Implementation (CAI) PA Group Progress
Overview

  • Queries: 9

    Layouts: 1

  • Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model
Queries
  • Queries 9
    Expressions:
  • Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model
  • Query
    PA Work in Progress
    • queryHints
    • autoSort: none
    Data Items
    16
    PRI
    [Presentation].[Case].[PRI]
    Cases by PRI
    count ([Presentation].[Case].[ID] for [PRI])
    Total Accounts (Start)
    87051
    # Cases Completed by PRI
    total( if ([Presentation].[State].[NAME] in ('Cancelled', 'Closed'))
    then (1)
    else (0)
    for [PRI])
    Is Account Complete
    if ([Cases by PRI] = [# Cases Completed by PRI])
    then (1)
    else (0)
    # Accounts Complete
    total ([Is Account Complete] for report)
    % Accounts Complete
    [# Accounts Complete] / [Total Accounts (Start)]
    # Cases Processed by PRI
    total (
    if ([Presentation].[State].[NAME] in ('Verified', 'Ready_To_Verify')
    OR
    ([Presentation].[State].[NAME] in ('Assigned', 'Pending') and
    ([Presentation].[Case].[AWAITING_PAY_SHEET_CREAT] = 'Yes/Oui' or
    [Presentation].[Case].[AWAITING_CALC] = 'Yes/Oui' or
    [Presentation].[Case].[AWAITING_CONFIRM] = 'Yes/Oui' or
    [Presentation].[Case].[AWAITING_OFF_CYCLE] = 'Yes/Oui')
    )
    )
    then (1)
    else (0)
    for [PRI])
    Is Account Processed
    if ([Cases by PRI] = [# Cases Completed by PRI] +[# Cases Processed by PRI])
    then (1)
    else (0)
    # Cases Processed
    total ([# Cases Processed by PRI] for report)
    Total # Accounts Processed
    total ([Is Account Processed] for report)
    % Accounts Processed
    [Total # Accounts Processed] / [Total Accounts (Start)]
    # Remaining Accounts
    [Total Accounts (Start)] - [Total # Accounts Processed]
    % Remaining Accounts
    [# Remaining Accounts] / [Total Accounts (Start)]
    Requiring Payment Control
    count (distinct if ([Presentation].[State].[NAME] in ('Assigned', 'Pending') and
    ([Presentation].[Case].[AWAITING_PAY_SHEET_CREAT] = 'Yes/Oui' or
    [Presentation].[Case].[AWAITING_CALC] = 'Yes/Oui' or
    [Presentation].[Case].[AWAITING_CONFIRM] = 'Yes/Oui' or
    [Presentation].[Case].[AWAITING_OFF_CYCLE] = 'Yes/Oui')
    )
    then ([PRI])
    else (null)
    )
    Awaiting Verification
    count (distinct
    if ([Presentation].[State].[NAME] = 'Ready_To_Verify')
    then ([PRI])
    else (null))
    • Detail Filters
      1
    • Required
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-711', 'ST-712', 'ST-710', 'ST-713', 'ST-706', 'ST-707', 'ST-708', 'ST-616', 'ST-709', 'ST-714', 'ST-704', 'ST-718', 'ST-705', 'ST-715', 'ST-717', 'ST-782', 'ST-904', 'ST-903')
  • Query
    Productivity Goal by Team
    Join 
    [Target-Team].[g_DBID] = [Summary-Team].[g_DBID] and
[Target-Team].[TARGET_DATE] = [Summary-Team].[STATUS_DATE]
    refQuery
    Target-Team
    0:1
    refQuery
    Summary-Team
    0:1
    Data Items
    13
    g_DBID
    coalesce( [Target-Team].[g_DBID], [Summary-Team].[g_DBID] )
    DATE
    coalesce([Target-Team].[TARGET_DATE], [Summary-Team].[STATUS_DATE])
    Team Name
    coalesce( [Target-Team].[Team Name], [Summary-Team].[Team Name] )
    Team Lead
    coalesce( [Target-Team].[Team Lead], [Summary-Team].[Team Lead] )
    Location
    coalesce( [Target-Team].[Location], [Summary-Team].[Location] )
    Team Name Sort Order
    coalesce( [Target-Team].[Team Name Sort Order], [Summary-Team].[Team Name Sort Order] )
    Day Number
    _day_of_week([DATE], 1)
    Weekday
    if (#sq($runLocale)# = 'fr')
    then (
    case ( upper(datename({weekday}, [DATE])) )
    when ('MONDAY') then ('LUNDI')
    when ('TUESDAY') then ('MARDI')
    when ('WEDNESDAY') then ('MERCREDI')
    when ('THURSDAY') then ('JEUDI')
    when ('FRIDAY') then ('VENDREDI')
    when ('SATURDAY') then ('SAMEDI')
    else ( 'DIMANCHE' )
    end
    )
    else ( upper(datename({weekday}, [DATE])) )
    Week of Target
    if
    (
    _week_of_year ([DATE])=1 and (datepart({day}, [DATE]) in (1,2))
    )
    then
    (
    datepart({week}, _add_days([DATE], -7))
    )
    else
    (
    if
    (
    [Day Number] = 7
    )
    then
    (
    datepart({week}, [DATE]) - 1
    )
    else
    (
    datepart({week}, [DATE])
    )
    )
    Target Value
    coalesce( [Target-Team].[Target Value], 0 )
    Aggregate
    total total
    Cases Processed
    if ([Target-Team].[TARGET_DATE] > current_date)
    then (null)
    else(
    coalesce( [Summary-Team].[TOTAL_PRODUCTION], 0 ))
    Aggregate
    total total
    label
    Cases Processed
    % Met
    [Cases Processed] / [Target Value]
    Aggregate
    calculated calculated
    Current Week
    if
    (
    _week_of_year (current_date)=1 and (datepart({day}, current_date) in (1,2))
    )
    then
    (
    datepart({week}, _add_days(current_date, -7))
    )
    else
    (
    if
    (
    _day_of_week(_add_days(current_date, -1), 1 ) = 7
    )
    then
    (
    datepart ({week}, _add_days(current_date, -1)) - 1
    )
    else
    (
    datepart ({week}, _add_days(current_date, -1))
    )
    )
    • Detail Filters
      1
    • Required
      [Week of Target]=[Current Week]
  • Query
    Summary-Team
    Join 
    [Summary].[TEAM_ID] = [Team].[g_DBID]
    refQuery
    Summary
    1:N
    refQuery
    Team
    1:N
    Data Items
    8
    g_DBID
    [Team].[g_DBID]
    STATUS_DATE
    [Summary].[STATUS_DATE]
    Team Name
    [Team].[Team Name]
    Team Lead
    [Team].[Team Lead]
    Location
    [Team].[Location]
    Team Name Sort Order
    [Team].[Team Name Sort Order]
    Week of Status
    [Summary].[Week of Status]
    TOTAL_PRODUCTION
    [Summary].[TOTAL_PRODUCTION]
    Aggregate
    total total
  • Query
    Target-Team
    Join 
    [Target].[TEAM_ID] = [Team].[g_DBID]
    refQuery
    Target
    1:N
    refQuery
    Team
    1:N
    Data Items
    9
    g_DBID
    [Team].[g_DBID]
    TARGET_DATE
    [Target].[TARGET_DATE]
    Team Name
    [Team].[Team Name]
    Team Lead
    [Team].[Team Lead]
    Location
    [Team].[Location]
    Team Name Sort Order
    [Team].[Team Name Sort Order]
    Day Number
    [Target].[Day Number]
    Week of Target
    [Target].[Week of Target]
    Target Value
    [Target].[Target Value]
    Aggregate
    total total
    Rollup
    total total
    • Detail Filters
      1
    • Required
      [Target Value] is not null
  • Query
    CAI Total PA Production
    Data Items
    8
    Target Date
    upper(datename({weekday},coalesce( [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[STATUS_DATE], [Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[TARGET_DATE])))
    Day Number
    case
    (upper(datename({weekday},[Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[TARGET_DATE])))
    when ('MONDAY')
    then (1)
    when ('TUESDAY')
    then (2)
    when ('WEDNESDAY')
    then (3)
    when ('THURSDAY')
    then (4)
    when ('FRIDAY')
    then (5)
    when ('SATURDAY')
    then (6)
    when ('SUNDAY')
    then (7)
    else (null)
    end
    Total Production
    [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[TOTAL_PRODUCTION]
    Aggregate
    summarize summarize
    Rollup
    summarize summarize
    Team Name
    if ([Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[TEAM] in ('Team059', 'Team075', 'Team094', 'Team095', 'Team053', 'Team037', 'Team054', 'Team067', 'Team068', 'Team039', 'Team038', 'Team040', 'Team084', 'Team022','Team031','Team032','Team096','Team027', 'Team076'))
    then ([Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[TEAM])
    else ('Other')
    Target Value
    [Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[TARGET_VALUE]
    Aggregate
    summarize summarize
    Rollup
    summarize summarize
    % Met
    [Total Production] / [Target Value]
    Aggregate
    calculated calculated
    Rollup
    calculated calculated
    Week of Target
    if
    (
    _week_of_year (coalesce( [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[STATUS_DATE], [Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[TARGET_DATE]))=1 and (datepart({day}, coalesce( [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[STATUS_DATE], [Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[TARGET_DATE])) in (1,2))
    )
    then
    (
    datepart({week}, _add_days(coalesce( [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[STATUS_DATE], [Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[TARGET_DATE]), -7))
    )
    else
    (
    if
    (
    _day_of_week( coalesce( [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[STATUS_DATE], [Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[TARGET_DATE]), 1) = 7
    )
    then
    (
    datepart({week}, coalesce( [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[STATUS_DATE], [Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[TARGET_DATE]) ) - 1
    )
    else
    (
    datepart({week}, coalesce( [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[STATUS_DATE], [Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[TARGET_DATE]) )
    )
    )
    Current Week
    if
    (
    _week_of_year (current_date)=1 and (datepart({day}, current_date) in (1,2))
    )
    then
    (
    datepart({week}, _add_days(current_date, -7))
    )
    else
    (
    if
    (
    _day_of_week(_add_days(current_date, -1), 1 ) = 7
    )
    then
    (
    datepart ({week}, _add_days(current_date, -1)) - 1
    )
    else
    (
    datepart ({week}, _add_days(current_date, -1))
    )
    )
    • Detail Filters
      2
    • Required
      [Week of Target] = [Current Week]
    • Required
      [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[CAI_GROUP]='PA'
  • Query
    Team
    Data Items
    5
    g_DBID
    [Presentation].[VW_STG_CURRENT_TEAM_Alias For DAILY_CAI_PROGRESS_SUMMARY].[g_DBID]
    Team Name
    if ( [Presentation].[VW_STG_CURRENT_TEAM_Alias For DAILY_CAI_PROGRESS_SUMMARY].[TEAM_NAME]in ('Team059', 'Team075', 'Team094', 'Team095', 'Team053', 'Team037', 'Team054', 'Team067', 'Team068', 'Team039', 'Team038', 'Team040', 'Team084','Team031','Team032','Team096','Team027', 'Team076', 'Team072', 'Team073', 'Team077'))
    then (
    [Presentation].[VW_STG_CURRENT_TEAM_Alias For DAILY_CAI_PROGRESS_SUMMARY].[TEAM_NAME]
    )
    else (
    if([Presentation].[VW_STG_CURRENT_TEAM_Alias For DAILY_CAI_PROGRESS_SUMMARY].[Team_Name] in ('Compensation-Advisor-OGD'))
    then ('TeamOGD')
    else (
    if(#sq($runLocale)# = 'fr')
    then ('Autres équipes')
    else ('Other Teams')
    )
    )
    Team Lead
    if ([Team Name] in ('Other Teams','Autres équipes','TeamOGD'))
    then ('N / A')
    else(
    SUBSTRING([Presentation].[VW_STG_CURRENT_TEAM_Alias For DAILY_CAI_PROGRESS_SUMMARY].[TEAM_LEAD], 1, position(' ', [Presentation].[VW_STG_CURRENT_TEAM_Alias For DAILY_CAI_PROGRESS_SUMMARY].[TEAM_LEAD])-1)
    )
    Location
    if ([Team Name] not in ('Other Teams', 'Autres équipes','TeamOGD'))
    then (
    if (
    [Presentation].[VW_STG_CURRENT_TEAM_Alias For DAILY_CAI_PROGRESS_SUMMARY].[TEAM_NAME] =
    [Presentation].[VW_STG_CURRENT_TEAM_Alias For DAILY_CAI_PROGRESS_SUMMARY].[TEAM_NAME_CENTER]
    )
    then (
    [Presentation].[VW_STG_CURRENT_TEAM_Alias For DAILY_CAI_PROGRESS_SUMMARY].[PAY_PROCESSING_CENTRE]
    )
    else (
    [Presentation].[VW_STG_CURRENT_TEAM_Alias For DAILY_CAI_PROGRESS_SUMMARY].[TEAM_NAME_CENTER]
    )
    )
    else (
    if (
    [Team Name]='TeamOGD'
    )
    then (
    if(#sq($runLocale)# = 'fr')
    then ('Autre')
    else ('Other')
    )
    else ('N / A')
    )
    Team Name Sort Order
    case
    when [Location] in ('Moncton Satellite Centre ', 'Centre satellite de Moncton') then (1)
    when [Location] in ('Matane Triage', 'Triage Miramichi') then (2)
    when [Location] in ('Halifax Satellite Centre ', 'Centre satellite de Halifax') then (3)
    when [Location] in ('Charlottetown Satellite Centre ', 'Centre satellite de Charlottetown') then (4)
    when [Location] in ('Gatineau Satellite Centre ', 'Centre satellite de Gatineau') then (5)
    when [Location] in ('CSB NCR ', 'BSC de la RCN') then (5.5)
    when [Location] in ('Pay Liaison Unit', 'Unité de service de la paie') then (6)
    when [Location] in ('Kirkland Lake Satellite Centre ', 'Centre satellite de Kirkland Lake') then (7)
    when [Location] in ('Toronto Satellite Centre ', 'Centre satellite de Toronto') then (8)
    when [Location] in ('Edmonton Satellite Centre ', 'Centre satellite d’Edmonton') then (9)
    when [Location] in ('Vancouver Satellite Centre ', 'Centre satellite de Vancouver') then (9.1)
    when [Location] in ('EDP ', 'EDP') then (9.2)
    when [Location] in ('Other', 'Autre') then (12)
    when [Location] in ('N / A', 'N / A') then (99)
    end
  • Query
    Target
    Data Items
    6
    TEAM_ID
    [Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[TEAM_ID]
    TARGET_DATE
    [Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[TARGET_DATE]
    Day Number
    case
    (upper(datename({weekday},coalesce( [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[STATUS_DATE], [Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[TARGET_DATE]))))
    when ('MONDAY')
    then (1)
    when ('TUESDAY')
    then (2)
    when ('WEDNESDAY')
    then (3)
    when ('THURSDAY')
    then (4)
    when ('FRIDAY')
    then (5)
    when ('SATURDAY')
    then (6)
    when ('SUNDAY')
    then (7)
    else (null)
    end
    Target Value
    [Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[TARGET_VALUE]
    Rollup
    total total
    Week of Target
    if
    (
    _week_of_year (coalesce( [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[STATUS_DATE], [Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[TARGET_DATE]))=1 and (datepart({day}, coalesce( [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[STATUS_DATE], [Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[TARGET_DATE])) in (1,2))
    )
    then
    (
    datepart({week}, _add_days(coalesce( [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[STATUS_DATE], [Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[TARGET_DATE]), -7))
    )
    else
    (
    if
    (
    _day_of_week( coalesce( [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[STATUS_DATE], [Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[TARGET_DATE]), 1) = 7
    )
    then
    (
    datepart({week}, coalesce( [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[STATUS_DATE], [Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[TARGET_DATE])) - 1
    )
    else
    (
    datepart({week}, coalesce( [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[STATUS_DATE], [Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[TARGET_DATE]))
    )
    )
    CAI_GROUP
    [Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[CAI_GROUP]
    • Detail Filters
      1
    • Required
      [CAI_GROUP]='PA'
  • Query
    Summary
    Data Items
    5
    STATUS_DATE
    [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[STATUS_DATE]
    TEAM_ID
    [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[TEAM_ID]
    Week of Status
    if
    (
    _week_of_year ([Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[STATUS_DATE])=1 and (datepart({day}, [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[STATUS_DATE]) in (1,2))
    )
    then
    (
    datepart({week}, _add_days([Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[STATUS_DATE], -7))
    )
    else
    (
    if
    (
    _day_of_week( [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[STATUS_DATE], 1) = 7
    )
    then
    (
    datepart({week}, [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[STATUS_DATE]) - 1
    )
    else
    (
    datepart({week}, [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[STATUS_DATE])
    )
    )
    TOTAL_PRODUCTION
    [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[TOTAL_PRODUCTION]
    CAI_GROUP
    [Presentation].[VW_STG_DAILY_CAI_PROGRESS_SUMMARY].[CAI_GROUP]
    • Detail Filters
      1
    • Required
      [CAI_GROUP]='PA'
  • Query
    Dates
    • queryHints
    • suppress: none
    Data Items
    5
    Week of Target
    if (
    _day_of_week( [Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[TARGET_DATE], 1) = 7
    )
    then(
    datepart({week}, [Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[TARGET_DATE]) - 1
    )
    else (
    datepart({week}, [Presentation].[VW_STG_DAILY_CAI_PROGRESS_TARGET].[TARGET_DATE])
    )
    Current Week
    if (
    _day_of_week(_add_days(current_date, -1), 1 ) = 7
    )
    then (
    datepart ({week}, current_date) - 1
    )
    else (
    datepart ({week}, current_date)
    )
    Current Day of Week
    _day_of_week(current_date, 1)
    First of Week
    if ([Current Day of Week]=1)
    then (_add_days(current_date, -7))
    else(_add_days(current_date, -1*([Current Day of Week] - 1) ))
    Last of Week
    if ([Current Day of Week]=1)
    then (_add_days([First of Week],7))
    else (_add_days(current_date, 7 - [Current Day of Week] ))
Layouts