Deployments
UAT_PCR_Deployment
Reports
Report Name: POD Monthly Service Standard Reports
Overview

  • Queries: 4

    Layouts: 1

  • Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model
Queries
  • Queries 4
    Expressions:
  • Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model
  • Query
    Main
    Data Items
    38
    Case Number
    [Presentation].[Case].[ID]
    Age
    [Presentation].[Case].[CMT_AGE]
    Date Open
    [Presentation].[Case].[DATE_OPEN_EST]
    Service Standard Name
    [Presentation].[Sub Type].[SERVICE_STANDARD_NAME]
    label
    Types Of Transaction
    Modified Service standard Name
    case
    when ([Sub Type Code] in ('ST-236','ST-510','ST-052','ST-099','ST-237') ) then
    ('Overtime / Heures supplémentaires')
    else
    ([Service Standard Name])
    end
    Service Standard Days
    cast([Presentation].[Sub Type].[SERVICE_STANDARD], Integer)
    Modified Service Standard Days
    case
    when ([Sub Type Code] in ('ST-236','ST-510','ST-052','ST-099','ST-237') ) then
    20
    else
    ([Service Standard Days])
    end
    Processing Centre
    [Presentation].[Case].[PROCESSING_CENTRE]
    Date Effective From
    [Presentation].[Case].[DATE_EFFECTIVE_FROM_EST]
    EX / Non EX
    [Presentation].[Case].[SUBJECT]
    BF Date
    [Presentation].[Case].[DATE_BF_EST]
    Date Closed
    [Presentation].[Case].[DATE_CLOSED_EST]
    DateOpen/Date BF
    coalesce ([BF Date], [Date Open])
    Aggregate
    maximum maximum
    Date Closed Est
    [Presentation].[Case].[DATE_CLOSED_EST]
    Classification
    [Presentation].[Case].[CLASSIFICATION]
    State Name
    [Presentation].[State].[NAME]
    PRI
    [Presentation].[Case].[PRI]
    Language
    [Presentation].[Case].[LANG]
    Sub Type Name
    [Presentation].[Sub Type].[NAME]
    Sub Type Code
    [Presentation].[Sub Type].[SUB_TYPE_CODE]
    Work Type Name
    [Presentation].[Work Type].[NAME]
    Work Type Code
    [Presentation].[Work Type].[WORK_TYPE_CODE]
    Created By
    [Presentation].[Case].[CREATED_BY]
    Was the Service Standard met?
    If ([Age] <= [Modified Service Standard Days])
    then ('Yes / Oui')
    else ('No / Non')
    Previous Month
    _add_months (current_date, -1)
    Case Count
    [Presentation].[Case].[Case Count]
    Aggregate
    count count
    Unique PRI
    count(distinct [PRI])
    Success Cases
    total(if ([Was the Service Standard met?] = 'Yes / Oui') then
    (1)
    else (0) for [Modified Service standard Name])
    Total Cases for sevice standard
    count([Case Number] for [Modified Service standard Name])
    Success Rate
    round (([Success Cases] / [Total Cases for sevice standard]) * 100, 2) + '%'
    Overall Sucess Rate
    round ((total([Success Cases] for report ) / total([Total Cases for sevice standard] for report) ) * 100, 2) + '%'
    Average Wait Time
    round(average ([Age] for [Modified Service standard Name]), 0)
    label
    Average Wait Time (in Days)
    Overall Average wait time
    round(average([Average Wait Time ] for report),0)
    POD_START_DT
    [Presentation].[VW_CMT_POD_LOOKUP].[POD_START_DT]
    Previous Month 1st day
    _first_of_month ([Previous Month])
    Business Unit
    [Presentation].[VW_STG_DEPARTMENT].[CODE]
    Group
    [Presentation].[VW_STG_ACCESS_CONTROL].[NAME]
    Previous Month Last day
    _last_of_month ([Previous Month])
    • Detail Filters
      5
    • Required
      case
      when (?ParamPod? = 0)
      then ([Presentation].[Pod 0] and [Presentation].[Exclude Specialized])
      when (?ParamPod? = 1)
      then ([Presentation].[Pod 1] and [Presentation].[Exclude Specialized])
      when (?ParamPod? = 2)
      then ([Presentation].[Pod 2] and [Presentation].[Exclude Specialized])
      when (?ParamPod? = 3)
      then ([Presentation].[Pod 3] and [Presentation].[Exclude Specialized])
      when (?ParamPod? = 4)
      then ([Presentation].[Pod 4] and [Presentation].[Exclude Specialized])
      when (?ParamPod? = 5)
      then ([Presentation].[Pod 5] and [Presentation].[Exclude Specialized])
      when (?ParamPod? = 6)
      then ([Presentation].[Pod 6] and [Presentation].[Exclude Specialized])
      when (?ParamPod? = 7)
      then ([Presentation].[Pod 7] and [Presentation].[Exclude Specialized])
      when (?ParamPod? = 8)
      then ([Presentation].[Pod 8] and
      (
      [Group] <> 'Restricted'
      and
      ([EX / Non EX] = 'Non-EX' or [EX / Non EX] = 'SC/SO')
      and
      [Work Type Code] <> 'WT-058'
      and
      [Sub Type Code] not in ('ST-043',
      'ST-045',
      'ST-046',
      'ST-067',
      'ST-071',
      'ST-082',
      'ST-087',
      'ST-106',
      'ST-126',
      'ST-132',
      'ST-133',
      'ST-134',
      'ST-150',
      'ST-170',
      'ST-172',
      'ST-174',
      'ST-176',
      'ST-185',
      'ST-186',
      'ST-187',
      'ST-203',
      'ST-207',
      'ST-209',
      'ST-210',
      'ST-239',
      'ST-246',
      'ST-250',
      'ST-251',
      'ST-254',
      'ST-255',
      'ST-258',
      'ST-264',
      'ST-268',
      'ST-271',
      'ST-304',
      'ST-326',
      'ST-331',
      'ST-341',
      'ST-516',
      'ST-520',
      'ST-521',
      'ST-522',
      'ST-530',
      'ST-531',
      'ST-532',
      'ST-533',
      'ST-536',
      'ST-538',
      'ST-540',
      'ST-542',
      'ST-543',
      'ST-544',
      'ST-545',
      'ST-600',
      'ST-605')
      )
      )
      when (?ParamPod? = 9)
      then ([Presentation].[Pod 9] and [Presentation].[Exclude Specialized])
      when (?ParamPod? = 10)
      then ([Presentation].[Pod 10] and [Presentation].[Exclude Specialized])
      when (?ParamPod? = 11)
      then ([Presentation].[Pod 11] and [Presentation].[Exclude Specialized])
      when (?ParamPod? = 12)
      then ([Presentation].[Pod 12] and [Presentation].[Exclude Specialized])
      when (?ParamPod? = 13)
      then ([Presentation].[Pod 13] and [Presentation].[Exclude Specialized])
      when (?ParamPod? = 14)
      then ([Presentation].[Pod 14] and [Presentation].[Exclude Specialized])
      when (?ParamPod? = 15)
      then ([Presentation].[Pod 15] and [Presentation].[Exclude Specialized])
      when (?ParamPod? = 16)
      then ([Presentation].[Pod 16] and [Presentation].[Exclude Specialized])
      when (?ParamPod? = 17)
      then ([Presentation].[Pod 17] and [Presentation].[Exclude Specialized])
      when (?ParamPod? = 18)
      then ([Presentation].[Pod 18] and [Presentation].[Exclude Specialized])
      end
    • Required
      [State Name] = 'Closed'
    • Required
      if (?Param-Date-Daily? = 0) then
      (cast([Date Closed Est], date) in_range ?Param_Date_Closed?)
      else if (?Param-Date-Daily? = 1) then
      (cast([Date Closed Est], date) between _first_of_month ([Previous Month]) and _last_of_month ([Previous Month]))
      else (0)
    • Required
      cast([DateOpen/Date BF], date) > [POD_START_DT]
    • Required
      [Presentation].[Case].[PROCESSING_CENTRE] not contains [Presentation].[VW_CMT_POD_LOOKUP].[POD_TAG] OR [Presentation].[Case].[PROCESSING_CENTRE] is null
  • Query
    Last Valid Date
    Data Items
    1
    Latest Valid Load Date
    [Presentation].[Data Load Archive].[Latest Valid Data Load Date]
  • Query
    Notes
    Data Items
    2
    Service Standard Name
    [Presentation].[Sub Type].[SERVICE_STANDARD_NAME]
    Sub Type Name
    [Presentation].[Sub Type].[NAME]
  • Query
    Pod
    Data Items
    1
    POD_NO
    [Presentation].[VW_CMT_POD_LOOKUP].[POD_NO]
    • Detail Filters
      1
    • Required
      [POD_NO] is not null
Layouts