Solved

Report Attachments (primarily data lake application)

  • 13 April 2022
  • 2 replies
  • 128 views
J
Rank
Userlevel 3

Good Morning All,

 

I have been able to successfully manipulate the data structured within the analytics module to create some useful dashboards previously via custom dimensions, measures, merges, and table calculations. Less desirably, I often have to resort to external software and the data lake to write more complicated queries for data that isn’t mapped into the analytics module or complex joins that exceed the merge (left join) capabilities integral to the analytics module. 

 

One thing that has always eluded me is being able to leverage report attachments and query their contents in association to particular reports. My particular use case is searching the attachments of Motor Vehicle Theft Offense/Incident reports for specific PDF attachments that detail vehicle recovery information. Our department has a specific workflow that requires the completion of a fillable PDF for stolen vehicles from our jurisdiction that are recovered by another agency. Oftentimes, this information is not subsequently entered into structured data elements. Ideally, I would be able to search the contents of the RMS.files and RMS.file_contents tables in the data lake and join that back to specific report ID’s to glean more information related to the auto thefts so I can perform audits and supplement the structured data in the RMS. 

 

Has anyone been able to tie the RMS.files table to a specific report_id? The RMS_event_id is present, the attachment_id is present, but I am not sure what join avenue I need to take to get from the RMS.file_contents table to the RMS.files table and then subsequently a report_id field. The file_server_id is not relatable to any other table in the data lake from what I can tell.

icon

Best answer by DavidSchwindt 2 June 2022, 16:20

View original
This topic has been closed for comments

2 replies

E
Rank

Jaycee- I also have had no luck with this. I was inspired by this post and did some digging as well and I couldn’t figure out how to make a join either. This would be great for my agency because we have the exact same problem as you regarding traffic crashes and this would solve it.

DavidSchwindt
Rank
Userlevel 3
Badge

@ed_wells,

I was able to work with @Jaycee.Elliott and one of our engineers to come up with a query for this. Please try the following and simply replace {insert_dept_number_here} with your department number:

USE [DEPT_{insert_dept_number_here}]
SELECT f.original_file_name AS file_name,
    r.reporting_event_number,
               r.id,
    t.name AS link_type_name
FROM rms.attachments a
         inner join rms.reports r on a.entity_id = r.id
         inner join rms.files f on f.id = a.attachment_id
         inner join rms.link_types t on t.id = a.link_type
where a.department_id = {insert_dept_number_here} and r.department_id = {insert_dept_number_here}
      and a.entity_type = 16

 

Terms & ConditionsCookie settings