Login / Register  search  syndication  about

          George Jensen's Blog

George Jensen on Reporting, Business Intelligence, Crystal Reports, Visual Analyzer, and more.

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-DTS\Sage_SalesLogix_Demonstration_Tools\Demo_Database\Documents\'+{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!



What's This?
Bookmark and Share

About George Jensen

   George Jensen is a Senior Developer for Customer FX Corporation.

Related Content
   Can I Bind an Attachment to a Specific Activity in Infor CRM version 7.54?
Recently I had a user ask if they could bind an attachment to a specific Activity within Infor CRM??
Posted on Jul 07, 2015 by Dale Richter to Infor CRM Questions & Answers
   Infor CRM (formerly SalesLogix) Administrator Locked Out
I thought I had seen all the issues affecting the datalink but this was a new one. We could not login t
Posted on Jun 18, 2015 by Mark Duthoy to SalesLogix Support
   How do I remove users from my Infor CRM system (formerly SalesLogix)?
I had an Administrator ask me for the steps they needed to take in order to remove a user from the Infor
Posted on May 08, 2015 by Dale Richter to Infor CRM Questions & Answers
   Infor CRM (Formerly Saleslogix) v8.1 update 03 and 05- Uncaught Error Scheduling Contact Processes
 In the Infor CRM (formerly Saleslogix) web client, under the Contact area you have the ability to s
Posted on May 07, 2015 by Kris Halsrud to Kris Halsrud's Blog
   Problem Showing All Area, Category, and Issue Values in Infor CRM (Saleslogix) Mobile And How to Fix
There is an issue with the Infor CRM (Saleslogix) Mobile client where, if you have a large list of Area,
Posted on Apr 24, 2015 by Ryan Farley to Ryan Farley's Blog

No Comments

Leave a Comment

All contents Copyright © 2015 Customer FX Corporation
Customer FX Corporation
2324 University Avenue West, Suite 115
Saint Paul, Minnesota 55114
Tel: 800.728.5783

  Follow @CustomerFX on twitter
Follow the best news, tips, and articles
  Subscribe to Customer FX on youtube
Watch SalesLogix tutorial videos from Customer FX
Login / Register