Rendering images from a database on SSRS reports

By:   |   Updated: 2013-06-20   |   Comments (22)   |   Related: > Reporting Services Development


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 at OPENROWSET 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:

How To Render an Image from a Database in a SQL Server Reporting Services (SSRS) Report

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:

add an image from the toolbox to your SSRS report

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

Set the image source to Database

Set the MIME type expression as shown below:

Set the MIME type expression

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:

The final step is to view the report that renders an image that is retrieved from the database

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

Next Steps
  • Retrieving images from a database table is a great way to provide a flexible solution for rending images on a report.
  • This is a very simple solution to implement and it could save you lots of time when your users want to freshen up their reports with some new images. 


sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at IT Resource Partners and a MSSQLTips.com BI Expert.

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

View all my tips


Article Last Updated: 2013-06-20

Comments For This Article




Monday, January 27, 2020 - 6:00:09 AM - Raymond Barley Back To Top (83994)

Regarding image size on export to PowerPoint, you will have to experiment with changing device information settings in the RSReportServer configuration file; take a look at these 2 links: https://docs.microsoft.com/en-us/sql/reporting-services/pptx-device-information-settings   https://docs.microsoft.com/en-us/sql/reporting-services/customize-rendering-extension-parameters-in-rsreportserver-config I've never done this before.

Sunday, January 26, 2020 - 7:49:20 AM - Naga Back To Top (83987)

Hi Ray Barley,

I have created one RDL which takes three images from database for each month. The images are JPEG format saved in Database using a front end tool. Each Image is less than 20KB, but once the slide is exported from report server in PPT slide, overall slide size is coming to more than 1MB and each Image size is increased to 300KB. 

if created manually this slide size is less than100KB. Could you please suggest to reduce the slide size when generated from SSRS. This is very critical as the report generates above 80 slides and overall size is increasing and not compatable to share over email.

Thursday, June 13, 2019 - 7:20:53 PM - Ray Barley Back To Top (81461)

Check out the SQL Server documentation at https://docs.microsoft.com/en-us/sql/relational-databases/import-export/import-bulk-data-by-using-bulk-insert-or-openrowset-bulk-sql-server?view=sql-server-2017

Make sure you look at the Security considerations and Bulk importing from a remote data file sections.

You should be able to specify a UNC path when you insert images and store them in the database.

Monday, June 10, 2019 - 3:51:58 AM - Shaik Mohammed Gouse Back To Top (81396)

This approach works fine in the localhost but doesnt display images from QA / external reporting server. Please suggest if there is anything extra permission or any extra piece of code to be added / modified to display images properly from QA / External reporting server

Thursday, June 28, 2018 - 8:03:07 AM - Mike Back To Top (76451)

Amazing. It worked! And very simple and easy to follow instructions. Thanks!

Tuesday, March 27, 2018 - 4:41:25 PM - Sathish Back To Top (75535)

I tested and it is working fine in my system. But, it is not displaying image in QA environment as expected. Can you please guide me to find the issue?

 

Thanks,

Sathish

Thursday, February 5, 2015 - 9:56:31 AM - Raymond Barley Back To Top (36166)

One alternative would be to use FILESTREAM which is a good choice if:

  • Objects that are being stored are, on average, larger than 1 MB.

  • Fast read access is important.

  • You are developing applications that use a middle tier for application logic.

See https://technet.microsoft.com/en-us/library/bb933993%28v=sql.105%29.aspx for more details.

There are a number of tips on FILESTREAM; see http://www.mssqltips.com/sql-server-tip-category/92/filestream/

 

Wednesday, February 4, 2015 - 6:53:14 AM - Mohit Patel Back To Top (36150)

Hi,

If we use binary string as a part of database and load image in SSRS using that binary value then it causes report performance issue.

I checked this thing by using "External" option and observed that it is faster then previous option.

 

Can some one please suggest me that which one is the best way to use image in SSRS report??

 

Thanks,

Mohit Patel

Monday, March 10, 2014 - 4:24:07 PM - leqid Back To Top (29697)

This post got me up and running in a jiffy. The code to add images to the database was especially helpful. Thank you for posting!

Wednesday, February 26, 2014 - 4:01:05 PM - KrisMaly Back To Top (29595)

I tested and it is working as explained.

Thanks for the Tip.

I like it and recommend to others.

Wednesday, February 26, 2014 - 3:30:14 PM - KrisMaly Back To Top (29594)

Thanks for the tip.

I wish that whenever publishing Tip or an article with dialog boxes it is better to express Version of the software used. As you know lot of versions are released and each version has its own kind of dialog boxes.

 

Wednesday, October 30, 2013 - 10:20:22 AM - Raymond Barley Back To Top (27335)

