Sample report: Capture Statistics

Question

How many fields were verified in a Verification process step, and how long did that process take?

SELECT Client, Process,

SUM(CorrectValid) AS FieldCorrect,

SUM(SuggestionCorrect) AS FieldSuggCorr,

SUM(SuggestionFaulty) AS FieldSuggWrong,

SUM(Substitutions) AS FieldSubstitutedCorrect,

(SELECT COUNT(DISTINCT FieldID) AS Expr1

FROM V_FieldPerDocument AS Sel1

WHERE (V_StepsDocReport.ProcessID=ProcessID) AND (FieldEvaluation < 4) AND (DocCreateDateOnly

BETWEEN @StartDate AND @EndDate)) AS FieldCount, COUNT(DISTINCT DocID) AS DocCount,

SUM(CASE WHEN BatchID = DocID THEN CurrentSheetCount ELSE 0 END) AS SheetCount,

SUM(CASE WHEN BatchID = DocID THEN CurrentPageCount ELSE 0 END) AS PageCount,

(SELECT SUM(CASE WHEN BatchID = DocID THEN VerificationTimeSum / 3600 ELSE 0 END) AS Expr1 FROM V_VerificationStepsDocReport AS Sel1

WHERE (V_StepsDocReport.ProcessID = ProcessID)) AS Time,

FROM V_StepsDocReport

WHERE (Client IN (@strClient)) AND (Process IN (@strProcess)) AND ((CASE WHEN ParentID = 0 THEN (CASE WHEN ReclassifiedDocName IS NULL

THEN '<not assigned>' WHEN ReclassifiedDocName = '<only sub-document types>' THEN ' ' ELSE ReclassifiedDocName END) END) IN (@RootDocumentType) OR

(CASE WHEN ParentID <> 0 THEN (CASE WHEN ReclassifiedDocName IS NULL

THEN '<not assigned>' WHEN ReclassifiedDocName = '<only sub-document types>' THEN ' ' ELSE ReclassifiedDocName END) END) IN (@SubDocumentType))

AND (DocCreateDateOnly BETWEEN @StartDate AND @EndDate)

GROUP BY Client, ProcessID, Process

Client (@strClient)
SELECT DISTINCT Name, ID
FROM C_Client
Process (@strProcess)
SELECT DISTINCT   C_Process.Name, C_Process.ClientID, C_Process.ID, C_Client.ID AS ID_Client,
                  C_Client.Name AS ID_Name
FROM              C_Process INNER JOIN
                  C_Client ON C_Process.ClientID = C_Client.ID
WHERE             (C_Client.Name IN (@strClient))
Root document type (@RootDocumentType)
SELECT '<only sub document types>' AS RootDocumentType
UNION ALL
SELECT DISTINCT CASE WHEN ReclassifiedDocName IS NULL THEN '<not assigned>' ELSE ReclassifiedDocName END AS RootDocumentType
FROM V_StepsDocReport
WHERE (ParentID = 0) AND (Process IN (@strProcess))
Sub-document type (@SubDocumentType)
SELECT '<only root document types>' AS SubDoumentType
UNION ALL
SELECT DISTINCT CASE WHEN ReclassifiedDocName IS NULL THEN '<not assigned>' ELSE ReclassifiedDocName END AS SubDoumentType
FROM V_StepsDocReport
WHERE (ParentID <> 0) AND (Process IN (@strProcess))
Document creation date from/to
SELECT MIN(DocCreateDateOnly) AS StartDate, MAX(DocCreateDateOnly) AS EndDate
FROM V_StepsDocReport

V_StepsDocReport is derived from W_DocReport, W_Document, and V_StepsReports.
W_DocReport is populated by the XBOUND Agent for Reporting. A Capture End Point must be set in order for in order for documents to be reported here.
Why W_DocReport? The Agent for Reporting collects, processes, and saves the required values in the W_DocReport table.
Why V_StepsReports? All of a document's process steps are saved in this view. The field/document/process step connection is made here, as well as assignment to the process and client.
Verification Time refers to all of the documents which passed a Verification process step.
Doc Count includes all root documents and sub-documents that have passed Capture End and are populated by the Agent for Reporting.
Average Time refers to verification time / Doc Count.

Field label in the report	Field in the Select statement
Processed Time (Hours)	Time
Avg. Time per Document (Hours)	Time/DocCount
Root & Sub-documents	DocCount
Fields Captured (Total)	FieldCount
Fields Correct (Total)	FieldCorr
Fields Correct (%)	FieldCorr/FieldCount
Fields Suggested Correctly (Total)	FieldSuggCorr
Fields Suggested Correctly (%)	FieldSuggCorr/FieldCount
Fields Suggested Incorrectly (Total)	FieldSuggWrong
Fields Suggested Incorrectly (%)	FieldSuggWrong/FieldCount
Fields Substituted (Total)	FieldSubstituted
Fields Substituted (%)	FieldSubstituted/FieldCount