Queries

• 9
  Expressions:

  

  Data Items

  

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

• 

  

  Department_Prompt Query

  Data Items

  3

  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]

  • Detail Filters
    1
  • 

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

  

  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

• 

  

  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')
    
• 

  

  Daily Snapshot Department

  Data Items

  2

  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Department Name	[Presentation].[VW_STG_DEPARTMENT].[NAME_1]

  • Detail Filters
    1
  • 

    [Business Unit] is not null
    
• 

  

  Ticket Department

  Data Items

  2

  DEPARTMENT CODE	[Presentation].[Department].[DEPARTMENT_CODE]
  DEPARTMENT NAME	[Presentation].[Department].[DEPARTMENT_NAME]

• 

  

  Distinct Department

  

  

  Daily Snapshot Department

  

  Ticket Department

  • Projected Data Items
  • Business Unit
  • Department Name

  Data Items

  2

  Business Unit	[Union1].[Business Unit]
  Department Name	[Union1].[Department Name]

  • Detail Filters
    1
  • 

    [Department Name] contains '/'
    
• 

  

  Main Query

  Data Items

  6

  Selected Date	IF ( ?Date_Option_p? ='0' ) THEN ( #prompt('ParamEndDate', 'date', 'current_date')# ) ELSE ( _last_of_month (_add_months (current_date, -1) ) )
  Department Name	[Presentation].[Department].[DEPARTMENT_NAME]
  Business Unit	[Presentation].[Department].[DEPARTMENT_CODE]
  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]
  Department_Sorting	CASE [Department Name] WHEN 'Blank / Blanc ' THEN 2 ELSE 1 END
  VERSION DATE	cast([Presentation].[Daily_Snapshot].[VER_DATE],date)

  • Detail Filters
    4
  • 

    [Presentation].[Work_Type].[WORK_TYPE_CODE] <> ('WT-058')
    
  • 

    [VERSION DATE] = [Selected Date]
    
  • 

    [Presentation].[Daily_Snapshot].[TRANSACTION_TYPE]
    is not null
    and
    [Presentation].[Daily_Snapshot].[TRANSACTION_TYPE] in ('01: Promotions / Promotion', '02: Benefit Enrollment for New Hires / Employé nouvellement embauché - avantages sociaux', '03: Terminations / Cessation d''emploi', '04: Disability / Cas d''invalidité', '05: Parental leave / Congé parental', '06: Return from leave / Retour de congé', '07: Acting / Intérim', '08: Employee Transfer / Mutation', '09: Other / Autres')
    
  • 

    [Presentation].[Sub_Type].[SUB_TYPE_CODE] not in ('ST-024', 'ST-037', 'ST-041', 'ST-042', 'ST-050', 'ST-071', 'ST-072', 'ST-082', 'ST-083', 'ST-086', 'ST-089', 'ST-092', 'ST-093', 'ST-094', 'ST-095', 'ST-097', 'ST-098', 'ST-100', 'ST-118', 'ST-121', 'ST-122', 'ST-123', 'ST-124', 'ST-125', 'ST-132', 'ST-147', 'ST-149', 'ST-150', 'ST-153', 'ST-154', 'ST-155', 'ST-161', 'ST-162', 'ST-163', 'ST-167', 'ST-169', 'ST-170', 'ST-171', 'ST-182', 'ST-183', 'ST-188', 'ST-189', 'ST-191', 'ST-192', 'ST-193', 'ST-194', 'ST-195', 'ST-196', 'ST-197', 'ST-198', 'ST-199', 'ST-200', 'ST-201', 'ST-209', 'ST-210', 'ST-211', 'ST-212', 'ST-213', 'ST-214', 'ST-215', 'ST-216', 'ST-217', 'ST-222', 'ST-223', 'ST-224', 'ST-225', 'ST-232', 'ST-233', 'ST-234', 'ST-235', 'ST-236', 'ST-240', 'ST-241', 'ST-247', 'ST-262', 'ST-268', 'ST-269', 'ST-273', 'ST-280', 'ST-306', 'ST-504', 'ST-330', 'ST-505', 'ST-508', 'ST-509', 'ST-517', 'ST-518', 'ST-525', 'ST-526', 'ST-527', 'ST-538', 'ST-540', 'ST-611')
    
• 

  

  New Main

  

  [Main Query].[Business Unit] = [Distinct Department].[Business Unit]

  

  Main Query

  1:N

  

  Distinct Department

  1:1

  Data Items

  6

  Selected Date	[Main Query].[Selected Date]
  Department Name	[Distinct Department].[Department Name]
  Business Unit	[Main Query].[Business Unit]
  Active Cases	[Main Query].[Active Cases]
  Department_Sorting	[Main Query].[Department_Sorting]
  VERSION DATE	[Main Query].[VERSION DATE]

• 

  

  Final Query

  

  [New Main].[Business Unit] = [Departmental Population Query].[DEPT_CODE]

  

  New Main

  1:1

  

  Departmental Population Query

  1:1

  Data Items

  8

  Department Name	[New Main].[Department Name]
  Business Unit	[New Main].[Business Unit]
  Active Cases	[New Main].[Active Cases]
  Departmental Population	[Departmental Population Query].[POPULATION]
  Rank by Active Cases	rank ([Active Cases])
  Rank by Active Cases per Employee	rank ([Active Cases Per Active Employee])
  Active Cases Per Active Employee	[New Main].[Active Cases]/ [Departmental Population Query].[POPULATION]
  Link	1

  • Detail Filters
    1
  • 

    [Departmental Population] > 500
    

Layouts