Queries

• 27
  Expressions:

  

  Data Items

  

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

• 

  

  Current Variance

  

  [Current].[Measure Category] = [Baseline].[Measure Category]

  

  Current

  1:N

  

  Baseline

  1:1

  Data Items

  8

  Measure Category Sort	[Current].[Measure Category Sort]
  Measure Category	[Current].[Measure Category]
  Baseline Date	[Baseline].[VERSION DATE]
  Current Date	[Current].[VERSION DATE]
  Baseline	[Baseline].[Measure]	Aggregate total
  Current	[Current].[Measure]	Aggregate total
  Variance	[Current] - [Baseline]	Aggregate calculated Rollup calculated
  Change	[Variance] / [Baseline]	Aggregate calculated Rollup calculated

• 

  

  Intake

  • queryHints
  • crossProductAllowed: allow

  Data Items

  9

  Period Start Date	[Pod and Pay Dates - One Line].[Period Start]
  Pay Period End Date	[Pod and Pay Dates - One Line].[Period End]
  Open	if (/* open date in the last pay period */ [Presentation].[Case].[DATE_OPEN_EST] >= [Pod and Pay Dates - One Line].[Period Start] and [Presentation].[Case].[DATE_OPEN_EST] <= [Pod and Pay Dates - One Line].[Period End] and /* BF Date isnt populated to avoid double counting in BF calc */ [Presentation].[Case].[DATE_BF_EST] is missing ) then ( [Presentation].[Case].[Case Count] ) else ( null )	Aggregate count Rollup total
  Bring Forward	if (/* BF date in the last pay period */ [Presentation].[Case].[DATE_BF_EST] >= [Pod and Pay Dates - One Line].[Period Start] and [Presentation].[Case].[DATE_BF_EST] <= [Pod and Pay Dates - One Line].[Period End] ) then ( [Presentation].[Case].[Case Count] ) else ( null )	Aggregate count Rollup total
  Closed	if (/* date closed in the last pay period */ [Presentation].[Case].[Date Closed or Date Cancelled EST] >= [Period Start Date] and [Presentation].[Case].[Date Closed or Date Cancelled EST]<= [Pay Period End Date] and [Presentation].[State].[NAME]='Closed' ) then ( [Presentation].[Case].[Case Count] ) else ( null )	Aggregate count Rollup total
  Cancelled	if (/* date closed in the last pay period */ [Presentation].[Case].[DATE_CANCELLED_EST] >= [Period Start Date] and [Presentation].[Case].[DATE_CANCELLED_EST] <= [Pay Period End Date] ) then ( [Presentation].[Case].[Case Count] ) else ( null )	Aggregate count Rollup total
  Cases Unassigned	if ( [Presentation].[State].[NAME] in ('Open', 'Reassigned', 'Invalid') ) then ( [Presentation].[Case].[Case Count] ) else ( null )	Aggregate count Rollup total
  Cases In Progress	if (/* state is not closed */ [Presentation].[State].[NAME] not in ('Open', 'Bring_Forward', 'Cancelled', 'Closed', 'Invalid', 'Reassigned') ) then ( [Presentation].[Case].[Case Count] ) else ( null )	Aggregate count Rollup total
  Total Active Workload	count([Cases Unassigned]) + count([Cases In Progress])	Aggregate total label Active Workload

  • Detail Filters
    1
  • 

    [Presentation].[Pod EX]
    
• 

  

  Queue Age Count

  • queryHints
  • crossProductAllowed: allow

  Data Items

  3

  Age Sort	case when [Presentation].[Case].[CMT_AGE] <=9 then 1 when [Presentation].[Case].[CMT_AGE] between 10 and 20 then 2 when [Presentation].[Case].[CMT_AGE] >20 then 3 else 99 end	sort ascending
  Age	case when [Presentation].[Case].[CMT_AGE] <=9 then ' 0 - 9' when [Presentation].[Case].[CMT_AGE] between 10 and 20 then ' 10 - 20' when [Presentation].[Case].[CMT_AGE] >20 then ' +21' else cast([Presentation].[Case].[CMT_AGE], varchar(4)) end
  Case Count	[Presentation].[Case].[Case Count]	Aggregate count

  • Detail Filters
    2
  • 

    [Presentation].[Pod EX]
    
  • 

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

  

  Prior Variance

  

  [Prior].[Measure Category] = [Baseline].[Measure Category]

  

  Prior

  1:N

  

  Baseline

  1:N

  Data Items

  8

  Measure Category Sort	[Prior].[Measure Category Sort]
  Measure Category	[Prior].[Measure Category]
  Baseline Date	[Baseline].[VERSION DATE]
  Prior Date	[Prior].[VERSION DATE]
  Baseline	[Baseline].[Measure]	Aggregate total
  Prior	[Prior].[Measure]	Aggregate total
  Variance	[Prior] - [Baseline]	Aggregate calculated Rollup calculated
  Change	[Variance] / [Baseline]	Aggregate calculated Rollup calculated

