Validation › Monthly Crime Trend - Agency D (2024)
Compare results
MismatchCJIAgency D 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 | 12 | Match |
| Total of incidents | 1,955 | 1,949 | Mismatch |
| Record hash | 0e43cabc7c46 | b595477532cb | 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 |
|---|---|
| 2024-12 | 167 |
Source query
SELECT substr(i.occurred_date,1,7) AS month, COUNT(*) AS incidents FROM incident i WHERE i.agency_id = 4 AND i.occurred_date BETWEEN '2024-01-01' AND '2024-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)