Deployments
UAT_PCR_Deployment
Reports
Report Name: Weekly Departmental CA Productivity Report
Overview

  • Queries: 11

    Layouts: 1

  • Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model
Queries
  • Queries 11
    Expressions:
  • Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model
  • Query
    All Main Query
    Data Items
    13
    User-assigned-dept
    Case #sq($[Language_lookup]{$runLocale})#
    When 'EN' then 'All Client Departments'
    else 'Tous les ministères clients'
    End
    Week Of Year
    [Presentation].[Date Closed and Cancelled Dim].[WeekOfYear]
    Case Count
    [Presentation].[Case].[ID]
    Aggregate
    countDistinct countDistinct
    label
    Case Count
    User Full Name
    [Presentation].[VW_STG_USERS].[FULLNAME]
    Days offset-Sunday
    _day_of_week (current_date, 1)
    Days offset-Saturday
    _day_of_week (current_date, 7)
    Year
    cast((_year ([Presentation].[Date Closed and Cancelled Dim].[FullDate])), varchar(4))
    Week
    cast([Presentation].[Date Closed and Cancelled Dim].[WeekOfYear], varchar(2))
    Year-Week-sort
    [Year] || [Week]
    start-date
    _add_days ( cast(current_date, date), (- 56 - [Days offset-Sunday]))
    end-date
    _add_days (current_date, - [Days offset-Saturday])
    start-date-graph
    _add_days ( cast(current_date, date), (- 7 - [Days offset-Sunday]))
    end-date-graph
    _add_days (current_date, - [Days offset-Saturday])
    • Detail Filters
      3
    • Required
      [Presentation].[State].[NAME] in ('Closed','Cancelled')
    • Required
      [Presentation].[VW_STG_USERS].[IS_ACTIVE] = 1 and [Presentation].[VW_STG_USERS].[LOGIN_NAME] in ([Presentation].[VW_CMT_BOOTCAMP_USER].[login_name])
    • Required
      cast([Presentation].[Case].[Date Closed or Date Cancelled EST], date) >= (_add_days ( cast(current_date, date), (- 56 - [Days offset-Sunday]) ))
      and
      cast([Presentation].[Case].[Date Closed or Date Cancelled EST], date) <= _add_days (current_date, -[Days offset-Saturday])
  • Query
    All Query Day cnt
    Data Items
    9
    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 total
    Rollup
    total 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-Sunday
    _day_of_week (current_date, 1)
    Days offset-Saturday
    _day_of_week (current_date, 7)
    Week Start Date
    minimum([Presentation].[Date Closed and Cancelled Dim].[FullDate] for [Week Of Year])
    Week End Date
    maximum([Presentation].[Date Closed and Cancelled Dim].[FullDate] for [Week Of Year])
    • Detail Filters
      1
    • Required
      cast( [Presentation].[Date Closed and Cancelled Dim].[FullDate], date) >= (_add_days ( cast(current_date, date), (- 56 - [Days offset-Sunday]) ))
      and
      cast( [Presentation].[Date Closed and Cancelled Dim].[FullDate], date) <= _add_days (current_date, - [Days offset-Saturday])
  • Query
    Load Last Date Query
    Data Items
    1
    Latest Valid Data Load Date
    [Presentation].[Data Load Archive].[Latest Valid Data Load Date]
  • Query
    All Query-Main-Day-cnt
    Join 
    [All Main Query].[Week Of Year] =[All Query Day cnt].[Week Of Year]
    refQuery
    All Main Query
    1:1
    refQuery
    All Query Day cnt
    1:1
    Data Items
    9
    User-assigned-dept
    [All Main Query].[User-assigned-dept]
    Week Of Year
    [All Main Query].[Week Of Year]
    Case Count
    [All Main Query].[Case Count]
    Aggregate
    total total
    Head Count
    [All Main Query].[User Full Name]
    Day-cnt
    [All Query Day cnt].[Day-cnt]
    sort-key
    2
    Year-Week-sort
    [All Main Query].[Year-Week-sort]
    Week Start Date
    [All Query Day cnt].[Week Start Date]
    Week End Date
    [All Query Day cnt].[Week End Date]
  • Query
    Query-prompt-dept
    Data Items
    1
    Departmental Name
    [Presentation].[VW_CMT_BOOTCAMP_USER].[DEPT_NAME_BI]
  • Query
    Dept Main Query
    Data Items
    9
    User-assigned-dept
    [Presentation].[VW_CMT_BOOTCAMP_USER].[Department Name]
    Week Of Year
    [Presentation].[Date Closed and Cancelled Dim].[WeekOfYear]
    User Full Name
    [Presentation].[VW_STG_USERS].[FULLNAME]
    Case Count
    [Presentation].[Case].[ID]
    Aggregate
    countDistinct countDistinct
    label
    Case Count
    Days offset-Sunday
    _day_of_week (current_date, 1)
    Days offset-Saturday
    _day_of_week (current_date, 7)
    Year
    cast((_year ([Presentation].[Date Closed and Cancelled Dim].[FullDate])), varchar(4))
    Week
    Format([Presentation].[Date Closed and Cancelled Dim].[WeekOfYear], '0#')
    Year-Week-sort
    [Year] || [Week]
    • Detail Filters
      4
    • Required
      [Presentation].[State].[NAME] in ('Closed','Cancelled')
    • Required
      [Presentation].[VW_STG_USERS].[IS_ACTIVE] = 1 and [Presentation].[VW_STG_USERS].[LOGIN_NAME] in ([Presentation].[VW_CMT_BOOTCAMP_USER].[login_name])
    • Required
      cast([Presentation].[Case].[Date Closed or Date Cancelled EST], date) >= (_add_days ( cast(current_date, date), (- 56 - [Days offset-Sunday]) ))
      and
      cast([Presentation].[Case].[Date Closed or Date Cancelled EST], date) <= _add_days (current_date, - [Days offset-Saturday])
    • Optional
      [Presentation].[VW_CMT_BOOTCAMP_USER].[DEPT_NAME_BI] in ?Param-dept?
  • Query
    Dept Query Day cnt
    Data Items
    9
    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 total
    Rollup
    total 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-Sunday
    _day_of_week (current_date, 1)
    Days offset-Saturday
    _day_of_week (current_date, 7)
    Week Start Date
    minimum([Presentation].[Date Closed and Cancelled Dim].[FullDate] for [Week Of Year])
    Week End Date
    maximum([Presentation].[Date Closed and Cancelled Dim].[FullDate] for [Week Of Year])
    • Detail Filters
      1
    • Required
      cast( [Presentation].[Date Closed and Cancelled Dim].[FullDate], date) >= (_add_days ( cast(current_date, date), (- 56 - [Days offset-Sunday]) ))
      and
      cast( [Presentation].[Date Closed and Cancelled Dim].[FullDate], date) <= _add_days (current_date, - [Days offset-Saturday])
  • Query
    Dept Query-Main-Day-cnt
    Join 
    [Dept Main Query].[Week Of Year] = [Dept Query Day cnt].[Week Of Year]
    refQuery
    Dept Main Query
    1:1
    refQuery
    Dept Query Day cnt
    1:1
    Data Items
    9
    User-assigned-dept
    [Dept Main Query].[User-assigned-dept]
    Week Of Year
    [Dept Main Query].[Week Of Year]
    Case Count
    [Dept Main Query].[Case Count]
    Aggregate
    total total
    Head Count
    [Dept Main Query].[User Full Name]
    Day-cnt
    [Dept Query Day cnt].[Day-cnt]
    sort-key
    1
    Year-Week-sort
    [Dept Main Query].[Year-Week-sort]
    Week Start Date
    [Dept Query Day cnt].[Week Start Date]
    Week End Date
    [Dept Query Day cnt].[Week End Date]
  • Query
    Crosstab
    Join
    Query Reference
    All Query-Main-Day-cnt
    Query Reference
    Dept Query-Main-Day-cnt
    • Projected Data Items
    • User-assigned-dept
    • Week Of Year
    • Case Count
    • Head Count
    • Day-cnt
    • sort-key
    • Year-Week-sort
    • Week Start Date
    • Week End Date
    Data Items
    10
    User-assigned-dept
    [Union1].[User-assigned-dept]
    Week Of Year
    [Union1].[Week Of Year]
    Case Count
    [Union1].[Case Count]
    Aggregate
    total total
    Head Count
    [Union1].[Head Count]
    Aggregate
    countDistinct countDistinct
    Rollup
    countDistinct countDistinct
    Day-cnt
    [Union1].[Day-cnt]
    Aggregate
    average average
    Rollup
    total total
    Productivity
    [Case Count] / [Head Count] / [Day-cnt]
    Aggregate
    calculated calculated
    Rollup
    calculated calculated
    solveOrder
    1
    sort-key
    [Union1].[sort-key]
    Year-Week-sort
    [Union1].[Year-Week-sort]
    Week Start Date
    [Union1].[Week Start Date]
    Week End Date
    [Union1].[Week End Date]
  • Query
    Chart-single-dept
    Join
    Query Reference
    All Query-Main-Day-cnt
    Query Reference
    Dept Query-Main-Day-cnt
    • Projected Data Items
    • User-assigned-dept
    • Week Of Year
    • Case Count
    • Head Count
    • Day-cnt
    • sort-key
    • Year-Week-sort
    • Week Start Date
    • Week End Date
    Data Items
    7
    User-assigned-dept
    [Union2].[User-assigned-dept]
    Week Of Year
    [Union2].[Week Of Year]
    Case Count
    [Union2].[Case Count]
    Aggregate
    total total
    Head Count
    [Union2].[Head Count]
    Aggregate
    countDistinct countDistinct
    Rollup
    countDistinct countDistinct
    Day-cnt
    [Union2].[Day-cnt]
    Aggregate
    average average
    Rollup
    total total
    Productivity
    [Case Count] / [Head Count] / [Day-cnt]
    Aggregate
    calculated calculated
    Rollup
    calculated calculated
    solveOrder
    1
    Overall
    round(
    [Case Count] / [Head Count] / [Day-cnt]
    ,1)
    • Detail Filters
      1
    • Required
      [Union2].[Week Of Year] = _week_of_year(current_date)-1
  • Query
    Chart-all-depts
    Join
    Query Reference
    All Query-Main-Day-cnt
    Query Reference
    Dept Query-Main-Day-cnt
    • Projected Data Items
    • User-assigned-dept
    • Week Of Year
    • Case Count
    • Head Count
    • Day-cnt
    • sort-key
    • Year-Week-sort
    • Week Start Date
    • Week End Date
    Data Items
    7
    User-assigned-dept
    [Union2].[User-assigned-dept]
    Week Of Year
    [Union2].[Week Of Year]
    Case Count
    [Union2].[Case Count]
    Aggregate
    total total
    Head Count
    [Union2].[Head Count]
    Aggregate
    countDistinct countDistinct
    Rollup
    countDistinct countDistinct
    Day-cnt
    [Union2].[Day-cnt]
    Aggregate
    average average
    Rollup
    total total
    Productivity
    [Case Count] / [Head Count] / [Day-cnt]
    Aggregate
    calculated calculated
    Rollup
    calculated calculated
    solveOrder
    1
    Overall
    round(
    [Case Count] / [Head Count] / [Day-cnt]
    ,1)
    • Detail Filters
      2
    • Required
      [Union2].[Week Of Year] = _week_of_year(current_date)-1
    • Required
      ([Union2].[User-assigned-dept] <> 'All Client Departments'
      and
      [Union2].[User-assigned-dept] <> 'Tous les ministères clients')
Layouts