SSRS - Read images from the SQL Server database

SQL Server Reporting Services has a feature to read images data from the SQL Server database. To render the images on the report, we use the special report item called Image that contains a reference to an image that is embedded in the report, stored in a database, stored on the report server, or stored elsewhere on the Web. An image can be a picture that is repeated with rows of data. An image can be used as a background for certain report items also.

What are External, Embedded, and Data-Bound Images?

On the SSRS report, we can use the following source of the Images as -

1. External Images – In this case, the image item contains a path that points to an image on the report server or wherever it exists on the Web.
2. Embedded Images – In this case, the image data is stored within the report definition and does not exist as a separate file.  They ensure that the images are always available to the report, but they cannot be shared.
3. Data-Bound Images – In this case, Data-bound images can also be displayed from binary data stored in a database. Server-based images work well for logos and static pictures that are shared among several reports or Web pages.

Image.MIME Type Property

The MIME type is derived automatically when the image is imported. Every Image must be one of the following:

• image/bmp,
• image/jpeg,
• image/x-png,
• image/png,
• image/gif,

Report Builder and SSRS - add a Data-Bound Image

A report can include a reference to an image that is stored in a database. Such an image is known as a data-bound image. The pictures that appear alongside product names in a product list are examples of data-bound images.

Steps to add a data-bound image on the report

Step A: Load Images into SQL Database

In report design view, create a table with a data source connection and a dataset with a field that contains binary image data.

USE [DEMO] GO /****** Object:  Table [dbo].[TB_Image]    Script Date: 7/27/2016 2:00:09 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO SET ANSI_PADDING ON GO ---- Create table to store images in binary format CREATE TABLE [dbo].[TB_Image] (                 [Id] [int] IDENTITY(1,1) NOT NULL,                 [Functional Name] [varchar](50) NULL,                 [Picture] [varbinary](max) NULL,                 [MIME Type] [varchar](20) NULL, ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY] GO SET ANSI_PADDING OFF GO

To understand the functionality, I have the following images to store into the database and display on the SSRS report from the database table.

Load images in our SQL database in Image data table as given below:

USE [DEMO] GO ----- Image Data Table INSERT INTO dbo.TB_Image([Functional Name],[Picture],[MIME Type]) SELECT 'SQL Data Warehouse' as [Functional Name], BulkColumn as Picture, 'image/gif' as [MIME Type] FROM OPENROWSET (BULK 'E:\Images\sdw.gif', SINGLE_BLOB) AS IMAGE UNION SELECT 'Azure SQL Data Warehouse' as [Functional Name], BulkColumn as Picture, 'image/gif' as [MIME Type] FROM OPENROWSET (BULK 'E:\Images\asdw.gif', SINGLE_BLOB) AS IMAGE UNION SELECT 'SQL Server Integration Services' as [Functional Name], BulkColumn as Picture, 'image/gif' as [MIME Type] FROM OPENROWSET (BULK 'E:\Images\ssis_hdr.gif', SINGLE_BLOB) AS IMAGE UNION SELECT 'SQL Server Reporting Services' as [Functional Name], BulkColumn as Picture, 'image/gif' as [MIME Type] FROM OPENROWSET (BULK 'E:\Images\ssrs_hdr.gif', SINGLE_BLOB) AS IMAGE ---Pull data from the image table SELECT  [Id]       ,[Functional Name]       ,[Picture]       ,[MIME Type]   FROM [DEMO].[dbo].[TB_Image]

Step B: Display Images on SSRS Report from database

In the report development environment, add Demo report after making a database connection to our database. Now, we have to add dataset for the report as given below:

After clicking on the OK button, our report has the dataset as given below:

Now, we have to add the table on the report. Set dataset to the table and insert Image report item in the table column as given below:

Right click on the Image icon and choose Image Properties as given below:

On the General page of the Image Properties dialog box-

• In Select the image source, select Database,
• In Use this field, set as [Picture] because this is the column is containing the binary data of the picture
• In Use this MIME type, select the MIME type from the database field. 

Render Images on the report

After doing all the above stuffs, click OK button and browse the report as given below:

So, by using the above steps, we can render the requested images on the SSRS report from the database. Users can change these images in the data table without making any change on the report.

Tutorial Demo Video -