Queries

• 7
  Expressions:

  

  Data Items

  

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

• 

  

  Main Query

  Data Items

  6

  User-assigned-dept	[Presentation].[VW_CMT_BOOTCAMP_USER].[dept_code]
  Head Count	[Presentation].[VW_STG_USERS].[FULLNAME]	Aggregate countDistinct Rollup countDistinct label Head Count
  Week Of Year	[Presentation].[Date Closed and Cancelled Dim].[WeekOfYear]
  Case Count	[Presentation].[Case].[ID]	Aggregate countDistinct label Case Count
  Distinct Heads	count(distinct [Presentation].[VW_STG_USERS].[FULLNAME] for [User-assigned-dept])
  Days offset	_day_of_week (current_date, 1)

  • Detail Filters
    3
  • 

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

    [Presentation].[VW_STG_USERS].[IS_ACTIVE] = 1 and [Presentation].[VW_STG_USERS].[LOGIN_NAME] in ([Presentation].[VW_CMT_BOOTCAMP_USER].[login_name])
    
  • 

    cast([Presentation].[Case].[Date Closed or Date Cancelled], date) > (_add_days ( cast(getdate(), date), (- 56 - [Days offset]) ))
    and
    cast([Presentation].[Case].[Date Closed or Date Cancelled], date) < _add_days (current_date, -1 * [Days offset])
    
• 

  

  Distinct Headcount

  Data Items

  2

  Head Count	[Presentation].[VW_STG_USERS].[FULLNAME]	Aggregate countDistinct label Head Count
  Days offset	_day_of_week (current_date, 1)

  • Detail Filters
    3
  • 

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

    [Presentation].[VW_STG_USERS].[IS_ACTIVE] = 1 and [Presentation].[VW_STG_USERS].[LOGIN_NAME] in ([Presentation].[VW_CMT_BOOTCAMP_USER].[login_name])
    
  • 

    cast([Presentation].[Case].[Date Closed or Date Cancelled], date) > (_add_days ( cast(getdate(), date), (- 56 - [Days offset]) ))
    and
    cast([Presentation].[Case].[Date Closed or Date Cancelled], date) < _add_days (current_date, -1 * [Days offset])
    
• 

  

  Load Last Date Query

  Data Items

  1

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

• 

  

  Query-Day-cnt

  Data Items

  6

  Week Of Year	[Presentation].[Date Closed and Cancelled Dim].[WeekOfYear]
  Day-cnt	case [Presentation].[Date Closed and Cancelled Dim].[WeekdayWeekend] when 'Weekday' then 1 when 'Jour de la semaine' then 1 else 0 end	Aggregate total Rollup total
  Fiscal Year	[Presentation].[Date Closed and Cancelled Dim].[FiscalYear]
  Year-Week-join	cast ([Fiscal Year], varchar(4)) || cast([Week Of Year], varchar(4))
  week of current date	_week_of_year(current_date)
  Days offset	_day_of_week (current_date, 1)

  • Detail Filters
    1
  • 

    cast( [Presentation].[Date Closed and Cancelled Dim].[FullDate], date) > (_add_days ( cast(getdate(), date), (- 56 - [Days offset]) ))
    and
    cast( [Presentation].[Date Closed and Cancelled Dim].[FullDate], date) < _add_days (current_date, -1 * [Days offset])
    
• 

  

  Query-Main-Day-cnt

  

  [Main Query].[Week Of Year] =[Query-Day-cnt].[Week Of Year]

  

  Main Query

  1:1

  

  Query-Day-cnt

  1:1

  Data Items

  7

  User-assigned-dept	[Main Query].[User-assigned-dept]
  Week Of Year	[Main Query].[Week Of Year]
  Head Count	[Main Query].[Head Count]	Aggregate total
  Case Count	[Main Query].[Case Count]	Aggregate total
  Day-cnt	[Query-Day-cnt].[Day-cnt]	Aggregate total
  Productivity	[Case Count]/[Head Count]/[Day-cnt]	Aggregate calculated
  Distinct Heads	[Main Query].[Distinct Heads]	Aggregate average

• 

  

  Crosstab

  • queryHints
  • crossProductAllowed: allow

  Data Items

  15

  User-assigned-dept	[Query-Main-Day-cnt].[User-assigned-dept]
  Week Of Year	[Query-Main-Day-cnt].[Week Of Year]
  Head Count	[Query-Main-Day-cnt].[Head Count]	Aggregate total label HC
  Case Count	[Query-Main-Day-cnt].[Case Count]	Aggregate total label C
  Day-cnt	[Query-Main-Day-cnt].[Day-cnt]	Aggregate total Rollup average label D
  Productivity	[Case Count]/[Head Count]/[Day-cnt]	Aggregate calculated Rollup calculated solveOrder 1 label Prod
  Overall F	[Case Count] / [Head Count] / [Day-cnt]	Aggregate calculated solveOrder 4
  Overall T	total([Case Count])/[Distinct Heads by Dept]/average([Day-cnt] )	Aggregate calculated solveOrder 4
  avg day count (footer)	average([Query-Main-Day-cnt].[Day-cnt])
  Day Count Total	[Query-Main-Day-cnt].[Day-cnt]	Aggregate average Rollup total
  Productivity2	total([Query-Main-Day-cnt].[Case Count]) / [Distinct Heads by Dept] / total([Query-Main-Day-cnt].[Day-cnt])	label Overall
  Average days	average(total([Query-Main-Day-cnt].[Day-cnt] for [Query-Main-Day-cnt].[User-assigned-dept]) for report)
  Distinct Heads by Dept	[Query-Main-Day-cnt].[Distinct Heads]	label H (unique)
  Headcount Total	[Distinct Headcount].[Head Count]	Aggregate average
  ProductivityT	total([Case Count] for report )/ [Headcount Total] / [Average days]

• 

  

  Chart

  Data Items

  3

  User-assigned-dept	[Query-Main-Day-cnt].[User-assigned-dept]
  Productivity	[Query-Main-Day-cnt].[Case Count]/[Query-Main-Day-cnt].[Head Count]/[Query-Main-Day-cnt].[Day-cnt]	Aggregate calculated
  Overall	round( total([Query-Main-Day-cnt].[Case Count]) / average([Query-Main-Day-cnt].[Day-cnt]) / total([Query-Main-Day-cnt].[Head Count]) ,1)

  • Detail Filters
    1
  • 

    [Query-Main-Day-cnt].[Week Of Year] = _week_of_year(current_date)-1
    

Layouts