Working with images in Reporting Services

Posted by beakersoft | Posted in SQL Reporting Services | Posted on 24-12-2007

16


Images in reports header

Images. They can make your reports look great, but sometimes they are a bit tricky to show where you want, and when you want. Even something as simple as showing an image or not, or showing a different image based on the condition of some data.

Well, it might not be rocket science (or rocket engineering) but its not exactly well documented, so here are a couple of real world examples of how to handle images in your reports.

Showing/Hiding Images

First of all this is how to show or hide an image in a table, based on the condition of filed in your data set. This could be used to indicate (for instance) if an order is stopped. The first thing to do is add an image holder into your table. When you drag the image holder onto the cell of the table the image wizard will open. Follow the Wizard through and embed the image in your report. When its done you will see the image in the table cell.

Now, on the properties of the cell, drop down the Visible -> Hidden property, and click in Expression in the list, this will open up the expression editor. In here we are going to use an IIF statement to check the condition of one of our fields. We can then set the Visible state to true or false depending on the outcome. The statement will look something like:

=iif(Fields!Stop_Stat.Value = “Stop”, False, True)

With this now in place, when you run the report it should now only show the image when the value of Stop_Stat is true.

Changing the Image

The will be some circumstances where you want to show a different image depending on the value of a variable. This is done in a similar way to hiding the image, but instead you Data value of the image source.

The first thing to do is insert the images you need onto the report. Drop an image control onto your table where you want it to apear, and embed the image into the report. Then drop another image control on the report, maybe in the header or somewhere out of the way. Embed the image again and this time set this controls Visible property to false. Now you have both the images you need in the report.

At this point make a note of the names of both of the images embedded in the report. They tend to be the name of the file you used without the extension.

Now, go into the property’s of the image control in the table, under Data -> Value drop down to Expression to open up the expression editor. Here we are going to use an IIF statement on a filed, and tell it to use one image if the condition is true, or a different one if it was false:

=iif(Fields!Stop_date.Value is nothing, “advert_incomplete_1″, “stop”)

When you save it you wont see the image in the box any more, it should have changed to this

Image with expresion

Now, when you run the report, the images should swap round depending on what the value of your filed is, so you will end up with something like

image expresion example

Hopefully, you should now be able to make better use of images in your reports now, making them a bit better to look at and by using visual pointers instead of plain old text.

Comments (16)

Cool, This is very useful.

Thank you

Thanks, I always find some of the official Microsoft doc a bit lacking in certain areas, glad you found it useful

Thank god i found it.
Thank you.

Did you come across a solution to display images that are directly stored in a DB-table?

BR

Bendy

thanx a lot it is tooo useful :)

Cheers,
This was really helpful.

I want to load images into a report dynamically for a specific product. For each product I seek to view the report for, there may be no image, one image or multiple images. These images are stored in an external file system and their path is stored in a database table column called PhotoLink. I run a stored procedure that accepts ProductNumber as an input parameter and returns the file path for the image(s) stored in the Database table PhotoLink column. The path looks similar to this one:

file://ABC02YGK/Photos/XYZ-12342-002 (1000 XXX)[HB]-1.jpg

In the reports layout view, I have added an image control from the toolbox and set the following properties:

Source=External
Value =Fields!PhotoLink.Value
Sizing=FitProportional

When I run the report, the report asks for the ProductNumber as input parameter and correctly renders the image in case the product has only one image path stored in the database table column “PhotoLink”. Until here there is no issue. However, I need to further enhance the report and should dynamically add and populate single or multiple image controls to the report depending upon the number of images this product contains. Is this possible at all and how can I achieve this?

@smk – I think you are probably going to be into writting a custom extension for reporting services.
If there is a max number of images for each product you might just be able to drop that many controls onto the form and size them as needed, but thats not a very nice way to do it.
If you wrote a some custom code you could just add the images on the fly. Might be worth a look to see if anything already exists to do it?

If you use SSRS 2005 onwards you just need to use a matrix and add the imageid in the column grouping and productid in the row grouping. Run the report and any number of images that the product has will be displayed correctly.

Hola alguno pudo inserta una Image en una Table, Mi problema es q no me muestra las imagenes dentro de la table

I store images in my c:\crw\data\attachment\permit\images folder. they are Not stored in the DB nor in report services. I use the @xxx parameter to open a report for a certain record. So how do I get the images for that record i produce a report on to attach and display on a report?

Hi there, I have condition visibilty on images in my table, all works well. The problem I have is if the image is (correctly) hidden, I lose the border to that cell. Any ideas?
Roger

@Roger – i had never noticed that before, your hiding the content of the cell not the actual cell so i dont see why the border would go.

Maybe check the width of the cell is still large enough to see the border without the image in? If you just delete the image completely does the border still show?

Yes borders show when no image, if images all set to visible borders show, if all set to not visible no borders show. Got round the problem putting a rectangle in cell first then add image on top, all now works.
Roger

Roger,
Perfect solution! This has the added effect of allowing for exact placement of the image within the cell. It looks like the visibility is now associated to the rectange instead of the image.

Thanks,
Terry

That doesn’t work very well though if you’re exporting to Excel. Because the image is in a container you get extra rows, which, well, sucks…

Write a comment

Comments links could be nofollow free.