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.
Best answer by DavidSchwindtView original