Solved

Changing Image in report header based on parameter value

Posted on 2016-08-26
12
82 Views
Last Modified: 2016-08-29
Using Crystal Reports IX
SQL 2014

I have developed reports where there is a department logo image currently embedded in each report header.  I would like to be able to dynamically change the department logo based on a value the user enters which corresponds to the AgencyID.   Currently I either completely remove the logo image or painstakingly replace the image with another for each department.  

All departments use a version of MS SQL Server.  Some locations there may only be "One" department in the database so the default agency value is "1".  Other locations there can be multiple departments with a corresponding AgencyID.  

Example:
Single Agency Database    
AgencyID
      1      

Multiple Agency Database
AgencyID
       1
       2
       3

Can I link to a specific image?    Example: \\Image\departmentlogo1.jpg

Or do I need to store the image into the database?
0
Comment
Question by:MikeM670
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 18

Assisted Solution

by:vasto
vasto earned 125 total points
ID: 41772384
How many departments do you have ? If the number is small (2-4) you can add all images to the report and suppress all of them except the one for the current department. If you have more departments you can either load them dynamically using graphic location or pass them from the database. To set graphic location right click the image object and select menu "Format graphics", goto Picture tab ( the second one) and click the formula button for Graphic location, You can write a formula and return different path based on department ID
0
 

Author Comment

by:MikeM670
ID: 41772418
Vasto,

The second option would  have to be the way.  Some database locations  have many departments and it would be impossible to add all those images.  And since the report would be used in departments that were added after the report was written it would be a nightmare to maintain.

Would you recommend using a image field in the database that correspond to the  agencyid or using a url path?
0
 
LVL 18

Expert Comment

by:vasto
ID: 41772487
I would not recommend adding the image to the database. I know it is used widely , but it will require a BLOB field and I don't like BLOB fields. It is up to you though. The network traffic will be the same or similar. Database will give you a little bit more security, nobody will be able to change the logos without permissions, however managing the logos will be a little bit more complicate. I would place the logo in a network folder and use DepartmentID or name as a file name. The formula will just concatenate the folder name with the department id/name to return the right file.
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 

Author Comment

by:MikeM670
ID: 41772595
The simple option of adding the image to the database is probably the way I would go.  The logo's are not something that changes and I need to keep things simple and easy.  This is something that can be set and forget when the database is setup or a new agency is added to it.
0
 
LVL 101

Assisted Solution

by:mlmcc
mlmcc earned 125 total points
ID: 41772613
Try this

Add the image to the report
Right click the image
Click FORMAT IMAGE
Click the PICTURE TAB
Click GRAPHIC LOCATION

You an add a formula here or use a field from the database.

mlmcc
0
 
LVL 18

Expert Comment

by:vasto
ID: 41772979
Then just drag the database field which contains the logo to the report
0
 

Author Comment

by:MikeM670
ID: 41773182
vasto,

I'm going to try both methods for storing or retrieving the image with crystal reports now that the assistance you gave me with the other question.  So I won't close this question out right now.

Mike
0
 

Author Comment

by:MikeM670
ID: 41773266
ok I found out that you can't use a Blob in a Crystal formula.  Any idea on why they have this limitation? Would sure make things easier.

if {Agency.AgencyID} = {?Agency}
    then {Agency.Image}
else
    XXXXX

Note:   "Will use a default image for the else statement.  Either blank or the vendor's image.

I've read you can use a stored procedure to make this work so I will give that a shot.

Mike
0
 
LVL 18

Expert Comment

by:vasto
ID: 41773292
Drag the BLOB field directly to the report and add a formula to suppress if necessary
0
 
LVL 101

Expert Comment

by:mlmcc
ID: 41773462
Is the image stored in the database?

The graphic formula should return the path to the formula

mlmcc
0
 
LVL 23

Accepted Solution

by:
Ido Millet earned 250 total points
ID: 41773655
It sounds like the choice of logo actually depends on who (based on department) runs the report. In other words, each user will always want to see their own logo.

If that is the case, the solution can be very simple. Set the graphic location to a static path and file name (e.g. Dept_Logo.jpg) using local (if running local) or mapped drive (if running on a server). For example:  C:\...\Dept_Logo.jpg  or  P:\...\Dept_Logo.jpg

Obviously, each department would save a different logo under that same file name.
0
 

Author Closing Comment

by:MikeM670
ID: 41775214
Thanks everyone.  I tested this and it works properly now.  One thing I noted is when I created some test logos using a 150x150 pixel sized graphic and put some test in them to identify each department it ran perfectly.  But when I ran it based on actual logos that had been converted to 150x150 pixel size some of them displayed larger then the others thus altering the header size.  

I'll work on figuring out the graphics end of this.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers. Hopes this gives you ideas on visualizing your data in new ways ~ Create a calculated field in a query: …
Suggested Courses

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question