Validation › Copy of Monthly Crime Trend

Compare results

MismatchCJIAgency B onlyAccess logged

Object: Copy of Monthly Crime Trend · Source: IBM Cognos Analytics · Target: Power BI report · Parameters: Agency B, 1/1/2026 to 9/30/2026

Download evidence Audit trail Open familyRefresh

Finding: Date basis switched to reported date. (Defect seeded on purpose in this sample workspace.)

Checks

Check	Source (Cognos)	Target (Power BI)	Result
Row count	9	10	Mismatch
Total of incidents	1,791	1,789	Mismatch
Record hash	2a3a463a7aeb	d06f60ad9775	Mismatch

Access and audit

Classification: CJI
Row security: By agency AC-3
Session: MFA, CJIS-trained user IA-2(1)
Data location: Agency network SC-28
Run by: Validation service, read only AC-6
This view: Logged AU-2
Evidence: Retained with the release AU-11

CJIS Security Policy controls

In source, missing in target (1)

month	incidents
2026-09	206

Source query

SELECT substr(i.occurred_date,1,7) AS month,
       COUNT(*) AS incidents
FROM incident i
WHERE i.agency_id = 2
  AND i.occurred_date BETWEEN '2026-01-01' AND '2026-09-30'
GROUP BY substr(i.occurred_date,1,7)

Power BI dataset query

SELECT CONVERT(char(7),
       i.occurred_date, 126) AS month,
       COUNT(*) AS incidents
FROM incident i
WHERE i.agency_id = @Agency
  AND i.reported_date BETWEEN @DateFrom AND @DateTo
GROUP BY CONVERT(char(7), i.occurred_date, 126)