SQL Server Reporting Services Image Source Report Options

By: Sherlee Dizon | Read Comments (10) | Print Sherlee is an IT consultant and web application developer with over 14 years of experience as a software and web developer. Related Tips: More

Problem

Companies often need to generate reports and forms from scanned images and various government supplied formats. Proper knowledge of how to incorporate images in a report is a must. I am new to SQL Server 2008 Reporting Services (SSRS) and Business Intelligence Development Studio and have been assigned to use these tools. I have used Crystal Reports as my reporting tool from my previous jobs, so I had some background in reporting, but needed to learn these new tools and features available in SSRS 2008.  In preparation for this task, I tried to create a report where I need to display an images.  When I saw the options for selecting the image source, a lot of questions came to my mind. When should I embed an image? Why should I use an external image instead of a database image source? What are the advantages and disadvantages when I use embedded, external or database as the image source? Take a look at this tip and I will share with you what I've learned.

Solution

An image is a report item that contains a reference to an image that is stored on the report server, embedded within the report, or stored in a database. An image can be a logo or picture that appears once on the report or it can be a picture that is repeated with rows of data. You can also use an image as a background for certain report items.

In SQL Server Reporting Services 2008 the image properties available are "Embedded", "External" and "Database".  Let's dig into each of these options to learn about when we should use this option as well as the associated advantages and disadvantages.

SQL Server 2008 Reporting Services Image Source : Embedded

Local report images are embedded in the report and then referenced. When you embed an image, Report Designer MIME-encodes the image and stores it as text in the report definition.

When to use Embedded as images source

• When image is embedded locally within the report.
• When you are required to store all images within the report definition.
• When image is small and will be used as indicators or hyperlinks.
• When image is small and will be used only for the specific or special report only.
• When image is small, static or does not require to be change for a long period of time.

Advantages

• One of the easiest and simplest way to display images.
• Ensures that the image is always available to the report even when the database is busy or down.
• It does not exist as separate file.

Disadvantages

• This method will not work if the total size of the images exceed the Report Server limits for the rdl file size.
• Increases the size of the report definition file.
• It cannot be shared.
• Significantly bloat the size of your report server if there are many large images embedded in different reports.
• Requires report modification when there's a need to change the image.

SQL Server 2008 Reporting Services Image Source : External

Most large sites uses the file system to store images. When you use an external image in a report, the image source is set to External and the value for the image is the URL to the image. For a report published to a report server configured for native mode, use a full or relative path. You could store the images on the file system and use the database to keep a file-pointer, which is simply the path to the location of the image on your system. This is applicable if images will be kept in a permanent location and will be shared or accessed by other reports. Use a query to fetch the location, and use that as you would for any image. You also need to set the external image property too in the code behind of your application. See the code below.

        reportViewer1.LocalReport.EnableExternalImages = true;
    

If you need to make configurable external image in SSRS 2008 use the global field ReportServerUrl to get the URL of the report server on which the report is being run dynamically (TargetServerURL). Let's say we have the image Logo.jpg in the Images folder on the Report Server, we can set the external image value in the report to the expression as shown in the code below.

        =Globals!ReportServerUrl & "?/Image/Logo.jpg"
    

After deploying the report to the Report Server, the expression will be evaluated for the image path on the Report Server.

There will be a need to do additional tweaks to migrate a server report to a local report. If the server report uses an external image located in the same folder of the report catalog, you may need to re-adjust the image path. The Report Viewer won't attempt to load the image from the current application directory by default. As a workaround, you should use the file:// protocol to load external images. See example below.

            file:///D:/MSSQLTIPWriting/2012_01/SampleReport/sherlee.jpg
    

When to use External as Images Source

• When images are stored in a File System, External File Share or Web Site.
• If the total size of the images exceed the Report Server limits for the rdl file size.
• If the images are stored on the report server or on a Web server.
• If the images are too large to store in the database.
• When the images will be used or shared on other reports.
• When you need a configurable image in a report.
• When the images change from time to time.
• When there are images uploaded from time to time.
• If images location does not change from time to time.

Advantages

• External images work well for logos and static pictures that are shared among several reports.
• It does not bloat the database server and the report server.
• Image servers can run even when the database is busy or down.
• Any changes to the image will be reflected on the report.

Disadvantages

• The image data is under the same transactional control as the rest of the data. If your server crashes, then you don't have any guarantee that the file system is in sync with the database any more. And you can't ROLLBACK a file system change.
• When the file was accidentally deleted the image will not be able to display in the report.
• Images cannot be move from different location without considering the image path stored in the database.
• You have to back up both the database and the file system where the images are stored to keep it sync and safe.

SQL Server 2008 Reporting Services Image Source : Database

You can add images that are stored in a database to your report. Such an image is known as a data-bound image. You use the same Image report item as the one used for static images, but with a set of properties that indicate that the image is stored in a database. Data-bound images can also be displayed from binary data stored in a database. Storing images in the database, normally called BLOBs (binary large object) is a usual process when you work with data.

When to use Database as Images Source

• When image is stored in a Database.
• When you specify a dataset field that is bound to a database field that contains an image.
• If your site often moves.
• If you are using SQL Server 2008 it provides the capability of storing BLOBs in the NTFS file system rather than in a database file, yet accessing it through the database. It can be useful if the only concern with using a database is performance.

Advantages

• You can store an image very easily in SQL Server and use it later.
• Having the images in the database means you only have to backup the database.
• Data and the images in the database means you only have to backup the database.
• You can migrate to different server location easily without worrying about the path or location of the images.

Disadvantages

• Significantly bloat the size of your database and may result in performance degradation.
• Storing images in the physical database bloats the database.
• Increasing backup and restore times.
• No image will appear in the report when the database is busy or down.

Conclusion

There are a few different ways how to store the images for your reports, but choosing the right option always depends on the resources your client can afford and their business needs. Using the appropriate image source still depends where the images are stored. Now we are aware that each option has advantages and disadvantages, it is just a matter choosing the best option and maximizing the resources available. Otherwise you can suggest the latest technology that offers the best solution to meet the business needs.

Next Steps

• To view instructions about how to add an image to a report, see How to: Add an Image (Reporting Services).
• To view instructions about embedding an image in a report, see How to: Embed an Image in a Report Definition (Reporting Services).
• To view instructions about working with data-bound images in a report, see How to: Add a Data-Bound Image (Reporting Services).
• Learn how to display graphics dynamically using MS SQL Server 2005 reporting services?
• Do you need to display images when the report is displayed in HTML, but not when the report is exported to a PDF format?
• Read this tip about using SQL Server 2008 FILESTREAM.
• Improve your KPI report using SQL Server 2008 R2.

Share:

Share

Tweet

Free SQL Server Learning Free webcast - Introduction to SQL Azure Demystify TempDB Performance and Manageability

Comments and Feedback:

file://D:\MSSQLTIPWriting\2012_01\SampleReport\sherlee.jpg

Post a Comment or Question

Keep it clean and stay on the subject or we may delete your comment.
Your email address is not published. Required fields are marked with an asterisk (*)

*Name

*Email

Notify for updates

Comments *** NOTE *** - If you want to include code from SQL Server Management Studio (SSMS) in your post, please copy the code from SSMS and paste the code into a text editor like NotePad before copying the code below to remove the SSMS formatting.
*Enter Code