Validation › Monthly Crime Trend - Agency H (2025)

Compare results

MismatchCJIAgency H onlyAccess logged

Object: Monthly Crime Trend - Agency H (2025) · Source: IBM Cognos Analytics · Target: Power BI report · Parameters: Agency H, 1/1/2025 to 12/31/2025

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	12	13	Mismatch
Total of incidents	1,519	1,521	Mismatch
Record hash	0c7ce6b2e1e6	1f29511d4b1b	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
2025-12	120

Source query

SELECT substr(i.occurred_date,1,7) AS month,
       COUNT(*) AS incidents
FROM incident i
WHERE i.agency_id = 8
  AND i.occurred_date BETWEEN '2025-01-01' AND '2025-12-31'
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)