Queries

• 10
  Expressions:

  

  Data Items

  

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

• 

  

  Query-Closed

  Data Items

  16

  Case Number	[Presentation].[Case].[ID]
  Processing Centre	'="'+[Presentation].[Case].[PROCESSING_CENTRE]+'"'
  Age	[Presentation].[Case].[CMT_AGE]
  Date Open	#/* Replaced with New OPEN date field */# '="' + cast([Presentation].[Case].[DATE_OPEN_EST_DATE_TIME],varchar(19)) + '"'
  BF Date	#/* Replaced with New BF date field */# '="' + cast([Presentation].[Case].[DATE_BF_EST_DATE_TIME],varchar(19)) + '"'
  Work Type Name	[Presentation].[Work Type].[NAME]
  Sub Type Name	[Presentation].[Sub Type].[NAME]
  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Classification	[Presentation].[Case].[CLASSIFICATION]
  Assigned To	trim (both ' ',trim (both '·',[Presentation].[Case].[ASSIGNEE_CURR]))
  Group Name	#/* Replaced with Team Name field from Current Team on Case */# CASE(char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name])-21) WHEN 1 THEN 'Team '+'00'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 2 THEN 'Team '+'0'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 3 THEN 'Team '+''+substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) ELSE [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name] END
  Action	'4: Closed'
  ACTION_TIMESTAMP	#/* Replaced with New CLOSE date field */# [Presentation].[Case].[DATE_CLOSED_EST]
  Date of Action(-4hrs)	#/* Replaced with New CLOSE date field */# [Presentation].[Case].[DATE_CLOSED_EST]
  Current Date Stamp-1day	(_add_days ( cast(getdate(), date), - 1))	label Current Date Stamp-1
  Group	[Presentation].[VW_STG_ACCESS_CONTROL].[NAME]

  • Detail Filters
    4
  • 

    [Presentation].[Case].[DBID] <> 0
    
  • 

    [ACTION_TIMESTAMP] = [Current Date Stamp-1day]
    
  • 

    [Presentation].[State].[NAME] in ('Closed')
    
  • 

    [Presentation].[VW_STG_CURRENT_TEAM].[ASSIGNEE_CURR_ID] <> 0
    
• 

  

  Query-Assigned

  Data Items

  16

  Case Number	[Presentation].[Case].[ID]
  Processing Centre	'="'+[Presentation].[Case].[PROCESSING_CENTRE]+'"'
  Age	[Presentation].[Case].[CMT_AGE]
  Date Open	#/* Replaced with New OPEN date field */# '="' + cast([Presentation].[Case].[DATE_OPEN_EST_DATE_TIME],varchar(19)) + '"'
  BF Date	#/* Replaced with New BF date field */# '="' + cast([Presentation].[Case].[DATE_BF_EST_DATE_TIME],varchar(19)) + '"'
  Work Type Name	[Presentation].[Work Type].[NAME]
  Sub Type Name	[Presentation].[Sub Type].[NAME]
  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Classification	[Presentation].[Case].[CLASSIFICATION]
  Assigned To	trim (both ' ',trim (both '·',[Presentation].[Case].[ASSIGNEE_CURR]))
  Group Name	#/* Replaced with Team Name field from Current Team on Case */# CASE(char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name])-21) WHEN 1 THEN 'Team '+'00'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 2 THEN 'Team '+'0'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 3 THEN 'Team '+''+substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) ELSE [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name] END
  Action	'1: Assigned'
  ACTION_TIMESTAMP	#/* Replaced with DATE_ASSIGNED_EST date field */# [Presentation].[Case].[DATE_ASSIGNED_EST]
  Date of Action(-4hrs)	#/* Replaced with DATE_ASSIGNED_EST date field */# [Presentation].[Case].[DATE_ASSIGNED_EST]
  Current Date Stamp-1day	(_add_days ( cast(getdate(), date), - 1 ))	label Current Date Stamp
  Group	[Presentation].[VW_STG_ACCESS_CONTROL].[NAME]

  • Detail Filters
    4
  • 

    [Presentation].[Case].[DBID] <> 0
    
  • 

    [Presentation].[Case].[Indicator Flag] is null
    
  • 

    [ACTION_TIMESTAMP] = [Current Date Stamp-1day]
    
  • 

    [Presentation].[VW_STG_CURRENT_TEAM].[ASSIGNEE_CURR_ID] <> 0
    
