Queries

• 10
  Expressions:

  

  Data Items

  

  Detail Filters
• Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model

• 

  

  Department_Prompt Query

  Data Items

  4

  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Running Count	cast(running-count ([Business Unit]), varchar(2))
  Departmental Name	[Running Count]+' . '+[Business Unit] + ' - '+[Presentation].[VW_STG_DEPARTMENT].[NAME_1]
  Department Count	count([Business Unit])

  • Detail Filters
    1
  • 

    [Business Unit] in ('IAN','PEN','PCH','JUS','DND','CSD','DOE','DFO','EXT','DUS','PCO','SVC','GSS','TBD')
    
• 

  

  Load Last Date Query

  Data Items

  1

  Latest Valid Data Load Date	[Presentation].[Data Load Archive].[Latest Valid Data Load Date]

• 

  

  Pre-Phoenix Cases Since 2016 April 20 Query

  Data Items

  2

  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Pre-Phoenix Cases Received since Go-Live(April 20, 2016)	count([Presentation].[Case].[Case Count] for [Business Unit])

  • Detail Filters
    2
  • 

    cast(_add_hours([Presentation].[Case].[DATE_OPEN], -5),date) between cast('2016-04-20',date) and _last_of_month (_add_months (current_date, -1))
    
  • 

    cast(_add_hours([Presentation].[Case].[DATE_EFFECTIVE_FROM], -5),date) between minimum (cast(_add_hours([Presentation].[Case].[DATE_EFFECTIVE_FROM], -5),date))
    and cast('2016-04-20',date)
    
• 

  

  Pre-Phoenix Cases Received since 2016 October 1 Query

  Data Items

  3

  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Pre-Phoenix Cases Received since Oct. 1, 2016	count([Presentation].[Case].[Case Count] for [Business Unit])
  Department Name	[Presentation].[VW_STG_DEPARTMENT].[NAME_1]

  • Detail Filters
    2
  • 

    cast(_add_hours([Presentation].[Case].[DATE_OPEN], -5),date) between cast('2016-10-01',date) and _last_of_month (_add_months (current_date, -1))
    
  • 

    cast(_add_hours([Presentation].[Case].[DATE_EFFECTIVE_FROM], -5),date) between
    minimum (cast(_add_hours([Presentation].[Case].[DATE_EFFECTIVE_FROM], -5),date))
    and cast('2016-04-20',date)
    
• 

  

  All Cases Since 2016 October 1 Query

  Data Items

  2

  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  All Cases Received Since October 1, 2016	count([Presentation].[Case].[Case Count] for [Business Unit])

  • Detail Filters
    1
  • 

    cast(_add_hours([Presentation].[Case].[DATE_OPEN], -5),date) between cast('2016-10-01',date)
    and _last_of_month (_add_months (current_date, -1))
    
• 

  

  All Active Cases of Pre-Phoenix Since 2016 October 1 Query

  Data Items

  2

  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  All Cases Currently in the queue of those Pre-Phoenix Cases Received Since October 1, 2016	count([Presentation].[Case].[Case Count] for [Business Unit])

  • Detail Filters
    3
  • 

    cast(_add_hours([Presentation].[Case].[DATE_OPEN], -5),date) between
    cast('2016-10-01',date) and _last_of_month(_add_months (current_date, -1))
    
  • 

    [Presentation].[State].[NAME] not in ('Bring_Forward', 'Cancelled', 'Closed', 'Invalid')
    
  • 

    cast(_add_hours([Presentation].[Case].[DATE_EFFECTIVE_FROM], -5),date) between
    minimum (cast(_add_hours([Presentation].[Case].[DATE_EFFECTIVE_FROM], -5),date))
    and cast('2016-04-20',date)
    
• 

  

  Ratio of Pre-Phoenix Cases Query

  

  [Pre-Phoenix Cases Received since 2016 October 1 Query].[Business Unit] = [All Cases Since 2016 October 1 Query].[Business Unit]

  

  Pre-Phoenix Cases Received since 2016 October 1 Query

  1:1

  

  All Cases Since 2016 October 1 Query

  1:1

  Data Items

  2

  Business Unit	[Pre-Phoenix Cases Received since 2016 October 1 Query].[Business Unit]
  Ratio of Pre-Phoenix Cases	[Pre-Phoenix Cases Received since 2016 October 1 Query].[Pre-Phoenix Cases Received since Oct. 1, 2016] / [All Cases Since 2016 October 1 Query].[All Cases Received Since October 1, 2016 ]

• 

  

  Department Population For Join Pre-Phoenix

  • queryHints
  • outerJoinAllowed: allow

  Data Items

  2

  Business Unit	[Presentation].[VW_CMT_DEPARTMENT_POPULATION].[(en-zw) DEPT_CODE]
  Departmental Population	[Presentation].[VW_CMT_DEPARTMENT_POPULATION].[(en-zw) POPULATION]

• 

  

  Old Backlog Received After October 1 2016 Per Active Employee Query

  

  [Department Population For Join Pre-Phoenix].[Business Unit] = [Pre-Phoenix Cases Received since 2016 October 1 Query].[Business Unit]

  

  Department Population For Join Pre-Phoenix

  1:1

  

  Pre-Phoenix Cases Received since 2016 October 1 Query

  1:1

  • queryHints
  • outerJoinAllowed: allow

  Data Items

  6

  Business Unit	[Department Population For Join Pre-Phoenix].[Business Unit]
  Departmental Name	[Pre-Phoenix Cases Received since 2016 October 1 Query].[Department Name]
  Old Backlog Received After October 1, 2016 Per Active Employee	[Pre-Phoenix Cases Received since 2016 October 1 Query].[Pre-Phoenix Cases Received since Oct. 1, 2016] / [Department Population For Join Pre-Phoenix].[Departmental Population]
  Departmental Population	[Department Population For Join Pre-Phoenix].[Departmental Population]
  Rank of Old Backlog Received After October 1, 2016 Per Active Employee	rank ([Old Backlog Received After October 1, 2016 Per Active Employee])
  Link	1

  • Detail Filters
    1
  • 

    [Departmental Population] > 500
    
• 

  

  Department For Title Query

  Data Items

  3

  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Departmental Name	[Business Unit] + ' - '+[Presentation].[VW_STG_DEPARTMENT].[NAME_1]
  Link	1

  • Detail Filters
    2
  • 

    [Business Unit] = (?Department_p?)
    
  • 

    [Business Unit] in ('IAN','PEN','PCH','JUS','DND','CSD','DOE','DFO','EXT','DUS','PCO','SVC','GSS','TBD')
    

Layouts