Show images from a database in Crystal Reports
Do you need to extract photos or other images from your database to show in a report? Here are the options.
By Mike Lewis
Placing a static image in a report - for example, to show a company logo in the page header - is easy. You simply select Insert Picture, choose the required file, and drop it in the report.
But what if you want to insert an image from a database? For example, you might want to show product photos, employee mug shots (as in Figure 1), or perhaps a signature to append to a letter. If the images in question are contained in a database, you can't select the images at design time. You need a way to show dynamic images in the report.
Figure 1: A report showing dynamic images.
Blobs and similar fields
How you do that depends in part on whether the database stores the image itself, or whether it holds a reference (such as path and filename) to an external image file.
If the actual image is stored in the database (in a field whose data type is Blob, Picture, Image, General, or something similar), you can treat it more or less like any other field. Just drag it from the Field Explorer and drop it in the appropriate part of the report. When you do, Design view will show the image from the first record; Preview will show the images from all the visible records, just as it does with other types of data.
When you drop the image in the report, the relevant band will resize itself if necessary to accommodate the height of the picture. If the image is larger than you want, you can crop or resize it. To resize the image, drag the resizing handles in the usual way (unlike with other software, holding down the Shift key won't force CR to maintain the aspect ratio). Alternatively, right-click on the image, select Format Graphic, and go to the Picture tab. This tab offers controls for cropping the image, and for resizing it either by a given percentage or to a specified height and width.
Although adding an image to a report in this way is perfectly straightforward, it won't be appropriate in many cases. Database designers often choose not to store the actual images in the database. They prefer to keep them in free-standing files, and to use the database to hold the paths and filenames (or perhaps URLs) of those files. That's generally a good approach, partly because it's more efficient, and partly because it makes it easier to use other software, such a photo editor, to manipulate the pictures from outside the database.
So, given a database field that contains a filename, URL or similar reference, how do you go about displaying the actual image itself in the report?
Crystal Reports XI and later
If you have CR XI or above, the following steps will display a dynamic image when the database stores a reference to a separate image file:
- Use Insert Picture to insert a static image into the report, as described above. Select any image file. Drop it in the report at the point where you want the final image to appear.
- Right-click on the image, choose Format Graphic, and go to the Picture tab. Click the conditional formatting button (the "X-2" button) adjacent to Graphic Location label.
- You are now in the formula editor. Create a formula that returns the filename or URL of the image you want to display (typically, the formula will simply return the contents of the field in the database that holds the filename or URL).
- Come out of the formula editor. Refresh the report. You should now see all the images at the appropriate points in the report.
Unfortunately, this technique won't work in earlier versions of the CR. Versions before XI don't offer a direct way of showing a picture when the database only contains the filename or URL.
Show a hyperlink
Figure 2: Instead of the image, you
can show a hyperlink to the image.
One possible alternative would be to show a hyperlink in place of the image, as in Figure 2. The person viewing the report would then click on the hyperlink to view the image. It's not an ideal solution (and of course it won't work for printed copies of the report), but it might meet your needs in some cases.
To achieve this, proceed as follows:
- Drop a text object onto the report. Set the text to something like "Click here to see photo".
- Right-click on the object , choose Format Text, and go to the Hyperlink page.
- Set the hyperlink type to "A file". Click the conditional formatting button next to the Filename box.
- In the formula editor, create a formula that returns the relevant filename or URL (again, this will typically return the contents of the relevant database field). Come out of the formula editor.
- Format the text object to make it look like a hyperlink (for example, color it blue and underline it).
Now, when the user clicks on the text object, the appropriate image will appear, either in the user's web browser (if the field contains a URL) or in their default image viewing application (if it contains a filename).
Instead of a label, you can use a field or a formula as the hyperlink. That way, you can have it show variable text, such as the name of the person whose photo you are showing, rather than something like "Click here to see photo". If that's what you want, just drop the appropriate field or formula object in the report and create the hyperlink as described above.
Overlay static objects
Another option would be to add all the pictures to the report as static images, one on top of the other, and to use conditional formatting to suppress all except the one you want to show.
Obviously, this isn't a good solution if there is a large number of possible pictures. But it might work if there is just a limited number of images. As an example, suppose you want to generate a mail merge letter. The letter is to include the signature of the person who wrote it, of which there are no more than, say, a dozen. Each person's signature is held in a separate image file. In a case like that, this solution might be appropriate.
Start by placing all the signatures in the report as static images, in the position where you want the final signature to appear. As you add each one, go to the Common tab in the Format Editor. Click the conditional formatting button next to the Suppress option, and enter a formula which will suppress the image if doesn't belong to the person who wrote the letter (you will need a field in the database, or some other mechanism, to identify that person). When you view the report, you should see the correct signature in each letter.
A third-party option
One final possibility would be to use a tool called cViewImage, from Chelsea Technologies Ltd. Essentially, this involves creating a sub report using a COM server as the data source (you don't have to know anything about COM to use this solution). You then link the sub report to the field that contains the image's filename. In fact, it's a bit more complicated than that, but the steps are clearly explained in the product's documentation.
The cViewImage tool can also be used to display some or all of the images in a given folder, even if they're not referenced from the database. It also has the ability to dynamically resize the images. The product is not free, but it's not all that expensive. A free 30-day evaluation version is available.
In this article, I've offered several suggestions for the problem of displaying dynamic images in a report. I'll finish by summarizing the main points:
- If the image is held in the database (for example, in a Blob field), you can just drag it into the report, just like any other field. That applies to all versions of Crystal Reports.
- If the image is held in a separate file, the path or URL of which is held in the database, and you have CR XI or above, add a single static image to the report, and use conditional formatting to link it to the field holding the filename.
- If the image is held in a separate file, the path or URL of which is held in the database, and you have CR 10 or below, do one of the following: (i) Show a hyperlink in the report, and have the user click the hyperlink to see the image; or (ii) Overlay all the images and suppress all except the one you need; or (iii) Use Chelsea Technologies' cViewImage product.
Adding dynamic images to a report is something that users often need to do. It's a pity that it's not always as easy as it should be. But I hope that the suggestions I've given in this article will help you out next time you are faced with this task.
Please note: The information given on this site has been carefully checked and is believed to be correct, but no legal liability can be accepted for its use. Do not use code, components or techniques unless you are satisfied that they will work correctly with your sites or applications.