In this tip you get the image from a SQL query that returns a VARBINARY(MAX) column.  Just thinking out loud my idea would be to create a CLR stored procedure that retrieves the image from the database, performs some sort of manipulation of the image bits using .NET code, then returns the image bits as a VARBINARY(MAX) column.

I did a tip a while back on a CLR stored procedure: http://www.mssqltips.com/sqlservertip/1518/how-to-return-a-result-set-from-a-sql-server-2005-clr-stored-procedure/

The basic idea is that you create a stored procedure that executes .NET code.  

Wednesday, October 30, 2013 - 9:09:50 AM - Benny D. Back To Top (27334)

Great article Ray! I was wondering how I can control the image size dynamically in the SSRS report itself. Some requirements require the images to be imbedded into a table along side content. So therefore we don't always want to show the original image size nor do we want to scale. What if there was a specific height & width or image ratio we had to abide by? 

 

Thanks,

-Benny

Monday, July 29, 2013 - 9:01:55 AM - Raymond Barley Back To Top (26045)

Reply to can you expand this for PDF - Yes.  You can store just about anything in a VARBINARY column.

Here's a sample insert to load a report that I saved as a PDF:

INSERT INTO dbo.[Image] 

 (ImageID, ImageName, MimeType, ImageBits)

SELECT 

 1

,'Sample PDF Report'

,'application/pdf'

,BulkColumn FROM OPENROWSET 

 (BULK 'C:\temp\reports\4bd3b72e-6634-46ed-9699-d263b9852131.pdf', SINGLE_BLOB) MyImage

 

If you want to read the PDF from the VARBINARY column using ADO.NET code, you could use SqlDataReader.GetBytes() - see Working with Large-Value Types in ADO.NET at this link http://msdn.microsoft.com/en-us/library/bb399384.aspx

Monday, July 29, 2013 - 8:32:41 AM - Jeremy Kadlec Back To Top (26044)

Anon and Incrediblemouse,

Thank you both for your feedback.  All of the contributions Ray and all of the MSSQLTips.com Authors have made to the community are intended to help the community and share knowledge.  We hope this goal is achieved main times over on a daily basis.  Based on the feedback for this tip, it appears as if there is a great deal of positive feedback and more problems to be solved.

Thank you,
Jeremy Kadlec
Community Co-Leader

Saturday, July 27, 2013 - 2:09:10 PM - Michiel Back To Top (26034)

Nice article Ray, this does help. Can this functionality be expanded to reading pdf-files from a database too?

Saturday, July 27, 2013 - 8:24:00 AM - Incrediblemouse Back To Top (26032)

Ignore that Anon hater! Not everyone assumes every article should be about something they've never seen. Most people have realistic expectations that tip articles will contain some things they know and others they don't; some may be entirely new concepts and creative ideas, others will be basic useful tips for less the experienced but interested. It welcomes newcomers who may not have previous exposure.

That said, the article was great. Not every articletip article has to be an advanced journey into the depths of an author's experience.

Wednesday, July 24, 2013 - 8:51:10 PM - Anon Back To Top (25991)

No, I would use varbinary.

Filestream (2008R2) stores the entries on disk as files but you're not allowed to touch them through the filesystem in any way. It's filetable (SQL 2012) that lets you edit the files and retain consistency in the database.

But both absolutely require windows credentials and in the applications and reports I've seen lots of people are using sql credentials. So sadly this limits their usage for for very specific scenarios.

Finally, the other benefits of filestream and filetable (reducing memory usage) you wouldn't even see in SQL server because you need to use special .net commands to retrieve the file portion of the select statements. If you use a normal select statement there is no memory advantage.

With all of this said, I didn't like the article because this is basic functionality covered in any book or tutorial. If I read an SSRS article I want it to be something amazing, something that shows the depth of the writer's experience, something that the rest of us need to know but isn't already covered to death elsewhere. 

Sunday, July 7, 2013 - 1:56:12 PM - kerany Back To Top (25740)

thank you very much  for this nice tip ,it's very obvious to apply

 

Friday, June 28, 2013 - 12:16:40 AM - Jagdish Back To Top (25606)

Good Article. Thanks for sharing it. Thanks

 

Thursday, June 20, 2013 - 5:01:53 PM - TimothyAWiseman Back To Top (25508)

This was a great tip, thank you for adding it.  Though I do agree with Brian that I would at least consider using filestream instead of storing it in varbinary(max).

Thursday, June 20, 2013 - 6:03:12 AM - Brian Back To Top (25500)

Good article - the step further could be to use a filestream for easy update of pictures with no work for the sql-guy in maintennance...














get free sql tips
agree to terms