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 -
- 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.
- 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.
- 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 -
Tutorial Demo Video -
Hi,
ReplyDeleteThanks 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.
Hey, thanks for this great article I really like this post and I love your blog and also Check Marketing Analytics with phyton.
Delete360Digitmg Marketing Analytics with python
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.
ReplyDeleteThese ideas have taken in a rumored Hadoop Training Institute in Chennai, Once you have finished Big Data Hadoop confirmation you can adequately deal with your association. ExcelR Data Science Courses
ReplyDeleteNaturally, the bridal accessories are another great place to add glitter and glamor to the wedding. glitter for sale
ReplyDeleteI wanted to thank you for this excellent read!! I definitely loved every little bit of it. I have you bookmarked your site to check out the new stuff you post. actual day videographer
ReplyDeleteThe clipping path benefits require the method which incorporates the cutting of the expected region of the image from whatever remaining parts of the photo.
ReplyDeleteclipping path service provider