Validation › Monthly Crime Trend - Agency G (2024)

Compare results

Match

Object: Monthly Crime Trend - Agency G (2024) · Source: SAP BusinessObjects Web Intelligence · Target: Power BI report · Parameters: Agency G, 1/1/2024 to 12/31/2024

Download evidence Audit trail Open familyRefresh

Checks

Check	Source (Web Intelligence)	Target (Power BI)	Result
Row count	12	12	Match
Total of incidents	1,497	1,497	Match
Record hash	e093ca1b42c7	e093ca1b42c7	Match

Access and audit

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

Source query

SELECT substr(i.occurred_date,1,7) AS month,
       COUNT(*) AS incidents
FROM incident i
WHERE i.agency_id = 7
  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.occurred_date BETWEEN @DateFrom AND @DateTo
GROUP BY CONVERT(char(7), i.occurred_date, 126)