Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 
Untangle TempDB Performance with SQL Diagnostic Manager - Free Webinar
 

Rendering images from a database on SSRS reports


By:   |   Last Updated: 2013-06-20   |   Comments (18)   |   Related Tips: > Reporting Services Images

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. 


Last Updated: 2013-06-20


next webcast button


next tip button



About the author
MSSQLTips author Ray Barley Ray Barley is a Principal Architect at RDA Corporation and a MSSQLTips.com BI Expert.

View all my tips





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



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

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


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

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 05, 2015 - 9:56:31 AM - Raymond Barley Back To Top

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 04, 2015 - 6:53:14 AM - Mohit Patel Back To Top

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

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

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

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

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

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

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

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

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

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

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 07, 2013 - 1:56:12 PM - kerany Back To Top

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

Good Article. Thanks for sharing it. Thanks

 


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

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

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...


Learn more about SQL Server tools