SPM - KPI Report

Report Name: SPM - KPI

File Name: EarthSoft.Reports.Library.48827.dll

Dependencies: Licensed SPM and SPM Schema

Software: SPM

Source: SPM install

Language: English

Output Type: Excel1

Description: The SPM - KPI (Key Performance Indicators) report can be used to track seven key performance indicators:

•Sample Collection Failed

•Field Visits Canceled

•Field Results Unsuccessful

•SRN (Sample Receipt Notification) Issues

•Test Results Delayed

•Resamples Requested

•Retests Requested

Installation Instructions: This report requires a licensed SPM and the SPM Schema applied to the database.

•For SPM usage, no additional steps are necessary.

•For Enterprise usage, this report should be published to the database. The report will be available for selection once it has been published.

•For Professional usage, this report should be published to the database or have the .dll added to the local '.../Earthsoft/EQuIS/' folder.

Overview:

The report classifies each key performance indicator measurement as Excellent, Good, Satisfactory or Poor, based on user-defined values. For example, users may specify that if only 3 field visits are canceled over a particular date range, then performance is considered to be Excellent. Additionally they may specify that 8 field visits canceled qualifies as "good", 15 field visits canceled qualifies as "satisfactory", and anything more than 15 is considered poor performance. The report can qualify key performance indicators based on whole number counts, such as 2 field visits canceled or based on percentages, such as 10% of field visits canceled.

Prior to using the report, up to three Action Levels must be configured as follows:

•Excellent: Action Level Code = KPI_EXCELLENT

•Good: Action Level Code = KPI_GOOD

•Satisfactory: Action Level Code = KPI_SATISFACTORY

The report qualifies key performance indicators based on the following ranges:

•Excellent: from 0 to KPI_EXCELLENT

•Good: from KPI_EXCELLENT to KPI_GOOD

•Satisfactory: from KPI_GOOD to KPI_SATISFACTORY

•Poor: from KPI_SATISFACTORY to the upper possible limit

Each desired key performance indicator should be added as an Action Level Parameter to each of the above Action Levels as follows:

•Sample Collection Failed: Action Level Parameter Code = SAMPLE_FAILED

•Field Visits Canceled: Action Level Parameter Code = VISIT_CANCELED

•Field Results Unsuccessful: Action Level Parameter Code = FIELD_MEASURE_NO

•SRN (Sample Receipt Notification) Issues: Action Level Parameter Code = SRN_ISSUES

•Test Results Delayed: Action Level Parameter Code = TEST_RESULT_DELAYED

•Resamples Requested: Action Level Parameter Code = RESAMPLE_REQUESTED

•Retests Requested: Action Level Parameter Code = RETEST_REQUESTED

The counts for each of the key performance indicators are determined as follows, based on the date range selected when running the report:

•Sample Collection Failed: Number of Samples where DT_SAMPLE.SAMPLE_TYPE begins with "NST" (No Sample Taken)

•Field Visits Canceled: Number of Scheduled Tasks where DT_SPM_SCHEDULED_TASK.CANCELLED_YN equals "Y"

•Field Results Unsuccessful: Number of Field Samples where DT_FIELD_SAMPLE.FLD_QUALIFIER equals a user-defined Field Qualifier (defined in the report input parameters)

•SRN Issues: Number of Samples for which SRN Issues have been reported

•Test Results Delayed: Number of Samples where DT_SRN_ISSUE_TEST.RESULT_DELAY equals "Y"

•Resamples Requested: Number of Scheduled Samples where DT_SPM_RESAMPLE.RESAMPLE_TYPE equals "SPM_RESAMPLE"

•Retests Requested: Number of Tests where DT_TEST.RETEST_APPROVED equals "Y"

Note: The values used to determine the counts above are not typically modified directly in the tables listed. The values are added to the database by SPM, EQuIS Data Gathering Engine (EDGE), the EQuIS Professional SampleTestResult Form, and various EDD Formats.

The target values for each key performance indicator should be added to the ACTION_LEVEL field. For whole number counts, the UNIT field should be left null. In the example below, the range for Sample Collection Failed is defined as 0-10 Samples Failed = Excellent, 10-50 Samples Failed = Good, 50-100 Samples Failed = Satisfactory, and 100+ Samples Failed = Poor:

ACTION_LEVEL_CODE	ACTION_LEVEL_PARAMETER	ACTION_LEVEL
KPI_EXCELLENT	SAMPLE_FAILED	10
KPI_GOOD	SAMPLE_FAILED	50
KPI_SATISFACTORY	SAMPLE_FAILED	100

For percentage counts, the UNIT field should equal "%". In the example below, the range for Field Visits Canceled is defined as 0-2% Field Visits Canceled = Excellent, 2-5% Field Visits Canceled = Good, 5-10% Field Visits Canceled = Satisfactory, and 10+% Field Visits Canceled = Poor:

ACTION_LEVEL_CODE	ACTION_LEVEL_PARAMETER	ACTION_LEVEL	UNIT
KPI_EXCELLENT	VISIT_CANCELED	2	%
KPI_GOOD	VISIT_CANCELED	5	%
KPI_SATISFACTORY	VISIT_CANCELED	10	%

Tables: AT_SAMPLE_SDG, DT_ACTION_LEVEL_PARAMETER, DT_CHAIN_OF_CUSTODY, DT_CHAIN_OF_CUSTODY_MAG, DT_CHAIN_OF_CUSTODY_MAG_MEMBER, DT_FACILITY, DT_FIELD_SAMPLE, DT_SAMPLE, DT_SDG, DT_SPM_RESAMPLE, DT_SPM_SCHEDULED_TASK, DT_SRN, DT_SRN_ISSUE, DT_SRN_ISSUE_CONTAINER, DT_SRN_ISSUE_SAMPLE, DT_SRN_ISSUE_TEST, DT_TEST, RT_ANALYTE

Report Parameters

Facility	Parameter for selecting the facility
Date Range
Start	Select a start date.
End	Select an end date.
KPI Parameter(s)	Parameter for selecting KPI Parameters(s).
Field Results Qualifier	Input box for No Field Results Qualifier.

1Excel report output requires a valid license of Microsoft Excel to view the output.