• 

  

  Query-Sent-For-Verification

  Data Items

  16

  Case Number	[Presentation].[Case].[ID]
  Processing Centre	'="'+[Presentation].[Case].[PROCESSING_CENTRE]+'"'
  Age	[Presentation].[Case].[CMT_AGE]
  Date Open	#/* Replaced with New OPEN date field */# '="' + cast([Presentation].[Case].[DATE_OPEN_EST_DATE_TIME],varchar(19)) + '"'
  BF Date	#/* Replaced with New BF date field */# '="' + cast([Presentation].[Case].[DATE_BF_EST_DATE_TIME],varchar(19)) + '"'
  Work Type Name	[Presentation].[Work Type].[NAME]
  Sub Type Name	[Presentation].[Sub Type].[NAME]
  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Classification	[Presentation].[Case].[CLASSIFICATION]
  Assigned To	trim (both ' ',trim (both '·',[Presentation].[Case].[ASSIGNEE_CURR]))
  Group Name	#/* Replaced with Team Name field from Current Team on Case */# CASE(char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name])-21) WHEN 1 THEN 'Team '+'00'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 2 THEN 'Team '+'0'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 3 THEN 'Team '+''+substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) ELSE [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name] END
  Action	'2: Sent for Verification'
  ACTION_TIMESTAMP	#/* Replaced with New ACTION_TIMESTAMP_EST date field */# [Presentation].[VW_STG_HISTORY].[ACTION_TIMESTAMP_EST]
  Date of Action(-4hrs)	#/* Replaced with New ACTION_TIMESTAMP_EST date field */# [Presentation].[VW_STG_HISTORY].[ACTION_TIMESTAMP_EST]
  Current Date Stamp-1day	(_add_days ( cast(getdate(), date), - 1 ))	label Current Date Stamp
  Group	[Presentation].[VW_STG_ACCESS_CONTROL].[NAME]

  • Detail Filters
    4
  • 

    [Presentation].[Case].[DBID] <> 0
    
  • 

    ([Presentation].[VW_STG_HISTORY].[ENTITYDEF_ID] = 16777228)
    and
    (([Presentation].[VW_STG_HISTORY].[NEW_STATE] = 'Ready_To_Verify')
    and
    ([Presentation].[VW_STG_HISTORY].[OLD_STATE] = 'Assigned'))
    
  • 

    [ACTION_TIMESTAMP] = [Current Date Stamp-1day]
    
  • 

    [Presentation].[VW_STG_CURRENT_TEAM].[ASSIGNEE_CURR_ID] <> 0
    
