Placing images from your Service Desk database onto your report using Crystal 2008

Version 2

    Environment: Crystal 2008 (supplied from Service Desk 7.5)

     

    In Crystal 2008 you are able to drag image data types within your Service Desk database onto your Crystal report and have them display as images.  In this document I am going to guide you through how to create a report similar to the one below:

     

     

    create new report5.PNG

     

    • If you don't already have an image attribute in your database you will need to create one.  I am creating one on the system - user table within Object Designer in my example:

     

    create image attribute.PNG

    • Then you would need to add the image attribute onto the window that you want it to display on.  In my example this is the system - end user window within Window Manager:

     

    add image attribute on window.PNG

    • Then you need to populate the records with the images:

     

     

    set image on records.PNG

    • Due to the way in which attachments are stored in our database, next you will need to create a database view so that you will be able to join your table with the attachment data table in Crystal. 
    • In my example, the table is called tps_user and the image column that I created is called usr_image.  So when creating the view I select the tps_user table:

     

    view1.PNG

    • This is the statement within my view:  NOTE: You would need to replace tps_user and usr_image with the name of your table that you altered and the image attribute created.
    SELECT     CHARINDEX('<Guid>', usr_image) AS my_index, SUBSTRING(usr_image, CHARINDEX('<Guid>', usr_image) + 6, 36) AS my_guid, 
                          usr_image AS my_string
    FROM         dbo.tps_user
    

     

     

    view.PNG

    • Then create a new blank report in Crystal reports.  Select Create a new connection - OLEDB connection - Pick your Provider (for example Microsoft OLE DB Provider for SQL Server) and then complete your database connection details.
    • Select the table which your image attribute was created on, the view that you created, and the tps_attachment_data table:

     

    create new report.PNG

    • Link them in the following way below.  NOTE:  The way I have chosen is to use left outer joins and this means that records that don't have an image specified on them will still show on the report.  In order to change the join type on a link right-click on it and select Link Options.

    create new report3.PNG

    • Drag the required attributes onto your report.  In my example, I dragged on tps_user.tps_title, tps_user.tps_email_address, and tps_attachment_data.tps_data
    • Your report is done!