SQL Server Reporting Services Image Source Report Options

By:   |   Updated: 2012-02-21   |   Comments (24)   |   Related: > Reporting Services Development


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


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Sherlee Dizon Sherlee Dizon is an IT consultant and web application developer with over 14 years of experience as a software and web developer.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips


Article Last Updated: 2012-02-21

Comments For This Article




Tuesday, November 22, 2016 - 5:21:00 AM - Ramesh Korada Back To Top (43825)

Requirement : I need to display an image in SSRS report, we have written some code in dll and in that we are retutning byte array/Image/base64 (tried all the ways) and referenced that dll in the report and called that function  source as external and wrote the code in report properties >> in code tab.

tried serval ways to load the image still failed to display, please help me out on this.

 

Friday, March 4, 2016 - 4:17:11 AM - Ernest Sarmiento Back To Top (40859)

Hi Everyone,

I tried to add an image in ssrs report for crm online 2015, using a data-bound image. When i tried it on visual studio the image is showing correctly but when i tried to run the report in crm "Run Report" ribbon button, the image is not showing. Any help guys? Thanks in advance.

Friday, August 16, 2013 - 3:39:36 AM - Sherlee Back To Top (26342)

 

Yes this is possible. Your report output should be in a pdf file so that it can be viewable and printable in any types of android tablets.

Wednesday, August 14, 2013 - 4:29:57 PM - Sommmmmm Back To Top (26311)

 

I have an app which takes pictures of various shleves and other items. I want to create reports basd on the type and feet of the various different type of shevles and inventory on a tablet using SQL server. is it possisble.

Wednesday, June 26, 2013 - 8:30:34 PM - Brian Jacobs Back To Top (25586)

Hi, I have a report under a subscription that is e-mailed at the 1st of the month.  The Report was created using Visual Studio 2010.  On the report is an embedded graphic (LOGO, SIZE: 0.75 x 0.75), MIME type is not set in the properties and the graphic is positioned in the Header of the report.  The report is rendered in MHTML from the Report Server.  When the report is sent, the graphic expands to the full extent of the Header of the report, shifting all the header material instead of retaining the size it has been defined with in its properties.  What am I doing wrong and how can I correct this?  Thank you for your answer in advance.

Tuesday, June 11, 2013 - 9:52:26 AM - Jazzmine Back To Top (25391)

Where does this code get placed in the code section of the report?

reportViewer1.LocalReport.EnableExternalImages = true;
   

 

Monday, May 6, 2013 - 10:17:26 PM - Sherlee Back To Top (23750)

 

Hello Vinay ahire,

Based on your provided link it seems that it uses external image source.

http://frntadb001/Reports/Pages/Resource.aspx is the page where the image will be displayed.

?ItemPath is the name of the parameter for the image path.

%2fVPMReports is the folder or storage where the image resides.

%2fClientLogo.jpg is the name of the image.

 

Hope this helps.

 

 

 

Friday, May 3, 2013 - 9:06:21 AM - vinay ahire Back To Top (23702)

Hi ,

I have following image URL in SSRS.How can i find which method is used here?

 

http://frntadb001/Reports/Pages/Resource.aspx?ItemPath=%2fVPMReports%2fClientLogo.jpg

Sunday, March 3, 2013 - 10:59:16 PM - Sherlee Back To Top (22541)

 

Hello Farman,

May I know what version of SQL Server Business Intelligence Development Studio and MSSQL Version you are using.

 

Friday, March 1, 2013 - 3:19:23 PM - Farman Back To Top (22518)

Hi There,

Thanks, for the article, I am having a similar situation where in I store the path in the DB like "C:\Users\fsb\Images\TRISHProduct.jpg" the way the ERP system recommends and also I pull the picture in my ERP with the same path.

 

What I would like to do is use the same path to display the image in the SSRS reports, if you could please let me know how can this be achieved that would be great. and I store all the images in the shared folder only the path is stored in the table.

 

Thanks,

 

FS

Wednesday, February 20, 2013 - 5:53:56 AM - Sherlee Back To Top (22297)

 

Thanks for Michael Blake for reading my article.

Saturday, December 8, 2012 - 1:32:50 AM - Michael Blake Back To Top (20819)

Great article. I do not have SSRS or Report Builder available to me, so I am hand-coding a Report Generator tool. I have yet to cover the SubReport ReportItem, but I do have an embedded image in my PageHeader element. I put it in a Rectangle. (Images themselves do not appear to be disallowed, either.) The documentation states that SubReports are not allowed, however as a ReportItem in a Rectangle, it works fine for me. Naturally, it is not necessary for me to embed it, but I needed to test my code, so I initially used External. Now, I will work on Database....

 

Anyway, I thought the article was well done and informative.Thanks.

