Display Dynamic Image on SQL Server Reporting Services Reports - SSRS 2008

If you are storing images in file system and keeping the file location in MS SQL Server database tables, you might want to display database images by reading them from sql tables and showing them on web pages.
You can display images stored in SQL Server by using ASP.NET applications. You can refer to ASP.NET tutorial File Upload and Save Submitted Files to SQL Server 2005, Sample ASP.NET Web Site Application.
But as the title of this Reporting Services tutorial states you can build SQL Reporting Services reports in order to display database images.

Let's build our Reporting Services report example data. First start by creating sql tables.
After the table to store binary image files are created, insert image files into the sql table using


CREATE TABLE Authors

(

  AuthorId int IDENTITY(1,1) NOT NULL,

  FirstName nvarchar(100),

  LastName nvarchar(100),

  Avatar nvarchar(1000)

)

GO



INSERT INTO Authors (FirstName, LastName, Avatar) VALUES (

  N'Roger', N'Wolter', 

  N'C:\Inetpub\wwwroot\images\avatar1.jpg')



INSERT INTO Authors (FirstName, LastName, Avatar) VALUES (

  N'Dejan', N'Sarka', 

  N'C:\Inetpub\wwwroot\images\avatar2.jpg')



INSERT INTO Authors (FirstName, LastName, Avatar) VALUES (

  N'Itzik', N'Ben-Gan', 

  N'C:\Inetpub\wwwroot\images\avatar3.jpg')



SELECT AuthorId, FirstName, LastName, Avatar FROM Authors

Code

SQL Server Reporting Services Query Designer


select AuthorId, FirstName, LastName, avatar from Authors

Code

display image in Reporting Services table

Drag and drop Image control from Report Items Toolbox window onto the cell where you want to display the image
Right click and display context menu on the image. And select Image Properties menu item on the menu.

image properties added using report items toolbox

It is important that you should select the "External" as the value of the "image source" combo box.
And click the Expression button to display the Expression dialog window and edit the image source on the "Use this image" dropdown list.

expression editor for image url in reporting services table


= Code.ConvertTotWebUrl(Fields!avatar.Value)

Code

Note that I have used a Custom Code function named ConvertTotWebUrl in the expression of the image source.
You can open the code editor for Custom Code by selecting Report on the main menu then by choosing Report Properties sub-menu item. And navigating to Code tab on the Report Properties screen.
Here is the code of the custom code function ConvertTotWebUrl:

custom code in Reporting Services reports


Public Function ConvertTotWebUrl ( byVal FilePathOfImage as String) as String



RETURN REPLACE(REPLACE(FilePathOfImage, Report.Parameters!AvatarFolder.Value, Report.Parameters!WebFolder.Value), "\","/")



End Function

Code

As you see I'm using two string parameters in the Custom Code and access report parameters within the custom code by using the Report.Parameters collection.
I have defined the parameter "AvatarFolder" with default value "C:\Inetpub\wwwroot\".
And the second parameter is named "WebFolder" with default value "http://istw1849/".

And after I have executed and processed the MS SQL Server Reporting Services report "DisplayImageFromFolder" the .rdl report will display the following output on the report server.

SQL Server Reporting Services report table with images

MS SQL Server Reporting Services 2008 with Dynamic Image

If you want to display database image from MS SQL Server using MS SQL Server 2008 Reporting Services you can find another sql tutorial at Display Database Image using MS SQL Server 2008 Reporting Services.