Deployments
UAT_PCR_Deployment
Reports
Report Name: Status Update on Key CMT Data Dimensions - Open Cases by Priority
Overview

  • Queries: 5

    Layouts: 1

  • Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model
Queries
  • Queries 5
    Expressions:
  • Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model
  • Query
    Main Query
    Data Items
    10
    VERSION DATE
    cast([Presentation].[Daily_Snapshot].[VER_DATE],date)
    Priority
    IF ( #sq($runLocale)#='fr' or #sq($runLocale)#='fr-ca') THEN
    (CASE [Presentation].[Sub_Type].[PRIORITY]
    WHEN 1 THEN 'Élevé'
    WHEN 2 THEN 'Bas'
    WHEN 0 THEN 'Aucun'
    END)
    ELSE
    (CASE [Presentation].[Sub_Type].[PRIORITY]
    WHEN 1 THEN 'High'
    WHEN 2 THEN 'Low'
    WHEN 0 THEN 'No'
    END)
    Business Unit
    [Presentation].[Department].[DEPARTMENT_CODE]
    DEPARTMENT NAME
    [Business Unit] + ' - '+[Presentation].[Department].[DEPARTMENT_NAME]
    Priority Sorting
    CASE [Priority]
    WHEN 'High' THEN 1
    WHEN 'Élevé' THEN 1
    WHEN 'Low' THEN 2
    WHEN 'Bas' THEN 2
    WHEN 'No' THEN 3
    WHEN 'Aucun' THEN 3
    ELSE 4
    END
    Transaction Type
    [Presentation].[Daily_Snapshot].[TRANSACTION_TYPE]
    Active Cases
    [Presentation].[Daily_Snapshot].[COUNT_SUB_TYPE_TOTAL]-[Presentation].[Daily_Snapshot].[COUNT_BRING_FORWARD]-
    [Presentation].[Daily_Snapshot].[COUNT_CANCELLED]-
    [Presentation].[Daily_Snapshot].[COUNT_CLOSED]-
    [Presentation].[Daily_Snapshot].[COUNT_INVALID]
    TRANSACTION TYPE NAME
    [Presentation].[Sub_Type].[TRANSACTION_TYPE_NAME]
    Subtotal of Department
    total([Active Cases] for [DEPARTMENT NAME])
    Selected Date
    IF ( ?Date_Option_p? ='0' ) THEN
    ( cast(?ParamEndDate?,date) )
    ELSE
    ( _last_of_month (_add_months (current_date, -1) )
    )
    • Detail Filters
      3
    • Required
      [Presentation].[Work_Type].[WORK_TYPE_CODE] <> ('WT-058')
    • Optional
      [Business Unit] in (?Department_p?)
    • Required
      [VERSION DATE] = [Selected Date]
  • Query
    Department_Prompt Query
    Data Items
    3
    Business Unit
    [Presentation].[VW_STG_DEPARTMENT].[CODE]
    Running Count
    cast(running-count ([Business Unit]), varchar(3))
    Departmental Name
    [Running Count]+' . '+[Business Unit] + ' - '+[Presentation].[VW_STG_DEPARTMENT].[NAME_1]
    • Detail Filters
      1
    • Required
      [Business Unit] in ('IAN','PEN','PCH','JUS','DND','CSD','DOE','DFO','EXT','DUS','PCO','SVC','GSS','TBD')
  • Query
    Burst Query
    Data Items
    3
    Bursting Key
    'CAMID("::Everyone")'
    Business Unit
    [Presentation].[VW_STG_DEPARTMENT].[CODE]
    Full Department Name
    [Business Unit] + ' - '+[Presentation].[VW_STG_DEPARTMENT].[NAME_1]
    • Detail Filters
      2
    • Required
      [Business Unit] is not null
    • Required
      [Business Unit] in ('IAN','PEN','PCH','JUS','DND','CSD','DOE','DFO','EXT','DUS','PCO','SVC','GSS','TBD')
  • Query
    Departmental Population Query
    Data Items
    2
    DEPT_CODE
    [Presentation].[VW_CMT_DEPARTMENT_POPULATION].[(en-zw) DEPT_CODE]
    POPULATION
    [Presentation].[VW_CMT_DEPARTMENT_POPULATION].[(en-zw) POPULATION]
    Aggregate
    total total
  • Query
    Fianl Query
    Join 
    [Main Query].[Business Unit] = [Departmental Population Query].[DEPT_CODE]
    refQuery
    Main Query
    1:1
    refQuery
    Departmental Population Query
    1:1
    Data Items
    9
    Priority
    [Main Query].[Priority]
    Business Unit
    [Main Query].[Business Unit]
    DEPARTMENT NAME
    [Main Query].[DEPARTMENT NAME]
    Priority Sorting
    [Main Query].[Priority Sorting]
    Transaction Type
    [Main Query].[Transaction Type]
    Active Cases
    [Main Query].[Active Cases]
    TRANSACTION TYPE NAME
    [Main Query].[TRANSACTION TYPE NAME]
    POPULATION
    [Departmental Population Query].[POPULATION]
    Subtotal of Department
    [Main Query].[Subtotal of Department]
    • Detail Filters
      1
    • Required
      [POPULATION] > 500
Layouts