• 

  

  Current

  

  

  PRI Queue Current

  

  PRI Backlog Current

  

  PRI Baseline Current

  

  Case Queue Current

  

  Case Backlog Current

  

  Case Baseline Current

  • Projected Data Items
  • VERSION DATE
  • Measure
  • Measure Category
  • Measure Category Sort

  Data Items

  4

  VERSION DATE	[Union1].[VERSION DATE]
  Measure	[Union1].[Measure]	Aggregate total
  Measure Category	[Union1].[Measure Category]
  Measure Category Sort	[Union1].[Measure Category Sort]

• 

  

  Prior

  

  

  PRI Queue Prior

  

  PRI Backlog Prior

  

  PRI Baseline Prior

  

  Case Queue Prior

  

  Case Backlog Prior

  

  Case Baseline Prior

  • Projected Data Items
  • VERSION DATE
  • Measure
  • Measure Category
  • Measure Category Sort

  Data Items

  4

  VERSION DATE	[Union3].[VERSION DATE]
  Measure	[Union3].[Measure]	Aggregate total
  Measure Category	[Union3].[Measure Category]
  Measure Category Sort	[Union3].[Measure Category Sort]

• 

  

  Baseline

  

  

  PRI Queue Baseline

  

  PRI Backlog Baseline

  

  PRI Baseline Baseline

  

  Case Queue Baseline

  

  Case Backlog Baseline

  

  Case Baseline Baseline

  • Projected Data Items
  • VERSION DATE
  • Measure
  • Measure Category

  Data Items

  3

  VERSION DATE	[Union2].[VERSION DATE]
  Measure	[Union2].[Measure]	Aggregate total
  Measure Category	[Union2].[Measure Category]

• 

  

  Pod and Pay Dates - One Line

  • queryHints
  • crossProductAllowed: allow

  Data Items

  7

  POD_NO	[Presentation].[VW_CMT_POD_LOOKUP].[POD_NO]
  POD_NAME_EN	[Presentation].[VW_CMT_POD_LOOKUP].[POD_NAME_EN]
  POD_NAME_FR	[Presentation].[VW_CMT_POD_LOOKUP].[POD_NAME_FR]
  POD_START_DT	[Presentation].[VW_CMT_POD_LOOKUP].[POD_START_DT]
  Period Start	[Pay Period Dates].[Pay Period Start Date]
  Period End	[Pay Period Dates].[Pay Period End Date]
  Prior End	cast(_add_days([Pay Period Dates].[Pay Period End Date], -14), date)

  • Detail Filters
    1
  • 

    [Presentation].[VW_CMT_POD_LOOKUP].[POD Name] in ('EX')
    
• 

  

  Pay Period Dates

  Data Items

  2

  Pay Period Start Date	cast([Presentation].[VW_STG_PAY_PERIOD_CALENDAR].[PAY_PERIOD_START_DATE], date)	Aggregate maximum
  Pay Period End Date	cast(_add_hours ( [Presentation].[VW_STG_PAY_PERIOD_CALENDAR].[PAY_PERIOD_END_DATE], -5 ), date)	Aggregate maximum sort descending

  • Detail Filters
    1
  • 

    [Pay Period End Date] < current_date
    
