Display Graphics Dynamically in SSRS

By: Rob Fisch | Updated: 2007-10-19 | Comments (15) | Related: > Reporting Services Dynamic Reports

Problem

Sometimes having just numbers and tables on a report is not enough. Wouldn't it be much easier to spot trouble issues on reports based on color coding or specific images? In this tip we will explore how to display specific graphics based on specific data content. Instead of just showing the data now you can have images highlight certain data elements too. Take a look at this tip to find out how.

Solution

The first part of this tip will demonstrate how to add a graphics to your report using an external web reference. The second part will show how to add graphics to your project, in the actual data table, and then dynamically changing them based on data content.

Preparation

Step 1 - Add Some Sample Data

First, we'll need a little sample data to play with. In a test database, run the following script in a query window from the SQL Server Management Console.

-- MAKE SURE you do NOT run this in any production or system database
--uncomment the next line and replace the word "testdatabase' with the name of your database.
--USE testdatabase

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[GraphicsProjects1](
    [ProjectID] [int] IDENTITY(1,1) NOT NULL,
    [ProjectCode] [nvarchar](50) NULL,
    [ProjectDesc] [nvarchar](max) NULL,
    [ProjectOwner] [nvarchar](50) NULL,
    [ProjectDueDate] [datetime] NULL,
    [ProjectStatus] [nvarchar](50) NULL)

INSERT INTO GraphicsProjects1 ([ProjectCode] , [ProjectDesc] , [ProjectOwner] , [ProjectDueDate] , [ProjectStatus] )
Values ('A1' , 'Demo dynamic graphics' , 'John' , '10/31/2007 12:0:0 AM' , 'green' ) ;

INSERT INTO GraphicsProjects1 ([ProjectCode] , [ProjectDesc] , [ProjectOwner] , [ProjectDueDate] , [ProjectStatus] )
Values ('A2' , 'Billboard project' , 'Bill' , '11/15/2007 12:0:0 AM' , 'yellow' ) ;

INSERT INTO GraphicsProjects1 ([ProjectCode] , [ProjectDesc] , [ProjectOwner] , [ProjectDueDate] , [ProjectStatus] )
Values ('A3' , 'Magazine layout' , 'Alice' , '12/24/2007 12:0:0 AM' , 'red' ) ;

INSERT INTO GraphicsProjects1 ([ProjectCode] , [ProjectDesc] , [ProjectOwner] , [ProjectDueDate] , [ProjectStatus] )
Values ('B1' , 'Sandbox decorating' , 'Sally' , '7/4/2008 12:0:0 AM' , 'green' ) ;

This will add a table called GraphicsProjects1 to your test database. It contains 4 rows with simple information about various fictional projects.

Step 2 - Create a new Reporting Service project solution using the Business Intelligence Development Studio and follow the steps below.

In the new project, create a datasource connecting to the database where you created your new sample data (from Step 1).
Create a new report.
In the report, create a new dataset using the datasource you just created.

In the dataset, use the following query:

SELECT ProjectID, ProjectCode, ProjectDesc, ProjectOwner, ProjectDueDate, ProjectStatus
FROM GraphicsProjects1

Part 1 - Adding a static graphic to your report using an external web reference

On the layout section of your report, add a textbox by dragging the textbox object from the toolbox.

Inside the text box, enter Project Manager.
Then, drag the image object from the toolbox to a spot just below the Project Manager textbox. This will start the Image Wizard.

Click Next.
On the Select the Image Source page of the wizard, choose the Web option and click Next.

On the next page, enter the following for the URL:
http://www.mssqltips.com/images/RobFisch.JPG

Click Finish, on the Completing the Wizard page.

When you are done, your layout should look like this:

layout1

Let's do one more thing just for fun.

Right-click on the image and select Properties.
Click on the navigation tab and select the Jump to URL radio button.
Enter the following in the field:
http://www.mssqltips.com/author.asp?authorid=3
Save your work and preview the report.

You have just completed part 1 of this demonstration. You used the image object from the toolbox using an external web reference. Now on to the fun stuff...

Part 2 - Adding dynamic graphics based on data

Preparation

Download each of these images to a temporary folder on your computer. (Right-click and select Save Picture As for each one.)

Note: These images were taken from http://www.wpclipart.com. They post them as Public Domain images.

Add a table to the layout just below the picture.
Create 7 columns. (Each column should be about 1" wide.)
Add each of the data columns from the dataset, leaving the last column blank for now. The table should look like this:

Format the borderstyle for all textboxes in the table Solid. Font size 8.
Add an image object to the last column. This time choose Project as the image source.

On the Choose the Image from the Project page, click the New Image button.
Navigate to the folder on your computer where you saved the traffic light images. Choose the green traffic light image.
Click the New Image button again.
Choose the yellow traffic light.
Repeat again and choose the red traffic light. The page should look like this:

Click Next and then Finish.

Select the graphic and then find the Sizing property. Change it from Fit to FitProportional.
In the Value property, enter one the following expressions below:

=SWITCH(Fields!ProjectStatus.Value="red","traffic_light_red.png",
Fields!ProjectStatus.Value="yellow","traffic_light_yellow.png",
Fields!ProjectStatus.Value="green","traffic_light_green.png")

=IIF(Fields!ProjectStatus.Value="red","traffic_light_red.png",
          IIF(Fields!ProjectStatus.Value="yellow","traffic_light_yellow.png",
                   IIF(Fields!ProjectStatus.Value="green","traffic_light_green.png",nothing
                      )
              )
     )

Save your work.
Deploy the report to the Report Server.
IMPORTANT: You MUST also deploy the images. You should see them in your Solution Explorer along with your report.

Your deployed table should like like this:

report

In contrast to the first part of this tip, where we referenced an external image, this part uses several Project images that are stored on the reporting server with your reports. You could also have used the external web reference with this technique.

Next Steps

Look up the IIF() and SWITCH() functions in BooksOnLine.
Try adding tooltips to your images.
Take a look at these other Reporting Services tips.

About the author

Rob Fisch has worked with SQL Server since version 6.5 as a dba, developer, report writer and data warehouse designer.

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

View all my tips

Article Last Updated: 2007-10-19

Display Graphics Dynamically in SQL Server Reporting Services

Problem

Solution

Next Steps

About the author

Comments For This Article

Wednesday, January 22, 2014 - 7:58:54 AM - TYVM	Back To Top (28171)
For SQL Server 2008 the steps are a little different but I got it to work. Thank you very much for this great example :-)

Tuesday, January 8, 2013 - 6:33:10 PM - CK	Back To Top (21325)
For Visual Studio 2008, you can add multiple images to the "Images" folder, just above the DataSources folder, on the left panel of your your report. Then grap the Image icon from the Toolbox to the proper cell. After that Right Click\|Iamge Property\|Embedded\|fx. As Ludan stated above, no .png for SSRS 2008.

Thursday, January 20, 2011 - 9:03:44 AM - Rob Fisch	Back To Top (12645)
Saint, the article was written for SSRS 2005. In SSRS 2008, a similar option exists, but it is called "Action".

Thursday, January 28, 2010 - 8:51:05 AM - Ludan	Back To Top (4812)
be sure not to put the extension (.jpg, .png, etc) to graphics files (in the switch statement)