Queries

• 10
  Expressions:

  

  Data Items

  

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

• 

  

  QryDataLoadDate

  Data Items

  1

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

• 

  

  QryHeadcount

  • queryHints
  • crossProductAllowed: allow

  Data Items

  5

  Start Date	[Presentation].[Open LM Sessions - Summarized].[Start Date]
  Usage Time Hours Rounded	[Presentation].[Open LM Sessions - Summarized].[Usage Time Hours Rounded]	Aggregate total
  Daily Heads on Queue	count(distinct [Presentation].[Open LM Sessions - Summarized].[User] for [Start Date])
  Previous Period Begin Date	[Previous Pay Period].[Previous Period Begin Date]
  Previous Period End Date	[Previous Pay Period].[Previous Period End Date]

  • Detail Filters
    1
  • 

    (
    ?Prompt_Option? = '1' and ([Start Date] between #prompt('ParamStartDate', 'date', 'current_date')# and #prompt('ParamEndDate', 'date', 'current_date')# )
    )
    or
    (
    ?Prompt_Option? ='0' and ?Pay Period? = cast('9999-01-01',date) and
    ([Start Date] between [Previous Period Begin Date] and [Previous Period End Date] )
    )
    or
    (
    ?Prompt_Option? ='0' and ([Start Date] between ?Pay Period? and _add_days ( ?Pay Period?,13) )
    )
    
• 

  

  QryOutput

  • queryHints
  • crossProductAllowed: allow

  Data Items

  14

  VERSION DATE	[Presentation].[Daily_Snapshot].[VER_DATE]
  Day Name Of Week Short	upper(trim(both ' ',[Presentation].[Date].[(en-zw) DayNameOfWeek_Short]))
  Day Name Of Week	[Presentation].[Date].[DayNameOfWeek]
  Count SUB TYPE TOTAL	[Presentation].[Daily_Snapshot].[COUNT_SUB_TYPE_TOTAL]	Aggregate total
  Count of BRING FORWARD	[Presentation].[Daily_Snapshot].[COUNT_BRING_FORWARD]	Aggregate total
  Count of INVALID	[Presentation].[Daily_Snapshot].[COUNT_INVALID]	Aggregate total
  Intake	[Count SUB TYPE TOTAL] - [Count of BRING FORWARD] - [Count of INVALID]
  Daily Intake	running-difference ([Intake] )
  COUNT_MANUAL_OUT	[Presentation].[Daily_Snapshot].[COUNT_MANUAL_OUT]	Aggregate total
  COUNT_NON_MANUAL_OUT	[Presentation].[Daily_Snapshot].[COUNT_NON_MANUAL_OUT]	Aggregate total
  Daily Manual Output	running-difference ([COUNT_MANUAL_OUT] )
  Daily AutoClosed Output	running-difference ([COUNT_NON_MANUAL_OUT])
  Previous Period Begin Date	[Previous Pay Period].[Previous Period Begin Date]
  Previous Period End Date	[Previous Pay Period].[Previous Period End Date]

  • Detail Filters
    2
  • 

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

    ( ?Prompt_Option? ='1' and [VERSION DATE] >= _add_days (#prompt('ParamStartDate', 'date', 'current_date')# , -1) and [VERSION DATE] <= #prompt('ParamEndDate', 'date', 'current_date')#
    )
    or
    ( ?Prompt_Option? ='0' and ?Pay Period? = cast('9999-01-01',date) and
    [VERSION DATE] >= _add_days ([Previous Period Begin Date],-1) and [VERSION DATE] <=[Previous Period End Date] )
    or
    ( ?Prompt_Option? ='0' and [VERSION DATE] >= _add_days (?Pay Period?, -1) and [VERSION DATE]<= _add_days ( ?Pay Period?,13) )
    
• 

  

  QryMain

  

  [QryOutput].[VERSION DATE] = [QryHeadcount].[Start Date]

  

  QryOutput

  1:1

  

  QryHeadcount

  0:1

  • queryHints
  • crossProductAllowed: allow

  Data Items

  32

  VERSION DATE	[QryOutput].[VERSION DATE]	label Date
  Day Name Of Week Short	[QryOutput].[Day Name Of Week Short]
  Count SUB TYPE TOTAL	[QryOutput].[Count SUB TYPE TOTAL]
  Count of BRING FORWARD	[QryOutput].[Count of BRING FORWARD]
  Count of INVALID	[QryOutput].[Count of INVALID]
  Intake	[QryOutput].[Intake]
  Daily Intake	[QryOutput].[Daily Intake]
  COUNT_MANUAL_OUT	[QryOutput].[COUNT_MANUAL_OUT]
  COUNT_NON_MANUAL_OUT	[QryOutput].[COUNT_NON_MANUAL_OUT]
  Daily Manual Output	[QryOutput].[Daily Manual Output]
  Daily AutoClosed Output	[QryOutput].[Daily AutoClosed Output ]
  Cumulative Intake	running-total ([Daily Intake])
  Daily Total Output	[QryOutput].[Daily Manual Output] + [QryOutput].[Daily AutoClosed Output ]
  Cumulative Output	running-total ([Daily Total Output])
  Daily Net Change on the Queue	[Daily Intake] -[Daily Total Output]	label Daily Net Change on the Queue
  Days	count(distinct [VERSION DATE] for report)
  Average Intake	round(average([Daily Intake] for report),0)	Rollup calculated
  Average Total Output	round(average ([Daily Total Output] for report),0)
  Daily Heads on Queue	total(distinct [QryHeadcount].[Daily Heads on Queue] for [VERSION DATE])
  Daily Prod/HC	abs ([Daily Manual Output] / [Daily Heads on Queue])
  Usage Time Hours Rounded	total(distinct [QryHeadcount].[Usage Time Hours Rounded] for [VERSION DATE])
  Prod/HC/GD Period	abs(total ([Daily Manual Output] for report) / total([Daily Heads on Queue] for report) / [Days])
  Average Prod/HC	round(average ([Daily Prod/HC] for report), 0)
  Average CMT hrs logged /GD period	[Usage Time Hours Rounded] / ([Days] * 12)
  Day of the Pay Period Cycle	running-count([VERSION DATE])
  Average Impact on Queue	round([Average Intake] - [Average Total Output],0)
  Impact on Queue	[Daily Net Change on the Queue]
  Average Daily Intake	[Average Intake]
  AverageIntake	[Average Intake]
  Average Output	[Average Total Output]
  Previous Period Begin Date	[Previous Pay Period].[Previous Period Begin Date]
  Previous Period End Date	[Previous Pay Period].[Previous Period End Date]

  • Detail Filters
    1
  • 

    (?Prompt_Option? ='0' and ?Pay Period? = cast('9999-01-01',date) and
    [VERSION DATE] >= [Previous Period Begin Date] )
    or
    ( ?Prompt_Option? ='0' and [VERSION DATE] >= ?Pay Period? )
    or
    ( ?Prompt_Option? ='1' and [VERSION DATE] >= #prompt('ParamStartDate', 'date', 'current_date')# )
    
• 

  

  Working Days Query

  • queryHints
  • crossProductAllowed: allow

  Data Items

  3

  Working Days	total(count(distinct [Presentation].[Date Closed and Cancelled Dim].[FullDate]))
  Previous Period Begin Date	[Previous Pay Period].[Previous Period Begin Date]
  Previous Period End Date	[Previous Pay Period].[Previous Period End Date]

  • Detail Filters
    2
  • 

    ( ?Prompt_Option? ='1' and
    cast(cast (_add_hours ([Presentation].[Case].[Date Closed or Date Cancelled], -4), timestamp), date) >= #prompt('ParamStartDate', 'date', 'current_date')# and cast(cast (_add_hours ([Presentation].[Case].[Date Closed or Date Cancelled], -4), timestamp), date) <#prompt('ParamEndDate', 'date', 'current_date')#
    )
    or
    ( ?Prompt_Option? ='0' and (?Pay Period? = cast('9999-01-01',date) and
    cast(cast (_add_hours ([Presentation].[Case].[Date Closed or Date Cancelled], -4), timestamp), date) >= [Previous Period Begin Date] and cast(cast (_add_hours ([Presentation].[Case].[Date Closed or Date Cancelled], -4), timestamp), date) < [Previous Period End Date]
    )
    or
    ( ?Prompt_Option? ='0' and cast(cast (_add_hours ([Presentation].[Case].[Date Closed or Date Cancelled], -4), timestamp), date) >= ?Pay Period? and cast(cast (_add_hours ([Presentation].[Case].[Date Closed or Date Cancelled], -4), timestamp), date) < _add_days ( ?Pay Period?,13) )
    )
    
  • 

    [Presentation].[Date Closed and Cancelled Dim].[WeekdayWeekend] = 'Jour de la semaine'
    or
    [Presentation].[Date Closed and Cancelled Dim].[WeekdayWeekend] = 'Weekday'
    
• 

  

  Unique Head Count Query

  • queryHints
  • crossProductAllowed: allow

  Data Items

  14

  Start Date	[Presentation].[Open LM Sessions - Summarized].[Start Date]
  Weekday Weekend	[Presentation].[Open LM Sessions - Summarized].[WeekdayWeekend]
  Unique Head Count for Report	count(distinct [Presentation].[Open LM Sessions - Summarized].[User] for report)
  Unique Head Count By Location	count(distinct [Presentation].[Open LM Sessions - Summarized].[User] for [TEAM_NAME_CENTRE])
  Unique Head Count By Day and Location	count(distinct [Presentation].[Open LM Sessions - Summarized].[User] for [Start Date], [TEAM_NAME_CENTRE])
  Location DBID	[Presentation].[Case].[Assigned Group]
  TEAM_NAME_CENTRE	[Presentation].[VW_RPT_Team].[TEAM_NAME_CENTRE]
  Full Date	cast([Presentation].[Start Date Open LM Dim].[FullDate], date)
  Day Of Year	[Presentation].[Start Date Open LM Dim].[DayOfYear]
  Date Name	[Presentation].[Start Date Open LM Dim].[DateName]
  Month Name	[Presentation].[Start Date Open LM Dim].[MonthName]
  Day Name Of Week	[Presentation].[Start Date Open LM Dim].[DayNameOfWeek]
  Previous Period Begin Date	[Previous Pay Period].[Previous Period Begin Date]
  Previous Period End Date	[Previous Pay Period].[Previous Period End Date]

  • Detail Filters
    5
  • 

    (
    ?Prompt_Option? = '1' and
    [Start Date] between #prompt('ParamStartDate', 'date', 'current_date')# and #prompt('ParamEndDate', 'date', 'current_date')#
    )
    or
    ( ?Prompt_Option? ='0' and ?Pay Period? = cast('9999-01-01',date) and
    ( [Start Date] between [Previous Period Begin Date] and [Previous Period End Date])
    )
    or
    ( ?Prompt_Option? ='0' and ([Start Date] between ?Pay Period? and _add_days ( ?Pay Period?,13) )
    )
    
  • 

    [Presentation].[VW_RPT_Team].[TEAM_NAME] not in ('Team031', 'Team032')
    
  • 

    #/* This is for Manual Output */#
    [Presentation].[Case].[Auto Process Indicator] is null
    
  • 

    [Presentation].[Work Type].[WORK_TYPE_CODE] <> 'WT-058'
    
  • 

    [Presentation].[VW_STG_USERS].[FULLNAME] is not null
    
• 

  

  Case Count and Day Count From Ticket

  • queryHints
  • crossProductAllowed: allow

  Data Items

  13

  Location DBID	[Presentation].[Case].[Assigned Group]
  Auto Process Indicator	[Presentation].[Case].[Auto Process Indicator]
  Full Date	cast([Presentation].[Date Closed and Cancelled Dim].[FullDate], date)
  Day Of Year	[Presentation].[Date Closed and Cancelled Dim].[DayOfYear]
  Date Name	[Presentation].[Date Closed and Cancelled Dim].[DateName]
  Month Name	[Presentation].[Date Closed and Cancelled Dim].[MonthName]
  Day Name Of Week	[Presentation].[Date Closed and Cancelled Dim].[DayNameOfWeek]
  Weekday Weekend or Holiday	[Presentation].[Date Closed and Cancelled Dim].[WeekdayWeekend]
  TEAM_NAME_CENTRE	[Presentation].[VW_RPT_Team].[TEAM_NAME_CENTRE]
  Case Count	[Presentation].[Case].[Case Count]	Aggregate count
  Day_Count	count(distinct(Case when ([Presentation].[Date Closed and Cancelled Dim].[WeekdayWeekend] ='Weekday' or [Presentation].[Date Closed and Cancelled Dim].[WeekdayWeekend] = 'Jour de la semaine' ) then 1 else null end) )
  Previous Period Begin Date	[Previous Pay Period].[Previous Period Begin Date]
  Previous Period End Date	[Previous Pay Period].[Previous Period End Date]

  • Detail Filters
    6
  • 

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

    [Presentation].[VW_RPT_Team].[TEAM_NAME] not in ('Team031', 'Team032')
    
  • 

    [Presentation].[VW_STG_USERS].[FULLNAME] is not null
    
  • 

    ( ?Prompt_Option? = '1' and
    cast(cast (_add_hours ([Presentation].[Case].[Date Closed or Date Cancelled], -5), timestamp), date) >= #prompt('ParamStartDate', 'date', 'current_date')# and cast(cast (_add_hours ([Presentation].[Case].[Date Closed or Date Cancelled], -5), timestamp), date) < #prompt('ParamEndDate', 'date', 'current_date')#
    )
    or
    ( ?Prompt_Option? ='0' and (?Pay Period? = cast('9999-01-01',date) and
    cast(cast (_add_hours ([Presentation].[Case].[Date Closed or Date Cancelled], -5), timestamp), date) >= [Previous Period Begin Date] and cast(cast (_add_hours ([Presentation].[Case].[Date Closed or Date Cancelled], -5), timestamp), date) < [Previous Period End Date]
    )
    or
    ( ?Prompt_Option? ='0' and cast(cast (_add_hours ([Presentation].[Case].[Date Closed or Date Cancelled], -5), timestamp), date) >= ?Pay Period? and cast(cast (_add_hours ([Presentation].[Case].[Date Closed or Date Cancelled], -5), timestamp), date) < _add_days ( ?Pay Period?,13) )
    )
    
  • 

    [Presentation].[Work Type].[WORK_TYPE_CODE] <> 'WT-058'
    
  • 

    #/* This is for Manual Output */#
    [Auto Process Indicator] is null
    
• 

  

  Main Report

  

  [Unique Head Count Query].[Start Date] = [Case Count and Day Count From Ticket].[Full Date] and
  [Unique Head Count Query].[Location DBID] = [Case Count and Day Count From Ticket].[Location DBID]

  

  Unique Head Count Query

  0:1

  

  Case Count and Day Count From Ticket

  0:1

  Data Items

  27

  Unique Head Count for all Locations	average([Unique Head Count Query].[Unique Head Count for Report] for report)
  Unique Head Count By Location	average([Unique Head Count Query].[Unique Head Count By Location] for [TEAM_NAME_CENTRE])
  Unique Head Count By Day and Location	average([Unique Head Count Query].[Unique Head Count By Day and Location] for [Full Date], [TEAM_NAME_CENTRE])
  TEAM_NAME_CENTRE	coalesce ([Case Count and Day Count From Ticket].[TEAM_NAME_CENTRE], [Unique Head Count Query].[TEAM_NAME_CENTRE])
  Full Date	coalesce ([Case Count and Day Count From Ticket].[Full Date], [Unique Head Count Query].[Start Date])
  Day Of Year	coalesce ([Case Count and Day Count From Ticket].[Day Of Year],[Unique Head Count Query].[Day Of Year])
  Date Name	coalesce ([Case Count and Day Count From Ticket].[Date Name], [Unique Head Count Query].[Date Name])
  Month Name	coalesce ([Case Count and Day Count From Ticket].[Month Name], [Unique Head Count Query].[Month Name])
  Day Name Of Week	coalesce ([Case Count and Day Count From Ticket].[Day Name Of Week], [Unique Head Count Query].[Day Name Of Week])
  Date_SD	substring([Date Name],9,2)+'-'+substring([Month Name],1,3)
  The_StartDate	minimum ([Day Of Year] for report)
  Reporting Period Date	IF ( #sq($runLocale)#='fr') THEN ( 'Jour '+ cast([Day Of Year] - [The_StartDate]+1, varchar(3)) ) ELSE ( 'Days '+ cast([Day Of Year] - [The_StartDate]+1, varchar(3)) )
  DayNameofWeek_SD	substring ([Day Name Of Week],1,3)
  Weekday Weekend or Holiday	[Case Count and Day Count From Ticket].[Weekday Weekend or Holiday]
  Case Count By Day and Location	total( [Case Count and Day Count From Ticket].[Case Count] for [Full Date], [TEAM_NAME_CENTRE])
  Day Count for Average Cal	count( distinct [Full Date])
  Daily Productivity	_round ([Case Count By Day and Location]/[Unique Head Count By Day and Location],2)	Aggregate average Rollup average solveOrder 3
  Total Case Count by Location	total( [Case Count and Day Count From Ticket].[Case Count] for [TEAM_NAME_CENTRE])
  Total Day Count By Location	total(count(distinct(Case when ( [Case Count and Day Count From Ticket].[Weekday Weekend or Holiday]='Weekday' or [Case Count and Day Count From Ticket].[Weekday Weekend or Holiday] = 'Jour de la semaine' ) then [Full Date] else null end) ) for [TEAM_NAME_CENTRE])
  Total Productivity By Location	total([Daily Productivity] for [TEAM_NAME_CENTRE])
  Average Case Count By Location	[Total Case Count by Location] / [Total Day Count By Location]	solveOrder 3
  Average Productivity By Location	[Total Productivity By Location] / [Total Day Count By Location]	solveOrder 4
  Average Productivity by Day	[Daily Productivity] /[Day Count for Average Cal]	solveOrder 3
  Productivity By Location Chart	[Total Case Count by Location]/[Unique Head Count By Location]/[Total Day Count By Location]
  Total Case Count All Locations	total( [Case Count and Day Count From Ticket].[Case Count] for report)
  Average Day Count for All Locations	total([Case Count and Day Count From Ticket].[Day_Count] for report) / count(distinct [TEAM_NAME_CENTRE] for report)
  Overall Average For All Locations	_round ([Total Case Count All Locations]/[Unique Head Count for all Locations]/[Average Day Count for All Locations],2)

• 

  

  Previous Pay Period

  Data Items

  5

  Previous Period Begin Date	_add_days (cast([Presentation].[VW_STG_PAY_PERIOD_CALENDAR].[PAY_PERIOD_START_DATE], date),-2)
  Previous Period End Date	_add_days(cast([Presentation].[VW_STG_PAY_PERIOD_CALENDAR].[PAY_PERIOD_END_DATE],date), -3)
  Global_Dashboard_Calendar_Title	IF (?Prompt_Option?='0' and ?Pay Period? = cast('9999-01-01',date) ) THEN ( cast([Previous Period Begin Date],varchar(12)) + ' - ' + cast([Previous Pay Period].[Previous Period End Date],varchar(12) ) ) ELSE ( cast(?Pay Period?,varchar(12)) + ' - ' + cast(_add_days ( ?Pay Period?,13), varchar(12)) )
  StartDate_Des	IF ( ?Prompt_Option? ='0' and ?Pay Period? = cast('9999-01-01',date) ) THEN ( cast([Previous Period Begin Date],varchar(12))) ELSE ( cast(?Pay Period?,varchar(12)) )
  EndDate_Desc	IF ( ?Prompt_Option?='0' and ?Pay Period? = cast('9999-01-01',date) ) THEN ( cast([Previous Pay Period].[Previous Period End Date],varchar(12) ) ) ELSE ( cast(_add_days ( ?Pay Period?,13), varchar(12)) )

  • Detail Filters
    2
  • 

    current_date > [Previous Period End Date]
    
  • 

    total(count([Previous Period Begin Date]) for report) = rank ([Previous Period End Date] ASC)
    
• 

  

  Global Dashboard Prompt

  Data Items

  6

  Pay Period Year	[Presentation].[VW_STG_PAY_PERIOD_CALENDAR].[PAY_PERIOD_YEAR]
  Begin Date	_add_days (cast([Presentation].[VW_STG_PAY_PERIOD_CALENDAR].[PAY_PERIOD_START_DATE], date),-2)
  End Date	_add_days(cast([Presentation].[VW_STG_PAY_PERIOD_CALENDAR].[PAY_PERIOD_END_DATE],date), -3)
  Pay Period	cast([Presentation].[VW_STG_PAY_PERIOD_CALENDAR].[PAY_PERIOD_1], integer)
  Pay Period Desc	cast([Pay Period Year], varchar(4)) + IF ( #sq($runLocale)#='fr') THEN ( ' cycle#') ELSE ( ' cycle#') + IF ( [Pay Period] < 10 ) THEN ( '0'+cast([Pay Period], varchar(3)) ) ELSE ( cast([Pay Period], varchar(3)) ) +' ( '+ cast([Begin Date ], varchar(10))+ ' - '+cast([End Date], varchar(10)) +' ) '
  PayPeriod_Value	[Pay Period Year]*100+[Pay Period]

  • Detail Filters
    1
  • 

    _add_days (current_date, -14) > [End Date]
    

Layouts