• 

  

  Query-Returned-From-Verification

  Data Items

  16

  Case Number	[Presentation].[Case].[ID]
  Processing Centre	'="'+[Presentation].[Case].[PROCESSING_CENTRE]+'"'
  Age	[Presentation].[Case].[CMT_AGE]
  Date Open	#/* Replaced with New OPEN date field */# '="' + cast([Presentation].[Case].[DATE_OPEN_EST_DATE_TIME],varchar(19)) + '"'
  BF Date	#/* Replaced with New BF date field */# '="' + cast([Presentation].[Case].[DATE_BF_EST_DATE_TIME],varchar(19)) + '"'
  Work Type Name	[Presentation].[Work Type].[NAME]
  Sub Type Name	[Presentation].[Sub Type].[NAME]
  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Classification	[Presentation].[Case].[CLASSIFICATION]
  Assigned To	trim (both ' ',trim (both '·',[Presentation].[Case].[ASSIGNEE_CURR]))
  Group Name	#/* Replaced with Team Name field from Current Team on Case */# CASE(char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name])-21) WHEN 1 THEN 'Team '+'00'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 2 THEN 'Team '+'0'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 3 THEN 'Team '+''+substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) ELSE [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name] END
  Action	'3: Returned from Verification'
  ACTION_TIMESTAMP	#/* Replaced with New ACTION_TIMESTAMP_EST date field */# [Presentation].[VW_STG_HISTORY].[ACTION_TIMESTAMP_EST]
  Date of Action(-4hrs)	#/* Replaced with New ACTION_TIMESTAMP_EST date field */# [Presentation].[VW_STG_HISTORY].[ACTION_TIMESTAMP_EST]
  Current Date Stamp-1day	(_add_days ( cast(getdate(), date), - 1 ))	label Current Date Stamp
  Group	[Presentation].[VW_STG_ACCESS_CONTROL].[NAME]

  • Detail Filters
    4
  • 

    [Presentation].[Case].[DBID] > 0
    
  • 

    ([Presentation].[VW_STG_HISTORY].[ENTITYDEF_ID] = 16777228)
    and
    (([Presentation].[VW_STG_HISTORY].[NEW_STATE] = 'Reassigned')
    and
    ([Presentation].[VW_STG_HISTORY].[OLD_STATE] = 'Ready_To_Verify'))
    
  • 

    [ACTION_TIMESTAMP] = [Current Date Stamp-1day]
    
  • 

    [Presentation].[VW_STG_CURRENT_TEAM].[ASSIGNEE_CURR_ID] <> 0
    
• 

  

  Query-Verified

  Data Items

  16

  Case Number	[Presentation].[Case].[ID]
  Processing Centre	'="'+[Presentation].[Case].[PROCESSING_CENTRE]+'"'
  Age	[Presentation].[Case].[CMT_AGE]
  Date Open	#/* Replaced with New OPEN date field */# '="' + cast([Presentation].[Case].[DATE_OPEN_EST_DATE_TIME],varchar(19)) + '"'
  BF Date	#/* Replaced with New BF date field */# '="' + cast([Presentation].[Case].[DATE_BF_EST_DATE_TIME],varchar(19)) + '"'
  Work Type Name	[Presentation].[Work Type].[NAME]
  Sub Type Name	[Presentation].[Sub Type].[NAME]
  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Classification	[Presentation].[Case].[CLASSIFICATION]
  Assigned To	[Presentation].[Case].[VERIFIER]
  Group Name	'Verifiers/Coaches'
  Action	'6: Verified'
  ACTION_TIMESTAMP	#/* Replaced with New VERIFIED_DATE_EST date field */# [Presentation].[Case].[VERIFIED_DATE_EST]
  Date of Action(-4hrs)	#/* Replaced with New VERIFIED_DATE_EST date field */# [Presentation].[Case].[VERIFIED_DATE_EST]
  Current Date Stamp-1day	(_add_days ( cast(getdate(), date), - 1 ))	label Current Date Stamp
  Group	[Presentation].[VW_STG_ACCESS_CONTROL].[NAME]

  • Detail Filters
    2
  • 

    [Presentation].[Case].[DBID] <> 0
    
  • 

    [ACTION_TIMESTAMP]= [Current Date Stamp-1day]
    
