Deployments
UAT_PCR_Deployment
Reports
Report Name: Departmental Case List
Overview

  • Queries: 8

    Layouts: 1

  • Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model
Queries
  • Queries 8
    Expressions:
  • Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model
  • Query
    Main Query
    Data Items
    25
    Departmental Name
    [Presentation].[VW_STG_DEPARTMENT].[NAME_1]
    Case Number
    [Presentation].[Case].[ID]
    PRI
    [Presentation].[Case].[PRI]
    Transaction Type
    [Presentation].[Sub Type].[TRANSACTION_TYPE]
    Work Type Name
    [Presentation].[Work Type].[NAME]
    Work Type Code
    [Presentation].[Work Type].[WORK_TYPE_CODE]
    Work-Type-name-cd
    [Work Type Name] + '(' + [Work Type Code] + ')'
    Sub Type Name
    [Presentation].[Sub Type].[NAME]
    Sub Type Code
    [Presentation].[Sub Type].[SUB_TYPE_CODE]
    Sub-Type-name-cd
    [Sub Type Name] + '(' + [Sub Type Code] + ')'
    State Name
    [Presentation].[State].[NAME]
    Case Category
    [Presentation].[State].[(en-zw) Case Category]
    Date Open
    [Presentation].[Case].[DATE_OPEN]
    Date Closed
    [Presentation].[Case].[DATE_CLOSED]
    Date Cancelled
    [Presentation].[Case].[Date Cancelled]
    BF Date
    [Presentation].[Case].[DATE_BF]
    Date Effective From
    [Presentation].[Case].[DATE_EFFECTIVE_FROM]
    Pending Reason
    [Presentation].[Case].[PENDING_REASON]
    Age Group
    Case
    When [Age] >= 0 and [Age] < 10
    then '0 – 9 days / jours'
    When [Age] > 9 and [Age] < 21
    Then '10 – 20 days / jours'
    When [Age] > 20 and [Age] < 31
    then '21 – 30 days / jours'
    When [Age] >= 30
    then '31+ days / jours'
    end
    Age Group-filter
    Case
    When [Age] >= 0 and [Age] < 10
    then '1'
    When [Age] > 9 and [Age] < 21
    Then '2'
    When [Age] > 20 and [Age] < 31
    then '3'
    When [Age] >= 30
    then '4'
    end
    Age
    [Presentation].[Case].[CMT_AGE]
    Priority
    [Presentation].[Sub Type].[PRIORITY]
    Created By category
    Case
    When [Presentation].[Case].[CREATED_BY] = 'CRCD / DCRC'
    then 'PAR / DIP'
    When [Presentation].[Case].[CREATED_BY] = 'Worklist / Liste de travail'
    then 'Worklist / Liste de travail'
    else 'Manual / Manuel'
    end
    Created By category-filter
    Case
    When [Presentation].[Case].[CREATED_BY] = 'CRCD / DCRC'
    then '1'
    When [Presentation].[Case].[CREATED_BY] = 'Worklist / Liste de travail'
    then '2'
    else '3'
    end
    Date Received
    [Presentation].[Case].[DATE_RECEIVED]
    • Detail Filters
      13
    • Optional
      [Date Open] in_range ?Param-date-open?
    • Optional
      [Date Closed] in_range ?Param-date-closed?
    • Optional
      [Date Effective From] in_range ?Param-date-effective?
    • Optional
      [Date Cancelled] in_range ?Param-date-cancelled?
    • Optional
      [Date Received] in_range ?Param-date-received?
    • Required
      [Departmental Name]= ?Param-dept?
    • Required
      [Case Category] = ?Param-case-category?

      #/*
      Developer Notes
      Author: R. Peters
      Date: Jan-22-2019
      JIRA Defect: BICCABC-2300
      Notes:
      Previous FILTER for CASE CATEGORY compared hard coded values against hard-coded values
      from the Prompt Collection.
      Case Category prompt is now sourced from a Query: Query-prompt-case-category
      As a result we can now just FILTER the CASE CATEGORY value from the Prompt
      against the same column that has been added to this query - thereby avoiding
      hard-coding of English/French values in the report.
      */#
    • Optional
      [Age Group-filter] = ?Param-age-group?
    • Optional
      [Presentation].[Work Type].[NAME] in (?Param-work-type?)
    • Optional
      [Presentation].[Sub Type].[TRANSACTION_TYPE] in (?Param-transaction-type?)
    • Optional
      [Presentation].[Sub Type].[NAME] in (?Param-sub-type?)
    • Optional
      [Created By category-filter] in ?Param-create-category?
    • Optional
      [Presentation].[Case].[PRI] in (?Param-pri?)
  • Query
    Load Last Date Query
    Data Items
    1
    Latest Valid Data Load Date
    [Presentation].[Data Load Archive].[Latest Valid Data Load Date]
  • Query
    Query-prompt-dept
    Data Items
    1
    Departmental Name
    [Presentation].[VW_STG_DEPARTMENT].[NAME_1]
  • Query
    Query-prompt-work-type
    Data Items
    1
    Work Type Name
    [Presentation].[Work Type].[NAME]
  • Query
    Query-prompt-transaction-type
    Data Items
    1
    Transaction Type
    [Presentation].[Sub Type].[TRANSACTION_TYPE]
  • Query
    Query-prompt-sub-type
    Data Items
    1
    Sub Type Name
    [Presentation].[Sub Type].[NAME]
  • Query
    Query-prompt-PRI
    Data Items
    1
    PRI
    [Presentation].[Case].[PRI]
  • Query
    Query-prompt-case-category
    Data Items
    2
    State Name
    [Presentation].[State].[NAME]
    Case Category
    [Presentation].[State].[(en-zw) Case Category]
Layouts