Spectre

Deployments

UAT_PCR_Deployment

Reports

Report Name: POD Report - Intake and Current Baseline

Overview

Queries: 6

Layouts: 1
Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model

Queries

6

Expressions:
Model: /PCR/PCR Deployment/PCR Packages/packagePCR-RCP/ name: model

Query-POD-Baseline

Data Items


POD_NO	[Presentation].[VW_CMT_POD_LOOKUP].[POD_NO]
DEPT_CODE	[Presentation].[VW_CMT_POD_LOOKUP].[DEPT_CODE]
POD Start Date	[Presentation].[VW_CMT_POD_LOOKUP].[POD_START_DT]
BASELINE_DEPT_CASE_CNT	[Presentation].[VW_CMT_POD_LOOKUP].[BASELINE_DEPT_CASE_CNT]
BASELINE_DEPT_PRI_CNT	[Presentation].[VW_CMT_POD_LOOKUP].[BASELINE_DEPT_PRI_CNT]
BASELINE_POD_PRI_CNT	[Presentation].[VW_CMT_POD_LOOKUP].[BASELINE_POD_PRI_CNT]
Date Open	coalesce ([Presentation].[Case].[DATE_BF_EST], [Presentation].[Case].[DATE_OPEN_EST])
current-date-1day	(cast(getdate ()-1,date))
Opened since Baseline	if ( [Presentation].[Case].[DATE_BF_EST] is null) then ( [Presentation].[Case].[ID]) else (null)
Caseses Open since Baseline	count( [Opened since Baseline ] for [DEPT_CODE])
BF Due	if ([Presentation].[Case].[DATE_BF_EST] is not null) then ( [Presentation].[Case].[ID]) else (null)
BFs Due since Baseline	count([BF Due] for [DEPT_CODE])
Intake	count([Presentation].[Case].[ID] for [DEPT_CODE])

Detail Filters
2
[Presentation].[Pod 0] or
[Presentation].[Pod 1] or
[Presentation].[Pod 2] or
[Presentation].[Pod 3] or
[Presentation].[Pod 4] or
[Presentation].[Pod 5] or
[Presentation].[Pod 6] or
[Presentation].[Pod 7] or
[Presentation].[Pod 8] or
[Presentation].[Pod 9] or
[Presentation].[Pod 10] or
[Presentation].[Pod 11] or
[Presentation].[Pod 12] or
[Presentation].[Pod 13] or
[Presentation].[Pod 14] or
[Presentation].[Pod 15] or
[Presentation].[Pod 16] or
[Presentation].[Pod 17] or
[Presentation].[Pod 18]
[Date Open] > [POD Start Date] and [Date Open] <= [current-date-1day]

Query-POD-Baseline-Intake-Sum

Data Items


POD_NO	[Query-POD-Baseline].[POD_NO]
DEPT_CODE	[Query-POD-Baseline].[DEPT_CODE]
POD Start Date	[Query-POD-Baseline].[POD Start Date]
BASELINE_DEPT_CASE_CNT	[Query-POD-Baseline].[BASELINE_DEPT_CASE_CNT]
BASELINE_DEPT_PRI_CNT	[Query-POD-Baseline].[BASELINE_DEPT_PRI_CNT]
BASELINE_POD_PRI_CNT	[Query-POD-Baseline].[BASELINE_POD_PRI_CNT]
Caseses Open since Baseline	[Query-POD-Baseline].[Caseses Open since Baseline]
BFs Due since Baseline	[Query-POD-Baseline].[BFs Due since Baseline]
Intake	[Query-POD-Baseline].[Intake]

Load Last Date Query

Data Items


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

Query-POD-Cases-PRIs

Data Items


POD_NO	[Presentation].[VW_CMT_POD_LOOKUP].[POD_NO]
DEPT_CODE	[Presentation].[VW_CMT_POD_LOOKUP].[DEPT_CODE]
Case Number	count([Presentation].[Case].[ID] for [DEPT_CODE])
Unique count of PRIs	[Presentation].[Case].[Unique count of PRIs]

