Validation › Monthly Crime Trend - Agency E (2025)
Compare results
MismatchCJIAgency E onlyAccess loggedFinding: 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,823 | 1,814 | Mismatch |
| Record hash | afe6d562c05b | 7293f4fdb877 | 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
In source, missing in target (1)
| month | incidents |
|---|---|
| 2025-12 | 157 |
Source query
SELECT substr(i.occurred_date,1,7) AS month, COUNT(*) AS incidents FROM incident i WHERE i.agency_id = 5 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)