Standard Report Queries
The standard reports included with Kofax Capture are based on the following queries. You can use these as starting points for your own custom reports.
System Status Report Query
Note that viewBatchList is a view that was added to make it easier to create a similar report with slightly different data.
SELECT
ModuleName,
BatchStatus,
COUNT(*) AS NumBatches,
SUM(ActualDocs) AS NumDocs,
SUM(ActualPages) AS NumPages
FROM
viewBatchList
GROUP BY
ModuleName, BatchStatus
Batch Summary Report Query
Note that viewStatsBatchSummary was added to make it easier to create a similar report with slightly different data.
SELECT
BatchClass,
COUNT(ExternalBatchID) AS Batches,
SUM(ProcessingSeconds) AS ProcessingSecondsTotal,
SUM(EndToEndSeconds) AS EndToEndSecondsTotal,
SUM(Documents) AS Documents,
SUM(Pages) AS Pages
FROM
viewStatsBatchSummary
WHERE
Exported > 0
AND LastSessionDateTime >= {?Report Start}
AND LastSessionDateTime <= {?Report End}
GROUP BY
BatchClass
Module Productivity Report Query
Note that viewStatsSession was added to make it easier to create a similar report with slightly different data.
SELECT
ModuleName,
COUNT(ExternalBatchID) AS Sessions,
COUNT(DISTINCT ExternalBatchID) AS Batches,
SUM(DateDiff("s", SessionStartDateTime, SessionEndDateTime)) AS ProcessingSeconds,
SUM(CompletedDocuments) AS CompletedDocuments,
SUM(KeyStrokes) AS KeyStrokes
FROM
viewStatsSession
INNER JOIN
(
SELECT BatchModuleID,
SUM(CompletedDocs) AS CompletedDocuments,
SUM(KS_Manual + KS_OCRRepair + KS_ICRRepair + KS_BCRepair + KS_OMRRepair) AS KeyStrokes
FROM StatsFormType
GROUP BY BatchModuleID
) AS d
ON viewStatsSession.BatchModuleID = d.BatchModuleID
WHERE
ModuleStartDateTime IS NOT NULL
AND SessionEndDateTime >= {?Report Start}
AND SessionEndDateTime <= {?Report End}
GROUP BY
ModuleName
ORDER BY
ModuleName
User Productivity Report Query
Note that viewStatsSession was added to make it easier to create a similar report with slightly different data.
SELECT
ModuleName, UserName,
COUNT(ExternalBatchID) AS Sessions,
COUNT(DISTINCT ExternalBatchID) AS Batches,
SUM(DateDiff("s", SessionStartDateTime, SessionEndDateTime)) AS ProcessingSeconds,
SUM(CompletedDocuments) AS CompletedDocuments,
SUM(KeyStrokes) AS KeyStrokes
FROM
viewStatsSession
INNER JOIN
(
SELECT BatchModuleID,
SUM(CompletedDocs) AS CompletedDocuments,
SUM(KS_Manual + KS_OCRRepair + KS_ICRRepair + KS_BCRepair + KS_OMRRepair) AS KeyStrokes
FROM StatsFormType
GROUP BY BatchModuleID
) AS d
ON viewStatsSession.BatchModuleID = d.BatchModuleID
WHERE
SessionEndDateTime >= {?Report Start}
AND SessionEndDateTime <= {?Report End}
AND ModuleName IS NOT NULL
GROUP BY
ModuleName, UserName
ORDER BY
ModuleName, UserName
Pages Scanned Report Query
Note that viewStatsSession was added to make it easier to create a similar report with slightly different data.
SELECT
SiteName, StationID,
SUM(PagesScanned) AS PagesScanned,
YEAR(SessionEndDateTime) AS ScanYear,
MONTH(SessionEndDateTime) AS ScanMonth,
DAY(SessionEndDateTime) AS ScanDay
FROM
viewStatsSession
WHERE
SessionEndDateTime >= {?Report Start}
AND SessionEndDateTime <= {?Report End}
AND Deleted = 0
GROUP BY
YEAR(SessionEndDateTime),
MONTH(SessionEndDateTime),
DAY(SessionEndDateTime),
SiteName, StationID
HAVING
SUM(PagesScanned)<>0
ORDER BY
YEAR(SessionEndDateTime),
MONTH(SessionEndDateTime),
SiteName,
DAY(SessionEndDateTime),
StationID