Detail Filters
3
[Presentation].[Pod 0] or
[Presentation].[Pod 1] or
[Presentation].[Pod 2] or
[Presentation].[Pod 3] or
[Presentation].[Pod 4] or
[Presentation].[Pod 5] or
[Presentation].[Pod 6] or
[Presentation].[Pod 7] or
[Presentation].[Pod 8] or
[Presentation].[Pod 9] or
[Presentation].[Pod 10] or
[Presentation].[Pod 11] or
[Presentation].[Pod 12] or
[Presentation].[Pod 13] or
[Presentation].[Pod 14] or
[Presentation].[Pod 15] or
[Presentation].[Pod 16] or
[Presentation].[Pod 17] or
[Presentation].[Pod 18]
[Presentation].[Case].[PROCESSING_CENTRE] contains [Presentation].[VW_CMT_POD_LOOKUP].[POD_TAG]
[Presentation].[State].[NAME] in ('Assigned', 'Pending', 'Ready_to_Verify', 'Verified', 'Triage_Follow-up', 'Invalid', 'Open', 'Reassigned')

Query-POD-Cases-PRIs-sum

Data Items


POD_NO	[Query-POD-Cases-PRIs].[POD_NO]
DEPT_CODE	[Query-POD-Cases-PRIs].[DEPT_CODE]
Cases cnt	[Query-POD-Cases-PRIs].[Case Number]
PRIs cnt	[Query-POD-Cases-PRIs].[Unique count of PRIs]	Aggregate countDistinct
PRIs cnt for POD	count(distinct[Query-POD-Cases-PRIs].[Unique count of PRIs] for [Query-POD-Cases-PRIs].[POD_NO])

Query-report

[Query-POD-Baseline-Intake-Sum].[POD_NO] = [Query-POD-Cases-PRIs-sum].[POD_NO] and
[Query-POD-Baseline-Intake-Sum].[DEPT_CODE] = [Query-POD-Cases-PRIs-sum].[DEPT_CODE]

Query-POD-Baseline-Intake-Sum

1:1

Query-POD-Cases-PRIs-sum

1:1

Data Items


POD_NO	[Query-POD-Baseline-Intake-Sum].[POD_NO]
DEPT_CODE	[Query-POD-Baseline-Intake-Sum].[DEPT_CODE]
POD Start Date	[Query-POD-Baseline-Intake-Sum].[POD Start Date]
BASELINE_DEPT_CASE_CNT	[Query-POD-Baseline-Intake-Sum].[BASELINE_DEPT_CASE_CNT]
BASELINE_DEPT_PRI_CNT	[Query-POD-Baseline-Intake-Sum].[BASELINE_DEPT_PRI_CNT]
BASELINE_POD_PRI_CNT	[Query-POD-Baseline-Intake-Sum].[BASELINE_POD_PRI_CNT]
PRIs cnt for POD	[Query-POD-Cases-PRIs-sum].[PRIs cnt for POD]
Baseline-PRI POD total	[Query-POD-Baseline-Intake-Sum].[BASELINE_POD_PRI_CNT] - [Query-POD-Cases-PRIs-sum].[PRIs cnt for POD]
Caseses Open since Baseline	[Query-POD-Baseline-Intake-Sum].[Caseses Open since Baseline]
BFs Due since Baseline	[Query-POD-Baseline-Intake-Sum].[BFs Due since Baseline]
Intake	[Query-POD-Baseline-Intake-Sum].[Intake]	label Intake since baseline (up to
Cases cnt	[Query-POD-Cases-PRIs-sum].[Cases cnt]
Cases reduction	[Query-POD-Baseline-Intake-Sum].[BASELINE_DEPT_CASE_CNT] - [Query-POD-Cases-PRIs-sum].[Cases cnt]
Cases Reduction percentage	([Cases reduction] / [BASELINE_DEPT_CASE_CNT]) * 100
PRIs cnt	[Query-POD-Cases-PRIs-sum].[PRIs cnt]
PRIs reduction	[Query-POD-Baseline-Intake-Sum].[BASELINE_DEPT_PRI_CNT] - [Query-POD-Cases-PRIs-sum].[PRIs cnt]
PRIs Reduction percentage	([PRIs reduction] / [BASELINE_DEPT_PRI_CNT]) * 100
Current Date	current_date
current-date-1day	(cast(getdate ()-1,date))
Total(Cases reduction) percentage	(total([Total(Cases reduction)] for [POD_NO]) / total([Total(BASELINE_DEPT_CASE_CNT)] for [POD_NO])) *100
Total(PRIs reduction) percentage	([Baseline-PRI POD total] / [BASELINE_POD_PRI_CNT]) *100
Current Date eng	current_date
Current Date fre	current_date

Overview

Queries

Layouts