VERIFIED SOLUTION i
X

How to embed pictures or photos in a Report in Confirm

UPDATED: September 13, 2017


For all versions of Confirm® it should be possible to embed pictures from your Document Store, or those taken from a device, into your custom Confirm Reports.

In your Data Source query you would need to create your view, associating an image in your document store as a field (this is to say the SQL needs to return the image in the document store). Once you have created your Data Source, in Design mode when you are creating your Report Template, you then use the Expression/Compute button to locate where the image should be placed and associate the placeholders with the image fields in your Data Source.

The images need to be in .jpg or .png format.

When you are creating your Report Template, in Design mode, click the Expression/Compute button. Then draw a box in the visible area of your Report where you want to present the image. After this you will be presented with an Object Properties dialog for the Expression. Click the 'hour glass' icon on the Expression field and you will then be presented with an Expression dialog box. In the Expression area, you would then type:

bitmap(<photoField>)

where <photoField> is the name of your image field defined in your Data Source.

After this click Ok and the association of the image in your Document Store, to your Report, is complete.

An example SQL query is shown below:

SELECT
dbo.central_enquiry.enquiry_number,
MONTH(dbo.central_enquiry.enquiry_time) as SELECT_MONTH,
YEAR(dbo.central_enquiry.enquiry_time) as SELECT_YEAR,
dbo.central_site.site_name,
dbo.central_site.site_address,
dbo.area.area_name,
dbo.central_enquiry.enquiry_desc,
dbo.central_enquiry.enquiry_time,
dbo.enquiry_commitment.commit_target_time,
dbo.enquiry_commitment.commit_actual_time,

CASE WHEN dbo.enquiry_commitment.commit_actual_time >= dbo.enquiry_commitment.commit_target_time THEN 'OVERDUE'
ELSE '' END as Overdue,

dbo.user_login.first_name + ' ' +dbo.user_login.surname as StatLogUser,
DuringStatLog.log_effective_date as StatLogDate,
dbo.enquiry_status.enq_status_name,
dbo.action_officer.officer_name,
DuringStatLog.log_text as StatLogText,

(CASE WHEN ss_store.setting_value = 'F' THEN ss_path.setting_value+REPLACE(Picture1.document_name,'$Document Store$','')
ELSE Picture1.document_name END) as Photo1,

(CASE WHEN ss_store.setting_value = 'F' THEN ss_path.setting_value+REPLACE(Picture2.document_name,'$Document Store$','')
ELSE Picture2.document_name END) as Photo2

FROM
dbo.central_enquiry
JOIN dbo.enquiry_status_log OpenStatLog ON
dbo.central_enquiry.enquiry_number = OpenStatLog.enquiry_number AND
OpenStatLog.enquiry_log_number = 1
JOIN dbo.enquiry_status_log DuringStatLog ON
dbo.central_enquiry.enquiry_number = DuringStatLog.enquiry_number
JOIN dbo.enquiry_status ON
DuringStatLog.enq_status_code = dbo.enquiry_status.enq_status_code
JOIN dbo.action_officer ON
DuringStatLog.officer_code = dbo.action_officer.officer_code
JOIN dbo.user_login ON
DuringStatLog.login_name = dbo.user_login.login_name
JOIN dbo.central_site ON
dbo.central_enquiry.site_code = dbo.central_site.site_code
JOIN dbo.area ON
dbo.central_site.area_code = dbo.area.area_code
LEFT JOIN dbo.enquiry_commitment ON
dbo.central_enquiry.enquiry_number = dbo.enquiry_commitment.enquiry_number AND
dbo.enquiry_commitment.commit_log_number = 1
LEFT JOIN dbo.central_doc_link Picture1 ON
dbo.central_enquiry.enquiry_number = Picture1.entity_key AND
Picture1.entity_type = 'ENQUIRY' AND
Picture1.document_number = 1 AND
RIGHT(Picture1.document_name,3) IN('jpg','png')
LEFT JOIN dbo.central_doc_link Picture2 ON
dbo.central_enquiry.enquiry_number = Picture2.entity_key AND
Picture2.entity_type = 'ENQUIRY' AND
Picture2.document_number = 2 AND
RIGHT(Picture2.document_name,3) IN('jpg','png')
JOIN confirm.system_setting ss_path ON
ss_path.setting_id = 'DOCUMENT_LINK_PATH'
JOIN confirm.system_setting ss_store ON
ss_store.setting_id = 'DOCUMENT_STORE'
WHERE
(dbo.central_enquiry.subject_code = 'WEED' OR OpenStatLog.officer_code = 'LEM')


In the above example we define image fields Photo1 and Photo2, both of which need to be referenced in the Report design via the Expression/Compute button as described above. For example the associated Expression configuration is as attached.

Environment Details

Products affected: Confirm®
Product feature: Ad Hoc Reporting
 

Downloads

  • No Downloads