Friday, October 26, 2012 - 5:44:03 AM - Smitha Back To Top (20099)

Very nice article

Wednesday, August 29, 2012 - 6:46:58 AM - Anon Back To Top (19268)

Thanks for the help!!!!!

Monday, February 27, 2012 - 2:46:26 PM - Lori Back To Top (16177)

Thank you. Very good article!

Thursday, February 23, 2012 - 2:21:32 PM - Marco Polo Back To Top (16144)

Sherlee,

Your sugestion about put the image in a sub-report and use it as a report header has a problem.

The reporting services does not allow put a sub report inside a page header area.

This is a greate article.

 

Wednesday, February 22, 2012 - 11:28:12 PM - Sherlee Back To Top (16138)

To Adjunctor,

Yeah I include it just to be specific though its pretty obvious. Just in-case there are readers who has no any background about this topic or just beginning to learn new technical skills.

 

Thanks for reading my article.

 

Wednesday, February 22, 2012 - 5:48:34 PM - Adjunctor Back To Top (16134)

When to use Database as Images Source

  • When image is stored in a Database.

Really?

 

I suppose it sounds ridiculously obvious but maybe it will help beginners.

Wednesday, February 22, 2012 - 10:54:08 AM - Sherlee Back To Top (16130)

To Mike Gilbert,

Here's Additional info for you. This is from http://blogs.msdn.com/b/ie/archive/2006/12/06/file-uris-in-windows.aspx.

Check this out when you have time.

And also take note that I used file://D:\MSSQLTIPWriting\2012_01\SampleReport\sherlee.jpg to my report to indicate or assign the file location of my external image. I did not execute it in my IE or Firefox browser.

 

For the sake of other readers I include some part below regarding the proper syntax.

If you will use URL use file:\\

If you will use URI use file:\\\

 

Proper Syntax

For the UNC Windows file path
     \\laptop\My Documents\FileSchemeURIs.doc

The corresponding valid file URI in Windows is the following:
     file://laptop/My%20Documents/FileSchemeURIs.doc

For the local Windows file path
     C:\Documents and Settings\davris\FileSchemeURIs.doc

The corresponding valid file URI in Windows is: 
     file:///C:/Documents%20and%20Settings/davris/FileSchemeURIs.doc

Wednesday, February 22, 2012 - 10:38:40 AM - Sherlee Back To Top (16129)

 

To Mike Gilbert,

The file:// URL is not incorrect. Your given URL link answers and explain everything.

But for the sake of other readers I will include a part of it here.

A file URL takes the form of  "file://host/path".

where host is the fully qualified domain name of the system on which the path is accessible, and path is a hierarchical directory path of the form directory/directory/.../name. If host is omitted, it is taken to be "localhost", the machine from which the URL is being interpreted. Note that when omitting host you do not omit the slash ("file:///foo.txt" is okay, while "file://foo.txt" is not, although some interpreters manage to handle the latter).

On my report this one works for me.

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

 

Meaning of slash character

The slash character (/), depending on its position, is used in different meanings in a file URL.

The // after the file: is part of the general syntax of URLs. (The double slash // should always appear in a file URL according to the specification, but in practice many Web browsers allow you to omit it, in some cases at least.)

The single slash between host and path is part of the syntax of file URLs.

And the slashes in path separate directory names in a hierarchical system of directories and subdirectories. In this usage, the slash is a general, system-independent way of separating the parts, and in a particular host system it might be used as such in a pathname (as in Unix systems).

This is one is also correct because this is the correct URI as understood by the Windows Shell API.

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


To all the readers thank you very much for appreciating this article.

 

Wednesday, February 22, 2012 - 10:19:38 AM - Sherlee Back To Top (16128)

 

To abc,

Advantages and disavantages of each when exporting the report is not the scope of my topic in this tip. However, I will try to explore regarding exporting the report and I will write another article for that. If you need to used images in your report header you can create a subreport that contain only the image you need for your report header for example a company logo. You can also reuse that sub report in any report where you need to include the company logo as one of the item in your report header.

Wednesday, February 22, 2012 - 6:41:41 AM - Mike Gilbert Back To Top (16127)

The file:// URL in your example is incorrect - see for example Wikipedia (http://en.wikipedia.org/wiki/File_URI_scheme) for the correct syntax.  In your example, the correct URL would be:

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

Thanks for the informative article!


 

Wednesday, February 22, 2012 - 3:41:17 AM - abc Back To Top (16124)
How about advantages and disadvantages of each when exporting the report and the implications around cell merge (when images are used in header) - to be honest, the above I would be willing to trade off a number of the above disadvatages if cell merge was actually realistically/reliably resolved

Tuesday, February 21, 2012 - 6:12:15 AM - Eduard Back To Top (16109)

This is great! Thanks!














get free sql tips
agree to terms