Working with the Picture Object in Crystal

We have had several inquires lately on how to dynamically change images in a report based on report data.  

For example you have pictures of products that you want to display in a report.  One way to accomplish this is to add an image with each product in a huge segmented report and have Crystal sort things out through suppression rules.  This is a valid solution but the report is going to become very unmanageable as your inventory grows and than changes.  The best case is to store the picture in a central folder and call the image into your report based on the data.

With SalesLogix you have the ability to store attachments to records which can be be documents, spreadsheets, pdf’s, or pictures.  This provides a perfect area for managing images.  But – the standard Products area in SalesLogix does not have the attachment capability.

I came up with an easy work around that will allow you to create a Product Report that displays the correct picture that you maybe able to relate to a report that you are working on.

I am using the evaluation database for a SalesLogix version 7.5.4 system.  This database is owned by a company called “Phoenix Computers, Inc.”  and comes with product records we can use for our example.

The first thing I need is some pictures of some of the products.  I searched on Google Images for photos of a some common products that are already in the database.  I found images for the  “BlackBerry”, the “Dell Latitude”, the “Dell Optiplex”, and the “MacBook Pro”.  The pictures are all stored as .jpg

I went to the Phoenix Computers, Inc account record and added the pictures to the Attachment Tab.  You could also create a dummy account for this purpose if you want but whereever you decide to put the attachment we will need the record name.

Above are the images in the attachment tab, notice the file description (Attachment column) matches the Product Name.

Now we can build the report.  Open up Crystal and make your connection to the database.  In the base report I am just going to pull in the PRODUCT.NAME and PRODUCT.FAMILY fields.  For the purpose of this report I am going to set the Record Selection formulas to {PRODUCT.NAME} in [“BlackBerry”,”Dell Latitude”,”Dell Optiplex”, “MacBook Pro”] which are our target products. 

 I am going to add two sub reports. The first is for pricing which will use the PRODUCTPROGRAM table.  I will link the sub report to the base report using the PRODUCT.PRODUCTID (base) and PRODUCTPROGRAM.PRODUCTID (sub report).  For this example I brought in the PROGRAM and PRICE fields. 

The second sub report will be the one that contains the picture.  The ATTACHMENT table is the primary table for this sub report and stores all attachment data for the entire database so to help narrow down the data I will include the ACCOUNT table. Using the ACCOUNT.ACCOUNT field we can create a record selection formula that limits the Attachments to the Account record where I am storing the images.  Here is that formula:

 {ACCOUNT.ACCOUNT} = ‘Phoenix Computers, Inc.’

The next step in this report is to make formula field that provides the path to the Images using the data in the Attachment table.  The ATTACHMENT.DESCRIPTION field gives us the name of the image as we entered and is the link we will use to match to the image to the records in the Product table.  The other field is the ATTACHMENT.FILENAME fields which provides the unique name SalesLogix gave the image when we entered it into the database.  The images are really stored in a shared foler and the path for the folder is set in the SalesLogix Administrator under the Systems/Office tab.  If you double click on your office name in the list you will get the following screen.

At the bottom you find the Attachment Folder path.  You will also need the name of the server where the folder is located.  For my example I have a server name of w2003-dts.  With this information I am able to create a formula field called “Path”.

The formula inside the field is (using my example) : ‘\w2003-DTSSage_SalesLogix_Demonstration_ToolsDemo_DatabaseDocuments’+{ATTACHMENT.FILENAME}

Next go to the Crystal Insert menu and select “Picture” from the list that appears.  Immediately a search box comes up which will allows you to search for a default image.  This default will display when the report cannot find an image at the path you provided.  I like to use a screen shot with a white background and the text of “Picture Not Available”. 

Place this picture in the details section of the sub report.  Than, suppress all other section of the report.  Here is what my “picture” sub report looks like so far.

Now, to get the correct picture to display based on the product name listed in the report, right click on the image and select “Format Graphic” from the list that appears.  Navigate to the Picture Tab and find the field labeled “Graphic” toward the bottom of the tab and select the button to the right, the Formula Workshop Editor appears.

Double click on the formula field labeled “Path”, Save and Close the editor.

Finally we need make the link to the base report.  If you remember we described each attachment with name of the product it represents.  This allows us to match the data in the sub report to the main report using PRODUCT.NAME (Base) to the ATTACHMENT.DESCRIPTION (Sub-Report). 

When I run my report I see the images are displaying next to the product information.

So, by using just one picture object we are able to call different images and match them to correct product.

Very cool.  Thanks for reading!

geo