Rendering images from a database on SSRS reports

Problem

I have a requirement to put images on SSRS reports and allow the business users to change the images every so often.  I want to implement this in such a way that I don't have to modify a report when the image is changed.  How can I do that?

Solution

SSRS provides a built-in capability to handle your requirement.  When you add an image to a report, you can specify the source of the image as:

• Embedded - a copy of the image is stored in the report
• External - retrieve the image from a web site (e.g. SharePoint) or a file share
• Database - select a row that contains the image from a database table  

Choosing embedded would require you to modify your report every time the image changes. Either the external or the database options would work in this case.  The external option is really simple; take a look at our earlier tip SQL Server Reporting Services Image Source Report Options for the details.  That leaves us with the database option and I will walk through how to implement it in this tip.

I will review the following steps:

• Create a table to store the images
• Run a SQL script to load images into the table
• Add a report dataset to a report to retrieve an image
• Add an image to a report and retrieve it from a database table.
• Show the report that includes the image

Create an Image Table

I will use the following table to store images in the database:

CREATE TABLE dbo.[Image]
(
 ImageID INT,
 ImageName VARCHAR(50),
 MimeType VARCHAR(50),
 ImageBits VARBINARY(MAX)
);

The above table will allow for retrieving images by ImageID or ImageName.  I'm not using an IDENTITY column for the ImageID because I want to specify the values myself rather than having them assigned automatically.  The image will be stored in the ImageBits column and the MimeType is used to specify the type of image; e.g. image/png, image/gif, etc.

Add Images to the Image Table

I will use the following T-SQL script to insert an image into the image table:

INSERT INTO dbo.[Image] 
 (ImageID, ImageName, MimeType, ImageBits)
SELECT 
 1
,'UserGroupImage'
,'image/png'
,BulkColumn FROM OPENROWSET 
 (BULK 'C:\images\Baltimore_tech5.png', SINGLE_BLOB) MyImage

Take a look at our earlier tip Using OPENROWSET to read large files into SQL Server for the details on using OPENROWSET to read an image file from disk.  If you want to really dig in to the details of OPENROWSET, then take a look atOPENROWSET in the MSDN library.  Note that when you use the BULK option, you must have the ADMINISTER BULK OPERATIONS permission.

Add a Report Dataset

When you add a dataset to your report, you need to complete the Dataset Properties dialog as shown below:

The query for the above dataset selects the ImageBits and MimeType columns from the Image table where the ImageID is equal to 1. 

Add an Image to a Report

When you add an image from the toolbox to your SSRS report, you need to complete the image properties dialog as shown below:

Set the image source to Database; set the field expression as shown below:

Set the MIME type expression as shown below:

Note that in both of the above expressions, you select Datasets in the Category, the GetReportHeaderImage dataset in the Item, and the appropriate field from the dataset in the Values.

Show the Report

The final step is to view the report that renders an image that is retrieved from the database.  Here is the report as shown in the Preview tab of the report designer:

I added the image to the Page Header section of the report.

Source Collected from MSSQLTIPS.COM