• 

  

  Query-Rejected-By-Verifier

  Data Items

  16

  Case Number	[Presentation].[Case].[ID]
  Processing Centre	'="'+[Presentation].[Case].[PROCESSING_CENTRE]+'"'
  Age	[Presentation].[Case].[CMT_AGE]
  Date Open	#/* Replaced with New OPEN date field */# '="' + cast([Presentation].[Case].[DATE_OPEN_EST_DATE_TIME],varchar(19)) + '"'
  BF Date	#/* Replaced with New BF date field */# '="' + cast([Presentation].[Case].[DATE_BF_EST_DATE_TIME],varchar(19)) + '"'
  Work Type Name	[Presentation].[Work Type].[NAME]
  Sub Type Name	[Presentation].[Sub Type].[NAME]
  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Classification	[Presentation].[Case].[CLASSIFICATION]
  Assigned To	[Presentation].[Case].[VERIFIER]
  Group Name	'Verifiers/Coaches'
  Action	'7: Rejected by Verifier'
  ACTION_TIMESTAMP	#/* Replaced with New ACTION_TIMESTAMP_EST date field */# [Presentation].[VW_STG_HISTORY].[ACTION_TIMESTAMP_EST]
  Date of Action(-4hrs)	#/* Replaced with New ACTION_TIMESTAMP_EST date field */# [Presentation].[VW_STG_HISTORY].[ACTION_TIMESTAMP_EST]
  Current Date Stamp-1day	(_add_days ( cast(getdate(), date), - 1))	label Current Date Stamp
  Group	[Presentation].[VW_STG_ACCESS_CONTROL].[NAME]

  • Detail Filters
    4
  • 

    [Presentation].[Case].[DBID] <> 0
    
  • 

    [Presentation].[VW_STG_HISTORY].[ENTITYDEF_ID] = 16777228
    
  • 

    ([Presentation].[VW_STG_HISTORY].[NEW_STATE] = 'Reassigned')
    and
    ([Presentation].[VW_STG_HISTORY].[OLD_STATE] = 'Ready_To_Verify')
    
  • 

    [ACTION_TIMESTAMP] = [Current Date Stamp-1day]
    
• 

  

  Query-Pending

  Data Items

  16

  Case Number	[Presentation].[Case].[ID]
  Processing Centre	'="'+[Presentation].[Case].[PROCESSING_CENTRE]+'"'
  Age	[Presentation].[Case].[CMT_AGE]
  Date Open	#/* Replaced with New OPEN date field */# '="' + cast([Presentation].[Case].[DATE_OPEN_EST_DATE_TIME],varchar(19)) + '"'
  BF Date	#/* Replaced with New BF date field */# '="' + cast([Presentation].[Case].[DATE_BF_EST_DATE_TIME],varchar(19)) + '"'
  Work Type Name	[Presentation].[Work Type].[NAME]
  Sub Type Name	[Presentation].[Sub Type].[NAME]
  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Classification	[Presentation].[Case].[CLASSIFICATION]
  Assigned To	trim (both ' ',trim (both '·',[Presentation].[Case].[ASSIGNEE_CURR]))
  Group Name	#/* Replaced with Team Name field from Current Team on Case */# CASE(char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name])-21) WHEN 1 THEN 'Team '+'00'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 2 THEN 'Team '+'0'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 3 THEN 'Team '+''+substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) ELSE [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name] END
  Action	'8: Sent to Pending'
  ACTION_TIMESTAMP	#/* Replaced with New ACTION_TIMESTAMP_EST date field */# [Presentation].[VW_STG_HISTORY].[ACTION_TIMESTAMP_EST]
  Date of Action(-4hrs)	#/* Replaced with New ACTION_TIMESTAMP_EST date field */# [Presentation].[VW_STG_HISTORY].[ACTION_TIMESTAMP_EST]
  Current Date Stamp-1day	(_add_days ( cast(getdate(), date), - 1))	label Current Date Stamp
  Group	[Presentation].[VW_STG_ACCESS_CONTROL].[NAME]

  • Detail Filters
    5
  • 

    [Presentation].[Case].[DBID] <> 0
    
  • 

    [Presentation].[VW_STG_HISTORY].[ENTITYDEF_ID] = 16777228
    
  • 

    ([Presentation].[VW_STG_HISTORY].[NEW_STATE] = 'Pending')
    and
    ([Presentation].[VW_STG_HISTORY].[OLD_STATE] = 'Assigned')
    
  • 

    [ACTION_TIMESTAMP] = [Current Date Stamp-1day]
    
  • 

    [Presentation].[VW_STG_CURRENT_TEAM].[ASSIGNEE_CURR_ID] <> 0
    
