MikeM670
asked on
Changing Image in report header based on parameter value
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.jp
Or do I need to store the image into the database?
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.jp
Or do I need to store the image into the database?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
ASKER
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.
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Then just drag the database field which contains the logo to the report
ASKER
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
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
ASKER
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
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
Drag the BLOB field directly to the report and add a formula to suppress if necessary
Is the image stored in the database?
The graphic formula should return the path to the formula
mlmcc
The graphic formula should return the path to the formula
mlmcc
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
I'll work on figuring out the graphics end of this.
ASKER
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?