D Appendix: KPI Templates

The following are a set of KPI worked examples for guidance.

KPI Worked Examples

The following worked examples provide a sample of possible KPI metrics that can be generated. The details provided correlate to selections available in the KPI Report Template records in the Admin->Reporting area.

The query Assessment Scores Latest/Completed produces a record for the Latest Completed and Next Assessment of each Assessment Type for all sites. This means for a site there will be a maximum of two records for each Assessment Type (template).

Similarly, the Audit/Visit Scores Latest/Completed produces a maximum of two records of each Audit Template for each site.

Count of Latest Completed Assessment

Query : Assessment Scores Latest/Completed

Selected Column : Site Code

Aggregate Function : Count

Using the following filters, the KPI metric provides a count of sites with the latest completed assessments of the given type with the specified score, across all sites.

Column	Operator	Type	Value	Unit	Logic
Assessment Status	Equals	Fixed	Complete	-	and
Assessment Template	Equals	Fixed	“Assessment Template”	-	and
Assessment Scoring	Equals	Fixed	“Score”	-	and

Using the following filters, the KPI metric provides a count of the latest completed assessments of all types with the specified score for a given site.

Column	Operator	Type	Value	Unit	Logic
Assessment Status	Equals	Fixed	Complete	-	and
Site Name	Equals	Fixed	“Site Name”	-	and
Assessment Scoring	Equals	Fixed	“Score”	-	and

Average Assessment Score

Query : Assessment Scores Latest/Completed

Selected Column : Assessment Overridden

Aggregate Function : Average

Using the following filters, the KPI metric provides an average assessment score of completed assessments across a range of sites. The range is determined by the Business Category in this example.

Column	Operator	Type	Value	Unit	Logic
Assessment Status	Equals	Fixed	Complete	-	and
Business Categories (Description)	Equals	Fixed	“Business Category”	-	and

Assessments Overdue

Query : Assessment Scores Latest/Completed

Selected Column : Site Code

Aggregate Function : Count

Using the following filters, the KPI metric provides a full count of the assessments overdue based on the due date being in the past. If the Aggregate Function is updated to Distinct, you will have a count of sites with one or more overdue assessments.

Column	Operator	Type	Value	Unit	Logic
Due Date	Less Than	Relative	0	Day	and
Assessment Status	Is Not Equal To	Fixed	Complete	-	and

Additional Assessment Filter Options

Additional filters can be applied to the assessment KPIs detailed above to alter the KPI metric criteria. These relate to the Assessment Scores Latest/Completed query.

These filters can be set against the KPI Report Template record and/or Report Schedule record.

Completed within last seven days

Filters to just the records with a Completed Date within the last seven days. This should be used with a filter on the Status column checking that it is set to Completed.

Column	Operator	Type	Value	Unit	Logic
Completion Date	Greater Than	Relative	-7	Day	and
Completion Date	Less than or Equal To	Relative	0	Day	and

Due date in the next seven days

Filters to just records with a Due Date in the next seven days.

Column	Operator	Type	Value	Unit	Logic
Due Date	Greater Than	Relative	0	Day	and
Due Date	Less than or Equal To	Relative	7	Day	and

For Supplier Evaluation Type

Filters to just records with the specified Supplier Evaluation Type.

Column	Operator	Type	Value	Unit	Logic
Supplier Evaluation Type	Equals	Fixed	“Supplier Evaluation Type”	-	and

Count of Latest Completed Audit

Query: Audit/Visit Scores Latest/Completed

Selected Column: Code

Aggregate Function: Count

Using the following filters, the KPI metric provides a count of the latest completed audits of the given type with the specified score across all sites.

Column	Operator	Type	Value	Unit	Logic
Status	Equals	Fixed	Completed	-	and
Audit Template	Equals	Fixed	“Audit Template”	-	and
Score	Equals	Fixed	“Score”	-	and

Using the following filters, the KPI metric provides a count of the latest completed audits of all types with the specified score for a given site.

Column	Operator	Type	Value	Unit	Logic
Status	Equals	Fixed	Completed	-	and
Site Name	Equals	Fixed	“Site Name”	-	and
Score	Equals	Fixed	“Score”	-	and

Audits Overdue

Query : Audit/Visit Scores Latest/Completed

Selected Column : Code

Aggregate Function : Count

Using the following filters, the KPI metric provides a count of sites where the next audits are overdue based one the due date being in the past.

Column	Operator	Type	Value	Unit	Logic
Status	Is Not Equal To	Fixed	Completed	-	and
Due Date	Less than	Relative	0	Day	and

Query : Audit/Visit Scores Latest/Completed

Selected Column : Site Code

Aggregate Function : Distinct

Using the following filters, the KPI metric provides a distinct count of sites where the next audits are overdue based on the due date being in the past.

Column	Operator	Type	Value	Unit	Logic
Status	Is Not Equal To	Fixed	Completed	-	and
Due Date	Less than	Relative	0	Day	and

Site Summary KPI

Query : Supplier Evaluation Site Summary Scores available

Selected Column : Site Code

Aggregate Function : Count

Using the following filters, the KPI metric provides a count of the sites that have achieved a given SE Score.

Column	Operator	Type	Value	Unit	Logic
SE Score	Equals	Fixed	“SE Score”	-	and