• 

  

  Load Date Query

  Data Items

  1

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

• 

  

  Query-Cancelled

  Data Items

  16

  Case Number	[Presentation].[Case].[ID]
  Processing Centre	'="'+[Presentation].[Case].[PROCESSING_CENTRE]+'"'
  Age	[Presentation].[Case].[CMT_AGE]
  Date Open	#/* Replaced with New OPEN date field */# '="' + cast([Presentation].[Case].[DATE_OPEN_EST_DATE_TIME],varchar(19)) + '"'
  BF Date	#/* Replaced with New BF date field */# '="' + cast([Presentation].[Case].[DATE_BF_EST_DATE_TIME],varchar(19)) + '"'
  Work Type Name	[Presentation].[Work Type].[NAME]
  Sub Type Name	[Presentation].[Sub Type].[NAME]
  Business Unit	[Presentation].[VW_STG_DEPARTMENT].[CODE]
  Classification	[Presentation].[Case].[CLASSIFICATION]
  Assigned To	trim (both ' ',trim (both '·',[Presentation].[Case].[ASSIGNEE_CURR]))
  Group Name	#/* Replaced with Team Name field from Current Team on Case */# CASE(char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name])-21) WHEN 1 THEN 'Team '+'00'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 2 THEN 'Team '+'0'+ substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) WHEN 3 THEN 'Team '+''+substring ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name],22,char_length ([Presentation].[VW_STG_CURRENT_TEAM].[Team_Name]) ) ELSE [Presentation].[VW_STG_CURRENT_TEAM].[Team_Name] END
  Action	'5: Cancelled'
  ACTION_TIMESTAMP	#/* Replaced with New DATE_CANCELLED_EST date field */# [Presentation].[Case].[DATE_CANCELLED_EST]
  Date of Action(-4hrs)	#/* Replaced with New DATE_CANCELLED_EST date field */# [Presentation].[Case].[DATE_CANCELLED_EST]
  Current Date Stamp-1day	(_add_days ( cast(getdate(), date), - 1))	label Current Date Stamp-1
  Group	[Presentation].[VW_STG_ACCESS_CONTROL].[NAME]

  • Detail Filters
    4
  • 

    [Presentation].[Case].[DBID] <> 0
    
  • 

    [ACTION_TIMESTAMP] = [Current Date Stamp-1day]
    
  • 

    [Presentation].[State].[NAME] in ('Cancelled')
    
  • 

    [Presentation].[VW_STG_CURRENT_TEAM].[ASSIGNEE_CURR_ID] <> 0
    
• 

  

  Query-merged

  

  

  Query-Closed

  

  Query-Assigned

  

  Query-Sent-For-Verification

  

  Query-Returned-From-Verification

  

  Query-Verified

  

  Query-Rejected-By-Verifier

  

  Query-Pending

  

  Query-Cancelled

  • Projected Data Items
  • Case Number
  • Processing Centre
  • Age
  • Date Open
  • BF Date
  • Work Type Name
  • Sub Type Name
  • Business Unit
  • Classification
  • Assigned To
  • Group Name
  • Action
  • ACTION_TIMESTAMP
  • Date of Action(-4hrs)
  • Current Date Stamp-1day
  • Group

  Data Items

  13

  Case Number	[Union5].[Case Number]
  Processing Centre	[Union5].[Processing Centre]
  Age	[Union5].[Age]
  Date Open	[Union5].[Date Open]
  BF Date	[Union5].[BF Date]
  Work Type Name	[Union5].[Work Type Name]
  Sub Type Name	[Union5].[Sub Type Name]
  Business Unit	[Union5].[Business Unit]
  Classification	[Union5].[Classification]
  Assigned To	trim (both ' ',trim (both '·',[Union5].[Assigned To]))
  Team Name	[Union5].[Group Name]
  Action	[Union5].[Action]
  Group	[Union5].[Group]

Layouts