|
Including Images in a Report
|
NOTE:
The only attachments the ETL process imports into the DWH are images of the following file types
- 'bmp', 'gif', 'jpe', 'jpeg', 'jpg', 'tif', 'tiff', 'png'
All other file types (including, but not limited to DOC, CSV, PDF. TXT, XLS etc.) are not supported and will be ignored by the ETL process.
|
Linking Images to Business Records
To include images in an report, you will have to link the image records to the related business record (for example link images to Actions records).
Due to the way images are stored in SAI360, it will be necessary to link several tables to achieve this result.
The actual IMAGE is held in the table: "dw_file_data", but will be accessed via the view "rt_dbfile"
Here is the step-by-step instructions, using Actions as an example.
For other processes, replace "Action" with the name of the correct business process.
Link From View
|
Link To View/Table
|
Join Details
|
View: "rt_action"
Field: "dwi_action"
|
View: rt_file
Field: dwk_action_as_source_of_file_storage
|
all rows from "rt_action"
= Left Outer Join
|
View: rt_file
Field: dwi_file
|
View: rt_DBfile
Field: dwi_DBFile
|
return all records where the condition is met
= Inner Join
|
View: rt_DBfile
Field: dwi_DBFile
|
Table: dw_file_data
Field: filedata_hash
|
all rows from "rt_DBFile"
|
A sample SQL code for the above could look like the following:
SELECT
"rt_dbfile"."filename",
"rt_dbfile"."filedata"
FROM
"rt_action"
LEFT OUTER JOIN "rt_file" ON "rt_action"."dwi_action" = "rt_file"."dwk_action_as_source_of_file_storage"
INNER JOIN "rt_dbfile" ON "rt_file"."dwi_file" = "rt_dbfile"."dwi_dbfile"
Linking Images to Questionnaire Records
To link images to individual questions within a Questionnaire, it is necessary to incorporate the link table "dw_data_questionnaires_files"
SELECT
"dw_data_questionnaires"."dwi_data_questionnaire",
"rt_dbfile"."filedata",
"rt_dbfile"."filename"
FROM
"dw_data_questionnaires"
LEFT OUTER JOIN "dw_data_questionnaires_files"
ON "dw_data_questionnaires"."dwi_data_questionnaire" = "dw_data_questionnaires_files"."dwi_data_questionnaire"
INNER JOIN "rt_dbfile"
ON "dw_data_questionnaires_files"."dwi_dbfile" = "rt_dbfile"."dwi_dbfile"
Creating a Report with Images
In the vast majority of cases, the relationship from the Business Record to the Images Table will be a "one-to-many".
Therefore you will need to include any images as a sub-report, rather than being included in the main-record.
Step
|
Details
|
Create ALL required SQL scripts for the current report.
|
- Create the script for the Main Data Set (in our example: Incidents), and then the script to retrieve the images (see above)
|
Define the Report, and Sub-Report
|
Please refer to the sub-report section above.
|
Link the Images to the Parent Record.
|
Add an additional WHERE clause to the script which retrieves the images, making use of the parameter you set up to link the two.
In this example, you created a parameter "ActionIdentification", and therefore to link Actions and the Images, you would use the following condition:
WHERE
"rt_file"."dwk_action_as_source_of_file_storage" = ${ActionIdentification}
|
|
