Deployments
UAT_PCR_Deployment
Reports
Report Name: Weekly Mat LWOP & Weekly Disability Insurance Service Standards
Overview

  • Queries: 15

    Layouts: 1

  • Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model
Queries
  • Queries 15
    Expressions:
  • Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model
  • Query
    Load Last Date Query
    Data Items
    1
    Latest Valid Data Load Date
    [Presentation].[Data Load Archive].[Latest Valid Data Load Date]
  • Query
    Reporting Date Range
    Data Items
    5
    Begin Date - Previous Month
    IF ( current_date = _first_of_month (current_date ) ) THEN
    (_first_of_month ( _add_months (current_date, -2) ) )
    ELSE
    ( _first_of_month ( _add_months (current_date, -1) ) )
    End Date - Previous Month
    IF ( current_date = _first_of_month (current_date ) ) THEN
    (_last_of_month ( _add_months (current_date, -2) ) )
    ELSE
    ( _last_of_month ( _add_months (current_date, -1) ) )
    Last Month
    IF ( current_date = _first_of_month (current_date ) ) THEN
    (_add_months (current_date,-2) )
    ELSE
    (_add_months (current_date,-1) )
    Begin Date - Current Month
    IF ( current_date = _first_of_month (current_date ) ) THEN
    (_first_of_month ( _add_months (current_date, -1)) )
    ELSE
    (_first_of_month (current_date) )
    End Date - Current Month
    IF ( current_date = _first_of_month (current_date ) ) THEN
    ( _last_of_month ( _add_months (current_date, -1)) )
    ELSE
    (_add_days (current_date, -1))
  • Query
    Previous Month - Maternity / Paternity LWOP
    Data Items
    6
    Begin Date - Previous Month
    IF ( current_date = _first_of_month (current_date ) ) THEN
    (_first_of_month ( _add_months (current_date, -2) ) )
    ELSE
    ( _first_of_month ( _add_months (current_date, -1) ) )
    End Date - Previous Month
    IF ( current_date = _first_of_month (current_date ) ) THEN
    (_last_of_month ( _add_months (current_date, -2) ) )
    ELSE
    ( _last_of_month ( _add_months (current_date, -1) ) )
    Total Cases Closed Count
    count([Presentation].[Case].[Case Count] for report)
    Within Standard Days Cases
    total(CASE
    WHEN ( [Presentation].[Case].[CMT_AGE] <= [Presentation].[Sub Type].[SERVICE_STANDARD]
    ) THEN 1
    ELSE (0)
    END for report)
    Exceeding Standard Days Cases
    [Total Cases Closed Count] - [Within Standard Days Cases]
    Success Rate
    [Within Standard Days Cases] / [Total Cases Closed Count]
    • Detail Filters
      5
    • Required
      [Presentation].[Case].[DBID] <> 0
    • Required
      [Presentation].[Case].[DATE_CLOSED_EST] between [Begin Date - Previous Month] and [End Date - Previous Month]
    • Required
      (
      ?Report_Selection_p? ='1'
      and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-529', 'ST-938')
      )
      or
      (
      ?Report_Selection_p? ='2'
      and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in( 'ST-170', 'ST-176', 'ST-251', 'ST-254', 'ST-530', 'ST-531', 'ST-532', 'ST-533', 'ST-538', 'ST-540')
      )
    • Optional
      (?Report_Selection_p? ='1'
      and
      (
      ( ?Pod_Num_p? = '99999')
      or (?Pod_Num_p? = 0 and [Presentation].[Pod 0])
      or (?Pod_Num_p? = 1 and [Presentation].[Pod 1])
      or (?Pod_Num_p? = 2 and [Presentation].[Pod 2])
      or (?Pod_Num_p? = 3 and [Presentation].[Pod 3])
      or (?Pod_Num_p? = 4 and [Presentation].[Pod 4])
      or (?Pod_Num_p? = 5 and [Presentation].[Pod 5])
      or (?Pod_Num_p? = 6 and [Presentation].[Pod 6])
      or (?Pod_Num_p? = 7 and [Presentation].[Pod 7])
      or (?Pod_Num_p? = 8 and [Presentation].[Pod 8])
      or (?Pod_Num_p? = 9 and [Presentation].[Pod 9])
      or (?Pod_Num_p? = 10 and [Presentation].[Pod 10])
      or (?Pod_Num_p? = 11 and [Presentation].[Pod 11])
      or (?Pod_Num_p? = 12 and [Presentation].[Pod 12])
      or (?Pod_Num_p? = 13 and [Presentation].[Pod 13])
      or (?Pod_Num_p? = 14 and [Presentation].[Pod 14])
      or (?Pod_Num_p? = 15 and [Presentation].[Pod 15])
      or (?Pod_Num_p? = 16 and [Presentation].[Pod 16])
      or (?Pod_Num_p? = 17 and [Presentation].[Pod 17])
      or (?Pod_Num_p? = 18 and [Presentation].[Pod 18])
      )
      )
      or
      ?Report_Selection_p? ='2'
    • Required
      ?Embedded_to_Email_body_p? is not null
  • Query
    Month to Yesterday - Maternity / Paternity LWOP
    Data Items
    7
    Begin Date - Month to Yesterday
    IF ( current_date = _first_of_month (current_date ) ) THEN
    (_first_of_month ( _add_months (current_date, -1)) )
    ELSE
    (_first_of_month (current_date) )
    End Date - Month to Yesterday
    IF ( current_date = _first_of_month (current_date ) ) THEN
    ( _last_of_month ( _add_months (current_date, -1)) )
    ELSE
    (_add_days (current_date, -1))
    Total Cases Closed Count
    count([Presentation].[Case].[Case Count] for report)
    Within Standard Days Cases
    coalesce (total(CASE
    WHEN ( [Presentation].[Case].[CMT_AGE] <= [Presentation].[Sub Type].[SERVICE_STANDARD]
    ) THEN 1
    ELSE (0)
    END for report),0)
    Exceeding Standard Days Cases
    coalesce ([Total Cases Closed Count] - [Within Standard Days Cases],0)
    Success Rate
    coalesce ([Within Standard Days Cases] / [Total Cases Closed Count],0)
    Fake
    1
    • Detail Filters
      4
    • Required
      [Presentation].[Case].[DBID] <> 0
    • Required
      [Presentation].[Case].[DATE_CLOSED_EST] between [Begin Date - Month to Yesterday] and [End Date - Month to Yesterday]
    • Required
      (
      ?Report_Selection_p? ='1'
      and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-529', 'ST-938')
      )
      or
      (
      ?Report_Selection_p? ='2'
      and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in( 'ST-170', 'ST-176', 'ST-251', 'ST-254', 'ST-530', 'ST-531', 'ST-532', 'ST-533', 'ST-538', 'ST-540')
      )
    • Optional
      (?Report_Selection_p? ='1'
      and
      (
      ( ?Pod_Num_p? = '99999')
      or (?Pod_Num_p? = 0 and [Presentation].[Pod 0])
      or (?Pod_Num_p? = 1 and [Presentation].[Pod 1])
      or (?Pod_Num_p? = 2 and [Presentation].[Pod 2])
      or (?Pod_Num_p? = 3 and [Presentation].[Pod 3])
      or (?Pod_Num_p? = 4 and [Presentation].[Pod 4])
      or (?Pod_Num_p? = 5 and [Presentation].[Pod 5])
      or (?Pod_Num_p? = 6 and [Presentation].[Pod 6])
      or (?Pod_Num_p? = 7 and [Presentation].[Pod 7])
      or (?Pod_Num_p? = 8 and [Presentation].[Pod 8])
      or (?Pod_Num_p? = 9 and [Presentation].[Pod 9])
      or (?Pod_Num_p? = 10 and [Presentation].[Pod 10])
      or (?Pod_Num_p? = 11 and [Presentation].[Pod 11])
      or (?Pod_Num_p? = 12 and [Presentation].[Pod 12])
      or (?Pod_Num_p? = 13 and [Presentation].[Pod 13])
      or (?Pod_Num_p? = 14 and [Presentation].[Pod 14])
      or (?Pod_Num_p? = 15 and [Presentation].[Pod 15])
      or (?Pod_Num_p? = 16 and [Presentation].[Pod 16])
      or (?Pod_Num_p? = 17 and [Presentation].[Pod 17])
      or (?Pod_Num_p? = 18 and [Presentation].[Pod 18])
      )
      )
      or
      ?Report_Selection_p? ='2'
  • Query
    Service Standard for WIP
    Data Items
    4
    State Name
    IF ( #sq($runLocale)#='fr') THEN
    ( CASE [Presentation].[State].[NAME]
    WHEN 'Assigned' THEN 'Attribué'
    WHEN 'Invalid' THEN 'Invalide'
    WHEN 'Open' THEN 'Ouvert'
    WHEN 'Pending' THEN 'En attendant'
    WHEN 'Ready_To_Verify' THEN 'Prêt à vérifier'
    WHEN 'Reassigned' THEN 'Réaffecté'
    WHEN 'Verified' THEN 'Vérifié'
    WHEN 'Triage_Follow-Up' THEN 'Triage_Suivre'
    END )
    ELSE
    ([Presentation].[State].[NAME] )
    Total Active Within Standard Days Cases for Statename
    total(CASE
    WHEN ( [Presentation].[Case].[CMT_AGE] <= [Presentation].[Sub Type].[SERVICE_STANDARD]
    ) THEN 1
    ELSE (0)
    END for [State Name])
    Total Cases Closed Count for Statement
    count([Presentation].[Case].[Case Count] for [State Name])
    Exceeding Standard Days Cases
    [Total Cases Closed Count for Statement] - [Total Active Within Standard Days Cases for Statename]
    • Detail Filters
      4
    • Required
      [Presentation].[Case].[DBID] <> 0
    • Required
      (
      ?Report_Selection_p? ='1'
      and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-529', 'ST-938')
      )
      or
      (
      ?Report_Selection_p? ='2'
      and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in ( 'ST-170', 'ST-176', 'ST-251', 'ST-254', 'ST-530', 'ST-531', 'ST-532', 'ST-533', 'ST-538', 'ST-540')
      )
    • Required
      [Presentation].[State].[NAME] in ('Assigned', 'Invalid', 'Open', 'Pending', 'Ready_To_Verify', 'Reassigned', 'Verified', 'Triage_Follow-Up')
    • Optional
      (?Report_Selection_p? ='1'
      and
      (
      ( ?Pod_Num_p? = '99999')
      or (?Pod_Num_p? = 0 and [Presentation].[Pod 0])
      or (?Pod_Num_p? = 1 and [Presentation].[Pod 1])
      or (?Pod_Num_p? = 2 and [Presentation].[Pod 2])
      or (?Pod_Num_p? = 3 and [Presentation].[Pod 3])
      or (?Pod_Num_p? = 4 and [Presentation].[Pod 4])
      or (?Pod_Num_p? = 5 and [Presentation].[Pod 5])
      or (?Pod_Num_p? = 6 and [Presentation].[Pod 6])
      or (?Pod_Num_p? = 7 and [Presentation].[Pod 7])
      or (?Pod_Num_p? = 8 and [Presentation].[Pod 8])
      or (?Pod_Num_p? = 9 and [Presentation].[Pod 9])
      or (?Pod_Num_p? = 10 and [Presentation].[Pod 10])
      or (?Pod_Num_p? = 11 and [Presentation].[Pod 11])
      or (?Pod_Num_p? = 12 and [Presentation].[Pod 12])
      or (?Pod_Num_p? = 13 and [Presentation].[Pod 13])
      or (?Pod_Num_p? = 14 and [Presentation].[Pod 14])
      or (?Pod_Num_p? = 15 and [Presentation].[Pod 15])
      or (?Pod_Num_p? = 16 and [Presentation].[Pod 16])
      or (?Pod_Num_p? = 17 and [Presentation].[Pod 17])
      or (?Pod_Num_p? = 18 and [Presentation].[Pod 18])
      )
      )
      or
      ?Report_Selection_p? ='2'
  • Query
    Service Standard Success Rate for WIP
    Data Items
    8
    Total Closed Cases Within Standard Days
    total(
    CASE
    WHEN ([Presentation].[State].[NAME] = 'Closed' and [Presentation].[Case].[CMT_AGE] <= [Presentation].[Sub Type].[SERVICE_STANDARD]
    ) THEN 1
    ELSE 0
    END
    for report)
    Total Active Cases Within Standard Days
    total(
    CASE
    WHEN ([Presentation].[Case].[CMT_AGE] <= [Presentation].[Sub Type].[SERVICE_STANDARD]
    and [Presentation].[State].[NAME] in ('Assigned', 'Invalid', 'Open', 'Pending', 'Ready_To_Verify', 'Reassigned', 'Verified', 'Triage_Follow-Up')
    ) THEN 1
    ELSE 0
    END
    for report)
    Total Closed Cases
    total(CASE
    WHEN [Presentation].[State].[NAME] = 'Closed' THEN 1
    ELSE 0
    END
    for report)
    Total Active Cases
    total(
    CASE
    WHEN ( [Presentation].[State].[NAME] in ('Assigned', 'Invalid', 'Open', 'Pending', 'Ready_To_Verify', 'Reassigned', 'Verified', 'Triage_Follow-Up')
    ) THEN 1
    ELSE 0
    END
    for report)
    Subtotal 1
    [Total Closed Cases Within Standard Days]+[Total Active Cases Within Standard Days]
    Subtotal 2
    [Total Closed Cases]+[Total Active Cases]
    Success Rate
    [Subtotal 1] / [Subtotal 2]
    Fake
    1
    • Detail Filters
      4
    • Required
      [Presentation].[Case].[DBID] <> 0
    • Required
      (
      ?Report_Selection_p? ='1'
      and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-529', 'ST-938')
      )
      or
      (
      ?Report_Selection_p? ='2'
      and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in ( 'ST-170', 'ST-176', 'ST-251', 'ST-254', 'ST-530', 'ST-531', 'ST-532', 'ST-533', 'ST-538', 'ST-540')
      )
    • Optional
      (?Report_Selection_p? ='1'
      and
      (
      ( ?Pod_Num_p? = '99999')
      or (?Pod_Num_p? = 0 and [Presentation].[Pod 0])
      or (?Pod_Num_p? = 1 and [Presentation].[Pod 1])
      or (?Pod_Num_p? = 2 and [Presentation].[Pod 2])
      or (?Pod_Num_p? = 3 and [Presentation].[Pod 3])
      or (?Pod_Num_p? = 4 and [Presentation].[Pod 4])
      or (?Pod_Num_p? = 5 and [Presentation].[Pod 5])
      or (?Pod_Num_p? = 6 and [Presentation].[Pod 6])
      or (?Pod_Num_p? = 7 and [Presentation].[Pod 7])
      or (?Pod_Num_p? = 8 and [Presentation].[Pod 8])
      or (?Pod_Num_p? = 9 and [Presentation].[Pod 9])
      or (?Pod_Num_p? = 10 and [Presentation].[Pod 10])
      or (?Pod_Num_p? = 11 and [Presentation].[Pod 11])
      or (?Pod_Num_p? = 12 and [Presentation].[Pod 12])
      or (?Pod_Num_p? = 13 and [Presentation].[Pod 13])
      or (?Pod_Num_p? = 14 and [Presentation].[Pod 14])
      or (?Pod_Num_p? = 15 and [Presentation].[Pod 15])
      or (?Pod_Num_p? = 16 and [Presentation].[Pod 16])
      or (?Pod_Num_p? = 17 and [Presentation].[Pod 17])
      or (?Pod_Num_p? = 18 and [Presentation].[Pod 18])
      )
      )
      or
      ?Report_Selection_p? ='2'
    • Required
      [Presentation].[State].[NAME] in ('Assigned', 'Invalid', 'Open', 'Pending', 'Ready_To_Verify', 'Reassigned', 'Verified', 'Triage_Follow-Up')
  • Query
    Previous Month - Service Standard for Raw Data
    Data Items
    22
    Case Number
    [Presentation].[Case].[ID]
    Contact Info
    [Presentation].[Case].[CONTACT_INFO]
    Age
    [Presentation].[Case].[CMT_AGE]
    Processing Centre
    [Presentation].[Case].[PROCESSING_CENTRE]
    Assigned To User ID
    trim (both ' ',trim (both '·',[Presentation].[Case].[ASSIGNEE_CURR]))
    Current Team
    [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]
    Date Open
    [Presentation].[Case].[DATE_OPEN_EST_DATE_TIME]
    Date Effective From
    [Presentation].[Case].[DATE_EFFECTIVE_FROM_EST_DATE_TIME]
    BF Date
    [Presentation].[Case].[DATE_BF_EST_DATE_TIME]
    Date Closed
    [Presentation].[Case].[DATE_CLOSED_EST_DATE_TIME]
    Classification
    [Presentation].[Case].[CLASSIFICATION]
    State Name
    [Presentation].[State].[NAME]
    Business Unit
    [Presentation].[VW_STG_DEPARTMENT].[CODE]
    PRI
    [Presentation].[Case].[PRI]
    Language
    [Presentation].[Case].[LANG]
    Work Type Name
    [Presentation].[Work Type].[NAME]
    Sub Type Name
    [Presentation].[Sub Type].[NAME]
    POD
    [Presentation].[VW_STG_DEPARTMENT].[POD]
    Created By
    [Presentation].[Case].[CREATED_BY]
    Was the Service Standard Met
    IF ( [Presentation].[Case].[CMT_AGE] <= [Presentation].[Sub Type].[SERVICE_STANDARD]
    ) THEN
    ( IF ( #sq($runLocale)#='fr') THEN
    ( 'Oui' )
    ELSE
    ( 'Yes' ) )
    ELSE
    (IF ( #sq($runLocale)#='fr' ) THEN
    ( 'Non' )
    ELSE
    ( 'No' ))
    Begin Date - Previous Month
    IF ( current_date = _first_of_month (current_date ) ) THEN
    (_first_of_month ( _add_months (current_date, -2) ) )
    ELSE
    ( _first_of_month ( _add_months (current_date, -1) ) )
    End Date - Previous Month
    IF ( current_date = _first_of_month (current_date ) ) THEN
    (_last_of_month ( _add_months (current_date, -2) ) )
    ELSE
    ( _last_of_month ( _add_months (current_date, -1) ) )
    • Detail Filters
      4
    • Required
      [Presentation].[Case].[DBID] <> 0
    • Required
      [Presentation].[Case].[DATE_CLOSED_EST] between [Begin Date - Previous Month] and [End Date - Previous Month]
    • Required
      (
      ?Report_Selection_p? = '1'
      and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-529', 'ST-938')
      )
      or
      (
      ?Report_Selection_p? = '2'
      and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in( 'ST-170', 'ST-176', 'ST-251', 'ST-254', 'ST-530', 'ST-531', 'ST-532', 'ST-533', 'ST-538', 'ST-540')
      )
    • Optional
      (?Report_Selection_p? ='1'
      and
      (
      ( ?Pod_Num_p? = '99999')
      or (?Pod_Num_p? = 0 and [Presentation].[Pod 0])
      or (?Pod_Num_p? = 1 and [Presentation].[Pod 1])
      or (?Pod_Num_p? = 2 and [Presentation].[Pod 2])
      or (?Pod_Num_p? = 3 and [Presentation].[Pod 3])
      or (?Pod_Num_p? = 4 and [Presentation].[Pod 4])
      or (?Pod_Num_p? = 5 and [Presentation].[Pod 5])
      or (?Pod_Num_p? = 6 and [Presentation].[Pod 6])
      or (?Pod_Num_p? = 7 and [Presentation].[Pod 7])
      or (?Pod_Num_p? = 8 and [Presentation].[Pod 8])
      or (?Pod_Num_p? = 9 and [Presentation].[Pod 9])
      or (?Pod_Num_p? = 10 and [Presentation].[Pod 10])
      or (?Pod_Num_p? = 11 and [Presentation].[Pod 11])
      or (?Pod_Num_p? = 12 and [Presentation].[Pod 12])
      or (?Pod_Num_p? = 13 and [Presentation].[Pod 13])
      or (?Pod_Num_p? = 14 and [Presentation].[Pod 14])
      or (?Pod_Num_p? = 15 and [Presentation].[Pod 15])
      or (?Pod_Num_p? = 16 and [Presentation].[Pod 16])
      or (?Pod_Num_p? = 17 and [Presentation].[Pod 17])
      or (?Pod_Num_p? = 18 and [Presentation].[Pod 18])
      )
      )
      or
      ?Report_Selection_p? ='2'
  • Query
    Month to Yesterday - Service Standard for Raw Data
    Data Items
    22
    Case Number
    [Presentation].[Case].[ID]
    Contact Info
    [Presentation].[Case].[CONTACT_INFO]
    Age
    [Presentation].[Case].[CMT_AGE]
    Processing Centre
    [Presentation].[Case].[PROCESSING_CENTRE]
    Assigned To User ID
    trim (both ' ',trim (both '·',[Presentation].[Case].[ASSIGNEE_CURR]))
    Current Team
    [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]
    Date Open
    [Presentation].[Case].[DATE_OPEN_EST_DATE_TIME]
    Date Effective From
    [Presentation].[Case].[DATE_EFFECTIVE_FROM_EST_DATE_TIME]
    BF Date
    [Presentation].[Case].[DATE_BF_EST_DATE_TIME]
    Date Closed
    [Presentation].[Case].[DATE_CLOSED_EST_DATE_TIME]
    Classification
    [Presentation].[Case].[CLASSIFICATION]
    State Name
    [Presentation].[State].[NAME]
    Business Unit
    [Presentation].[VW_STG_DEPARTMENT].[CODE]
    PRI
    [Presentation].[Case].[PRI]
    Language
    [Presentation].[Case].[LANG]
    Work Type Name
    [Presentation].[Work Type].[NAME]
    Sub Type Name
    [Presentation].[Sub Type].[NAME]
    POD
    [Presentation].[VW_STG_DEPARTMENT].[POD]
    Created By
    [Presentation].[Case].[CREATED_BY]
    Was the Service Standard Met
    IF ( [Presentation].[Case].[CMT_AGE] <= [Presentation].[Sub Type].[SERVICE_STANDARD]
    ) THEN
    ( IF ( #sq($runLocale)#='fr') THEN
    ( 'Oui' )
    ELSE
    ( 'Yes' ) )
    ELSE
    (IF ( #sq($runLocale)#='fr' ) THEN
    ( 'Non' )
    ELSE
    ( 'No' ))
    Begin Date - Month to Yesterday
    IF ( current_date = _first_of_month (current_date ) ) THEN
    (_first_of_month ( _add_months (current_date, -1)) )
    ELSE
    (_first_of_month (current_date) )
    End Date - Month to Yesterday
    IF ( current_date = _first_of_month (current_date ) ) THEN
    ( _last_of_month ( _add_months (current_date, -1)) )
    ELSE
    (_add_days (current_date, -1))
    • Detail Filters
      4
    • Required
      [Presentation].[Case].[DBID] <> 0
    • Required
      (
      ?Report_Selection_p? = '1'
      and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-529', 'ST-938')
      )
      or
      (
      ?Report_Selection_p? = '2'
      and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in( 'ST-170', 'ST-176', 'ST-251', 'ST-254', 'ST-530', 'ST-531', 'ST-532', 'ST-533', 'ST-538', 'ST-540')
      )
    • Optional
      (?Report_Selection_p? ='1'
      and
      (
      ( ?Pod_Num_p? = '99999')
      or (?Pod_Num_p? = 0 and [Presentation].[Pod 0])
      or (?Pod_Num_p? = 1 and [Presentation].[Pod 1])
      or (?Pod_Num_p? = 2 and [Presentation].[Pod 2])
      or (?Pod_Num_p? = 3 and [Presentation].[Pod 3])
      or (?Pod_Num_p? = 4 and [Presentation].[Pod 4])
      or (?Pod_Num_p? = 5 and [Presentation].[Pod 5])
      or (?Pod_Num_p? = 6 and [Presentation].[Pod 6])
      or (?Pod_Num_p? = 7 and [Presentation].[Pod 7])
      or (?Pod_Num_p? = 8 and [Presentation].[Pod 8])
      or (?Pod_Num_p? = 9 and [Presentation].[Pod 9])
      or (?Pod_Num_p? = 10 and [Presentation].[Pod 10])
      or (?Pod_Num_p? = 11 and [Presentation].[Pod 11])
      or (?Pod_Num_p? = 12 and [Presentation].[Pod 12])
      or (?Pod_Num_p? = 13 and [Presentation].[Pod 13])
      or (?Pod_Num_p? = 14 and [Presentation].[Pod 14])
      or (?Pod_Num_p? = 15 and [Presentation].[Pod 15])
      or (?Pod_Num_p? = 16 and [Presentation].[Pod 16])
      or (?Pod_Num_p? = 17 and [Presentation].[Pod 17])
      or (?Pod_Num_p? = 18 and [Presentation].[Pod 18])
      )
      )
      or
      ?Report_Selection_p? ='2'
    • Required
      [Presentation].[Case].[DATE_CLOSED_EST] between [Begin Date - Month to Yesterday] and [End Date - Month to Yesterday]
  • Query
    WIP - Service Standard for Raw Data
    Data Items
    20
    Case Number
    [Presentation].[Case].[ID]
    Contact Info
    [Presentation].[Case].[CONTACT_INFO]
    Age
    [Presentation].[Case].[CMT_AGE]
    Processing Centre
    [Presentation].[Case].[PROCESSING_CENTRE]
    Assigned To User ID
    trim (both ' ',trim (both '·',[Presentation].[Case].[ASSIGNEE_CURR]))
    Current Team
    [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]
    Date Open
    [Presentation].[Case].[DATE_OPEN_EST]
    Date Effective From
    [Presentation].[Case].[DATE_EFFECTIVE_FROM_EST]
    BF Date
    [Presentation].[Case].[DATE_BF_EST]
    Date Closed
    [Presentation].[Case].[DATE_CLOSED_EST]
    Classification
    [Presentation].[Case].[CLASSIFICATION]
    State Name
    [Presentation].[State].[NAME]
    Business Unit
    [Presentation].[VW_STG_DEPARTMENT].[CODE]
    PRI
    [Presentation].[Case].[PRI]
    Language
    [Presentation].[Case].[LANG]
    Work Type Name
    [Presentation].[Work Type].[NAME]
    Sub Type Name
    [Presentation].[Sub Type].[NAME]
    POD
    [Presentation].[VW_STG_DEPARTMENT].[POD]
    Created By
    [Presentation].[Case].[CREATED_BY]
    Was the Service Standard Met
    IF ( [Presentation].[Case].[CMT_AGE] <= [Presentation].[Sub Type].[SERVICE_STANDARD]
    ) THEN
    ( IF ( #sq($runLocale)#='fr') THEN
    ( 'Oui' )
    ELSE
    ( 'Yes' ) )
    ELSE
    (IF ( #sq($runLocale)#='fr' ) THEN
    ( 'Non' )
    ELSE
    ( 'No' ))
    • Detail Filters
      4
    • Required
      [Presentation].[Case].[DBID] <> 0
    • Required
      (
      ?Report_Selection_p? = '1'
      and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in ('ST-529', 'ST-938')
      )
      or
      (
      ?Report_Selection_p? = '2'
      and
      [Presentation].[Sub Type].[SUB_TYPE_CODE] in( 'ST-170', 'ST-176', 'ST-251', 'ST-254', 'ST-530', 'ST-531', 'ST-532', 'ST-533', 'ST-538', 'ST-540')
      )
    • Optional
      (?Report_Selection_p? ='1'
      and
      (
      ( ?Pod_Num_p? = '99999')
      or (?Pod_Num_p? = 0 and [Presentation].[Pod 0])
      or (?Pod_Num_p? = 1 and [Presentation].[Pod 1])
      or (?Pod_Num_p? = 2 and [Presentation].[Pod 2])
      or (?Pod_Num_p? = 3 and [Presentation].[Pod 3])
      or (?Pod_Num_p? = 4 and [Presentation].[Pod 4])
      or (?Pod_Num_p? = 5 and [Presentation].[Pod 5])
      or (?Pod_Num_p? = 6 and [Presentation].[Pod 6])
      or (?Pod_Num_p? = 7 and [Presentation].[Pod 7])
      or (?Pod_Num_p? = 8 and [Presentation].[Pod 8])
      or (?Pod_Num_p? = 9 and [Presentation].[Pod 9])
      or (?Pod_Num_p? = 10 and [Presentation].[Pod 10])
      or (?Pod_Num_p? = 11 and [Presentation].[Pod 11])
      or (?Pod_Num_p? = 12 and [Presentation].[Pod 12])
      or (?Pod_Num_p? = 13 and [Presentation].[Pod 13])
      or (?Pod_Num_p? = 14 and [Presentation].[Pod 14])
      or (?Pod_Num_p? = 15 and [Presentation].[Pod 15])
      or (?Pod_Num_p? = 16 and [Presentation].[Pod 16])
      or (?Pod_Num_p? = 17 and [Presentation].[Pod 17])
      or (?Pod_Num_p? = 18 and [Presentation].[Pod 18])
      )
      )
      or
      ?Report_Selection_p? ='2'
    • Required
      [Presentation].[State].[NAME] in ('Assigned', 'Invalid', 'Open', 'Pending', 'Ready_To_Verify', 'Reassigned', 'Verified', 'Triage_Follow-Up')
  • Query
    Final Rate Calculation
    Join 
    [Month to Yesterday - Maternity / Paternity LWOP].[Fake] = [Service Standard Success Rate for WIP].[Fake]
    refQuery
    Month to Yesterday - Maternity / Paternity LWOP
    1:1
    refQuery
    Service Standard Success Rate for WIP
    1:1
    Data Items
    5
    Within Standard Days Cases
    [Month to Yesterday - Maternity / Paternity LWOP].[Within Standard Days Cases]
    Exceeding Standard Days Cases
    [Month to Yesterday - Maternity / Paternity LWOP].[Exceeding Standard Days Cases]
    Subtotal 1
    [Service Standard Success Rate for WIP].[Subtotal 1]+[Month to Yesterday - Maternity / Paternity LWOP].[Within Standard Days Cases]
    Subtotal 2
    [Service Standard Success Rate for WIP].[Subtotal 2]+[Month to Yesterday - Maternity / Paternity LWOP].[Total Cases Closed Count]
    Success Rate
    ([Service Standard Success Rate for WIP].[Subtotal 1]+[Month to Yesterday - Maternity / Paternity LWOP].[Within Standard Days Cases]) / ([Service Standard Success Rate for WIP].[Subtotal 2]+[Month to Yesterday - Maternity / Paternity LWOP].[Total Cases Closed Count])
  • Query
    POD Lookup Q
    Data Items
    2
    POD_NO
    [Presentation].[VW_CMT_POD_LOOKUP].[POD_NO]
    DEPT_CODE
    [Presentation].[VW_CMT_POD_LOOKUP].[DEPT_CODE]
  • Query
    New Previous Month - Service Standard for Raw Data
    Join 
    [Previous Month - Service Standard for Raw Data].[Business Unit] = [POD Lookup Q].[DEPT_CODE]
    refQuery
    Previous Month - Service Standard for Raw Data
    1:1
    refQuery
    POD Lookup Q
    0:1
    Data Items
    20
    Case Number
    [Previous Month - Service Standard for Raw Data].[Case Number]
    Contact Info
    [Previous Month - Service Standard for Raw Data].[Contact Info]
    Age
    [Previous Month - Service Standard for Raw Data].[Age]
    Processing Centre
    [Previous Month - Service Standard for Raw Data].[Processing Centre]
    Assigned To User ID
    [Previous Month - Service Standard for Raw Data].[Assigned To User ID]
    Team Name
    [Previous Month - Service Standard for Raw Data].[Current Team]
    Date Open
    [Previous Month - Service Standard for Raw Data].[Date Open]
    Date Effective From
    [Previous Month - Service Standard for Raw Data].[Date Effective From]
    BF Date
    [Previous Month - Service Standard for Raw Data].[BF Date]
    Date Closed
    [Previous Month - Service Standard for Raw Data].[Date Closed]
    Classification
    [Previous Month - Service Standard for Raw Data].[Classification]
    State Name
    [Previous Month - Service Standard for Raw Data].[State Name]
    Business Unit
    [Previous Month - Service Standard for Raw Data].[Business Unit]
    PRI
    [Previous Month - Service Standard for Raw Data].[PRI]
    Language
    [Previous Month - Service Standard for Raw Data].[Language]
    Work Type Name
    [Previous Month - Service Standard for Raw Data].[Work Type Name]
    Sub Type Name
    [Previous Month - Service Standard for Raw Data].[Sub Type Name]
    POD
    [POD Lookup Q].[POD_NO]
    Created By
    [Previous Month - Service Standard for Raw Data].[Created By]
    Was the Service Standard Met
    [Previous Month - Service Standard for Raw Data].[Was the Service Standard Met]
  • Query
    New Month to Yesterday - Service Standard for Raw Data
    Join 
    [Month to Yesterday - Service Standard for Raw Data].[Business Unit] = [POD Lookup Q].[DEPT_CODE]
    refQuery
    Month to Yesterday - Service Standard for Raw Data
    1:1
    refQuery
    POD Lookup Q
    0:1
    Data Items
    22
    Case Number
    [Month to Yesterday - Service Standard for Raw Data].[Case Number]
    Contact Info
    [Month to Yesterday - Service Standard for Raw Data].[Contact Info]
    Age
    [Month to Yesterday - Service Standard for Raw Data].[Age]
    Processing Centre
    [Month to Yesterday - Service Standard for Raw Data].[Processing Centre]
    Assigned To User ID
    [Month to Yesterday - Service Standard for Raw Data].[Assigned To User ID]
    Current Team
    [Month to Yesterday - Service Standard for Raw Data].[Current Team]
    Date Open
    [Month to Yesterday - Service Standard for Raw Data].[Date Open]
    Date Effective From
    [Month to Yesterday - Service Standard for Raw Data].[Date Effective From]
    BF Date
    [Month to Yesterday - Service Standard for Raw Data].[BF Date]
    Date Closed
    [Month to Yesterday - Service Standard for Raw Data].[Date Closed]
    Classification
    [Month to Yesterday - Service Standard for Raw Data].[Classification]
    State Name
    [Month to Yesterday - Service Standard for Raw Data].[State Name]
    Business Unit
    [Month to Yesterday - Service Standard for Raw Data].[Business Unit]
    PRI
    [Month to Yesterday - Service Standard for Raw Data].[PRI]
    Language
    [Month to Yesterday - Service Standard for Raw Data].[Language]
    Work Type Name
    [Month to Yesterday - Service Standard for Raw Data].[Work Type Name]
    Sub Type Name
    [Month to Yesterday - Service Standard for Raw Data].[Sub Type Name]
    POD
    [POD Lookup Q].[POD_NO]
    Created By
    [Month to Yesterday - Service Standard for Raw Data].[Created By]
    Was the Service Standard Met
    [Month to Yesterday - Service Standard for Raw Data].[Was the Service Standard Met]
    Begin Date - Month to Yesterday
    [Month to Yesterday - Service Standard for Raw Data].[Begin Date - Month to Yesterday]
    End Date - Month to Yesterday
    [Month to Yesterday - Service Standard for Raw Data].[End Date - Month to Yesterday]
  • Query
    New WIP - Service Standard for Raw Data
    Join 
    [WIP - Service Standard for Raw Data].[Business Unit] = [POD Lookup Q].[DEPT_CODE]
    refQuery
    WIP - Service Standard for Raw Data
    1:1
    refQuery
    POD Lookup Q
    0:1
    Data Items
    20
    Case Number
    [WIP - Service Standard for Raw Data].[Case Number]
    Contact Info
    [WIP - Service Standard for Raw Data].[Contact Info]
    Age
    [WIP - Service Standard for Raw Data].[Age]
    Processing Centre
    [WIP - Service Standard for Raw Data].[Processing Centre]
    Assigned To User ID
    [WIP - Service Standard for Raw Data].[Assigned To User ID]
    Current Team
    [WIP - Service Standard for Raw Data].[Current Team]
    Date Open
    [WIP - Service Standard for Raw Data].[Date Open]
    Date Effective From
    [WIP - Service Standard for Raw Data].[Date Effective From]
    BF Date
    [WIP - Service Standard for Raw Data].[BF Date]
    Date Closed
    [WIP - Service Standard for Raw Data].[Date Closed]
    Classification
    [WIP - Service Standard for Raw Data].[Classification]
    State Name
    [WIP - Service Standard for Raw Data].[State Name]
    Business Unit
    [WIP - Service Standard for Raw Data].[Business Unit]
    PRI
    [WIP - Service Standard for Raw Data].[PRI]
    Language
    [WIP - Service Standard for Raw Data].[Language]
    Work Type Name
    [WIP - Service Standard for Raw Data].[Work Type Name]
    Sub Type Name
    [WIP - Service Standard for Raw Data].[Sub Type Name]
    POD
    [POD Lookup Q].[POD_NO]
    Created By
    [WIP - Service Standard for Raw Data].[Created By]
    Was the Service Standard Met
    [WIP - Service Standard for Raw Data].[Was the Service Standard Met]
  • Query
    POD Prompt Query
    Data Items
    2
    POD_NO
    [Presentation].[VW_CMT_POD_LOOKUP].[POD_NO]
    POD_Description
    [Presentation].[VW_CMT_POD_LOOKUP].[POD Name]
    sort
    sort: ascending ascending
    • Detail Filters
      1
    • Required
      [POD_NO] is not null
Layouts