In one of my first projects, I was asked to store images in database and then use them for my SSRS 2005 reports. The newbie I was, for a moment I was shell-shocked how I could store images in a database, let alone use them in my SSRS reports. Fortunately for me, I mumbled to my client the most used phrase in the IT indutsry that I will check with the team and let him know, rather than showcasing my ignorance. A couple of searches and I had got all the matter that I required. This post will show how to create a sample employee report based on database images.
1) To start, create the employee table and populate the data into them. Sample code has been given below but remember to give the location of the photos.
CREATE TABLE EmployeeProfile( EmpId INT , EmpName VARCHAR (50) NOT NULL, DeptName VARCHAR (50) NOT NULL, EmpPhoto VARBINARY (MAX) NOT NULL);
GO
INSERT EmployeeProfile (EmpId, EmpName, DeptName, EmpPhoto)
SELECT 1001, ‘Prashant Kumar’, ‘DW’, BulkColumn
FROM OPENROWSET (BULK ‘C:Prashanth.jpg’, SINGLE_BLOB) AS EmployeePicture;
GO
INSERT EmployeeProfile (EmpId, EmpName, DeptName, EmpPhoto)
SELECT 1002, ‘Jitain Raheja’, ‘Manufacturing’, BulkColumn
FROM OPENROWSET (BULK ‘C:Jitain.jpg’, SINGLE_BLOB) AS EmployeePicture;
GO
INSERT EmployeeProfile (EmpId, EmpName, DeptName, EmpPhoto)
SELECT 1003, ‘Sandeep Shetty’, ‘Manufacturing’, BulkColumnFROM OPENROWSET (BULK ‘C:Sandy.jpg’, SINGLE_BLOB) AS EmployeePicture;
GO
INSERT EmployeeProfile (EmpId, EmpName, DeptName, EmpPhoto)
SELECT 1004, ‘Rahul Nair’, ‘DW’, BulkColumn
FROM OPENROWSET (BULK ‘C:Rahul.jpg’, SINGLE_BLOB) AS EmployeePicture;
GO
INSERT EmployeeProfile (EmpId, EmpName, DeptName, EmpPhoto)
SELECT 1005, ‘Hari Selvarajan’, ‘Manufacturing’, BulkColumn
FROM OPENROWSET (BULK ‘C:Hari.jpg’, SINGLE_BLOB) AS EmployeePicture;
2) Create a report with 2 datasets and one report parameter
a) DS_RP Query : SELECT DISTINCT DeptName FROM EmployeeProfile
b) Image Query : SELECT DeptName,EmpId, EmpName, EmpPhoto FROM EmployeeProfile WHERE (DeptName = @RP1)
3) Make sure that the properties of report parameter RP1 is set as follows
4) Go to the layout and then drag and drop a table from the toolbox. In the detail row of the first column, drag and drop an image. Then follow the steps of the image wizard.
Once the database option is selected, click on next and select the datasource, image type and image field from it.
Click on finish.
5) On the other 2 columns of the table, use the Emp Name and the Emp ID.
6) Click on preview. With a bit of formatting, you should be able to attain better results.
On selecting DW
On selecting Manufacturing
hi ,Great blog.can share rdl file.
[…] paid by the Indian Tourism Board for the extra effort ) and used it based on the state (refer SSRS reports using database images). Have fun and festive greetings in […]
[…] This is all it takes to embed images in your reports and use them. You can use them for a wide variety of purposes, though I usually use them for showing indicators or for using them as hyperlinks. However, if you want to store images in your database and use them in your reports, you might want to check out this post. […]
i have around 30,000 rows and it would be possible to extract those from database into ssrs services and if i click on particular image expansion should happen. Please suggest