• 

  

  PRI Queue Current

  • queryHints
  • crossProductAllowed: allow

  Data Items

  4

  VERSION DATE	cast( [Pod and Pay Dates - One Line].[Period End], date)
  Measure	[Presentation].[Case].[PRI]	Aggregate countDistinct Rollup summarize
  Measure Category	if ( #sq($runLocale)# <>'fr' ) then ( 'EX Queue (affected PRIs)' ) else ( 'File d’attente du groupe EX (CIDP touchés)' )
  Measure Category Sort	2

  • Detail Filters
    2
  • 

    [Presentation].[Pod EX]
    
  • 

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

  

  PRI Backlog Current

  • queryHints
  • crossProductAllowed: allow

  Data Items

  4

  VERSION DATE	[Pod and Pay Dates - One Line].[Period End]
  Measure	[Presentation].[Case].[PRI]	Aggregate countDistinct Rollup summarize
  Measure Category	if ( #sq($runLocale)# <>'fr' ) then ( 'EX Backlog (affected PRIs)' ) else ( 'Arriéré du groupe EX (CIDP touchés)' )
  Measure Category Sort	4

  • Detail Filters
    2
  • 

    [Presentation].[Pod EX]
    
  • 

    [Presentation].[State].[NAME] not in ('Bring_Forward', 'Cancelled', 'Closed')
    and
    (
    [Presentation].[Case].[CMT_AGE] >cast([Presentation].[Sub Type].[SERVICE_STANDARD], integer)
    or
    [Presentation].[Case].[PROCESSING_CENTRE] contains 'S214'
    )
    
• 

  

  PRI Baseline Current

  • queryHints
  • crossProductAllowed: allow

  Data Items

  4

  VERSION DATE	[Pod and Pay Dates - One Line].[Period End]
  Measure	[Presentation].[Case].[PRI]	Aggregate countDistinct Rollup summarize
  Measure Category	if ( #sq($runLocale)# <>'fr' ) then ( 'EX Baseline (affected PRIs)' ) else ( 'Référence du groupe EX (CIDP touchés)' )
  Measure Category Sort	6

  • Detail Filters
    2
  • 

    [Presentation].[Pod EX]
    
  • 

    [Presentation].[State].[NAME] not in ('Bring_Forward', 'Cancelled', 'Closed')
    and
    [Presentation].[Case].[PROCESSING_CENTRE] contains 'S214'
    
• 

  

  PRI Queue Prior

  • queryHints
  • crossProductAllowed: allow

  Data Items

  4

  VERSION DATE	_add_days([Pod and Pay Dates - One Line].[Period Start], -1)
  Measure	[Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[PRI]	Aggregate countDistinct Rollup summarize
  Measure Category	if ( #sq($runLocale)# <>'fr' ) then ( 'EX Queue (affected PRIs)' ) else ( 'File d’attente du groupe EX (CIDP touchés)' )
  Measure Category Sort	2

  • Detail Filters
    2
  • 

    [Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[sd_NAME] not in ('Bring_Forward', 'Cancelled', 'Closed')
    
  • 

    [Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[EX_IND] = 1
    
• 

  

  PRI Backlog Prior

  • queryHints
  • crossProductAllowed: allow

  Data Items

  4

  VERSION DATE	_add_days([Pod and Pay Dates - One Line].[Period Start], -1)
  Measure	[Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[PRI]	Aggregate countDistinct Rollup summarize
  Measure Category	if ( #sq($runLocale)# <>'fr' ) then ( 'EX Backlog (affected PRIs)' ) else ( 'Arriéré du groupe EX (CIDP touchés)' )
  Measure Category Sort	4

  • Detail Filters
    2
  • 

    [Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[EX_IND] = 1
    
  • 

    [Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[sd_NAME] not in ('Bring_Forward', 'Cancelled', 'Closed')
    and
    (
    [Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[CMT_AGE] >cast( [Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[lt_VALUE_EN], integer)
    or
    [Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[PROCESSING_CENTRE] contains 'S214'
    )
    
• 

  

  PRI Baseline Prior

  • queryHints
  • crossProductAllowed: allow

  Data Items

  4

  VERSION DATE	_add_days([Pod and Pay Dates - One Line].[Period Start], -1)
  Measure	[Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[PRI]	Aggregate countDistinct Rollup summarize
  Measure Category	if ( #sq($runLocale)# <>'fr' ) then ( 'EX Baseline (affected PRIs)' ) else ( 'Référence du groupe EX (CIDP touchés)' )
  Measure Category Sort	6

  • Detail Filters
    2
  • 

    [Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[EX_IND] = 1
    
  • 

    [Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[sd_NAME] not in ('Bring_Forward', 'Cancelled', 'Closed')
    and
    [Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[PROCESSING_CENTRE] contains 'S214'
    
• 

  

  PRI Queue Baseline

  • queryHints
  • crossProductAllowed: allow

  Data Items

  3

  VERSION DATE	[Pod and Pay Dates - One Line].[POD_START_DT]
  Measure	[Presentation].[VW_CMT_POD_LOOKUP].[BASELINE_POD_PRI_CNT]	Rollup summarize
  Measure Category	if ( #sq($runLocale)# <>'fr' ) then ( 'EX Queue (affected PRIs)' ) else ( 'File d’attente du groupe EX (CIDP touchés)' )

  • Detail Filters
    1
  • 

    [Presentation].[VW_CMT_POD_LOOKUP].[POD Name] = 'EX'
    
• 

  

  PRI Backlog Baseline

  • queryHints
  • crossProductAllowed: allow

  Data Items

  3

  VERSION DATE	[Pod and Pay Dates - One Line].[POD_START_DT]
  Measure	[Presentation].[VW_CMT_POD_LOOKUP].[BASELINE_POD_PRI_CNT]	Rollup summarize
  Measure Category	if ( #sq($runLocale)# <>'fr' ) then ( 'EX Backlog (affected PRIs)' ) else ( 'Arriéré du groupe EX (CIDP touchés)' )

  • Detail Filters
    1
  • 

    [Presentation].[VW_CMT_POD_LOOKUP].[POD Name] = 'EX'
    
• 

  

  PRI Baseline Baseline

  • queryHints
  • crossProductAllowed: allow

  Data Items

  3

  VERSION DATE	[Pod and Pay Dates - One Line].[POD_START_DT]
  Measure	[Presentation].[VW_CMT_POD_LOOKUP].[BASELINE_POD_PRI_CNT]	Rollup summarize
  Measure Category	if ( #sq($runLocale)# <>'fr' ) then ( 'EX Baseline (affected PRIs)' ) else ( 'Référence du groupe EX (CIDP touchés)' )

  • Detail Filters
    1
  • 

    [Presentation].[VW_CMT_POD_LOOKUP].[POD Name] = 'EX'
    
• 

  

  Case Queue Current

  • queryHints
  • crossProductAllowed: allow

  Data Items

  4

  VERSION DATE	cast( [Pod and Pay Dates - One Line].[Period End], date)
  Measure	[Presentation].[Case].[ID]	Aggregate count Rollup total
  Measure Category	if ( #sq($runLocale)# <>'fr' ) then ( 'EX Queue (# cases)' ) else ( 'File d’attente du groupe EX (cas)' )
  Measure Category Sort	1

  • Detail Filters
    2
  • 

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

    [Presentation].[Pod EX]
    
• 

  

  Case Backlog Current

  • queryHints
  • crossProductAllowed: allow

  Data Items

  4

  VERSION DATE	[Pod and Pay Dates - One Line].[Period End]
  Measure	[Presentation].[Case].[ID]	Aggregate count Rollup total
  Measure Category	if ( #sq($runLocale)# <>'fr' ) then ( 'EX Backlog (# cases)' ) else ( 'Arriéré du groupe EX (cas)' )
  Measure Category Sort	3

  • Detail Filters
    2
  • 

    [Presentation].[State].[NAME] not in ('Bring_Forward', 'Cancelled', 'Closed')
    and
    (
    [Presentation].[Case].[CMT_AGE] >cast( [Presentation].[Sub Type].[SERVICE_STANDARD], integer)
    or
    [Presentation].[Case].[PROCESSING_CENTRE] contains 'S214'
    )
    
  • 

    [Presentation].[Pod EX]
    
• 

  

  Case Baseline Current

  • queryHints
  • crossProductAllowed: allow

  Data Items

  4

  VERSION DATE	[Pod and Pay Dates - One Line].[Period End]
  Measure	[Presentation].[Case].[ID]	Aggregate count Rollup total
  Measure Category	if ( #sq($runLocale)# <>'fr' ) then ( 'EX Baseline (# cases)' ) else ( 'Référence du groupe EX (cas)' )
  Measure Category Sort	5

  • Detail Filters
    2
  • 

    [Presentation].[State].[NAME] not in ('Bring_Forward', 'Cancelled', 'Closed')
    and
    [Presentation].[Case].[PROCESSING_CENTRE] contains 'S214'
    
  • 

    [Presentation].[Pod EX]
    
• 

  

  Case Queue Baseline

  • queryHints
  • crossProductAllowed: allow

  Data Items

  3

  VERSION DATE	[Presentation].[VW_CMT_POD_LOOKUP].[POD_START_DT]
  Measure	[Presentation].[VW_CMT_POD_LOOKUP].[BASELINE_POD_CASE_CNT]	Rollup total
  Measure Category	if ( #sq($runLocale)# <>'fr' ) then ( 'EX Queue (# cases)' ) else ( 'File d’attente du groupe EX (cas)' )

  • Detail Filters
    1
  • 

    [Presentation].[VW_CMT_POD_LOOKUP].[POD Name] = 'EX'
    
• 

  

  Case Backlog Baseline

  • queryHints
  • crossProductAllowed: allow

  Data Items

  3

  VERSION DATE	[Presentation].[VW_CMT_POD_LOOKUP].[POD_START_DT]
  Measure	[Presentation].[VW_CMT_POD_LOOKUP].[BASELINE_POD_CASE_CNT]	Rollup total
  Measure Category	if ( #sq($runLocale)# <>'fr' ) then ( 'EX Backlog (# cases)' ) else ('Arriéré du groupe EX (cas)')

  • Detail Filters
    1
  • 

    [Presentation].[VW_CMT_POD_LOOKUP].[POD Name] = 'EX'
    
• 

  

  Case Baseline Baseline

  • queryHints
  • crossProductAllowed: allow

  Data Items

  3

  VERSION DATE	[Presentation].[VW_CMT_POD_LOOKUP].[POD_START_DT]
  Measure	[Presentation].[VW_CMT_POD_LOOKUP].[BASELINE_POD_CASE_CNT]	Rollup total
  Measure Category	if ( #sq($runLocale)# <>'fr' ) then ( 'EX Baseline (# cases)' ) else ('Référence du groupe EX (cas)')

  • Detail Filters
    1
  • 

    [Presentation].[VW_CMT_POD_LOOKUP].[POD Name] = 'EX'
    
• 

  

  Case Queue Prior

  • queryHints
  • crossProductAllowed: allow

  Data Items

  4

  VERSION DATE	_add_days([Pod and Pay Dates - One Line].[Period Start], -1)
  Measure	[Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[ID]	Aggregate count Rollup total
  Measure Category	if ( #sq($runLocale)# <>'fr' ) then ( 'EX Queue (# cases)' ) else ( 'File d’attente du groupe EX (cas)' )
  Measure Category Sort	1

  • Detail Filters
    2
  • 

    [Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[EX_IND] = 1
    
  • 

    [Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[sd_NAME] not in ('Bring_Forward', 'Cancelled', 'Closed')
    
• 

  

  Case Backlog Prior

  • queryHints
  • crossProductAllowed: allow

  Data Items

  4

  VERSION DATE	_add_days([Pod and Pay Dates - One Line].[Period Start], -1)
  Measure	[Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[ID]	Aggregate count Rollup total
  Measure Category	if ( #sq($runLocale)# <>'fr' ) then ( 'EX Backlog (# cases)' ) else ( 'Arriéré du groupe EX (cas)' )
  Measure Category Sort	3

  • Detail Filters
    2
  • 

    [Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[EX_IND] = 1
    
  • 

    [Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[sd_NAME] not in ('Bring_Forward', 'Cancelled', 'Closed')
    and
    (
    [Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[CMT_AGE] >cast([Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[lt_VALUE_EN], integer)
    or
    [Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[PROCESSING_CENTRE] contains 'S214'
    )
    
• 

  

  Case Baseline Prior

  • queryHints
  • crossProductAllowed: allow

  Data Items

  4

  VERSION DATE	_add_days([Pod and Pay Dates - One Line].[Period Start], -1)
  Measure	[Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[ID]	Aggregate count Rollup total
  Measure Category	if ( #sq($runLocale)# <>'fr' ) then ( 'EX Baseline (# cases)' ) else ( 'Référence du groupe EX (cas)' )
  Measure Category Sort	5

  • Detail Filters
    2
  • 

    [Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[EX_IND] = 1
    
  • 

    [Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[sd_NAME] not in ('Bring_Forward', 'Cancelled', 'Closed')
    and
    (
    [Presentation].[VW_CMT_LAST_PAY_PERIOD_TICKET].[PROCESSING_CENTRE] contains 'S214'
    )
    

Layouts