Wednesday, July 27, 2016

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 - 


2 comments:

  1. Hi,
    Thanks in advance, i am having a query on refresh data set in sql server 2014 , after published to live the data was not getting refreshed with latest data. Can you help me out how to track the issue in ssrs 2014.

    ReplyDelete
  2. The reason could be that you may be using the views to pull the data from the database and views always reserved the data in the cache memory. That means you should refresh or rebuild your views forcefully.

    ReplyDelete

Popular Posts