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