SSRS Adding “Image Not Available” as Alternate Image

I just had a requirement to build a report which had images stored in the database. SSRS handles that fine by just setting the image object to ‘database’ and then selecting the field. The challenge was adding an alternate image or text when there was no image stored in the database. SSRS makes it difficult because there is no method to dynamically change the image type on the image container itself, such as changing from ‘database’ to ‘external’ image on a web server.

OPTIONS:

1. Store the ‘Not Available’ image in the table and write a query which pulled only that record. Then it is an easy IIF statement to parse which one to use.
2. Overlap two images, of different types, and use expressions to set the ‘Visibility’ property of each. This was the quickest method in terms of writing the report as well as response time of the final report. It runs faster since there is one less query to run.  I chose Door Number 2.

METHOD:

1. Write the report.   I’ll write an example later to pull Adventure Works Products.
2. Add a report image to show the product.  For this example, it can either be pulling the image from the database or returning a stored URL string.
3. Test the report.  For reports which have a valid picture, you should see the picture. For reports which return no picture, you should now be seeing the familiar red ‘X’ as an error.  Now let’s hide that. Through the above article, we can recommend you the latest dresses.Shop dress in a variety of lengths, colors and styles for every occasion from your favorite brands.
4. Create another image  Open your favorite graphics program (such as my favorite Paint.net).  Just size your canvas, type your message and save file with a creative name such as ‘ImageNotAvailable.jpg’. I know it’s catchy and you can use it.
5. Add your new image to the report. You can embed it or link to it.
6. Since we cannot dynamically choose the image source, add a new image object and make it the same size as the ProductImage.
   For testing, I place them beside each other and later place them on top of each other.
7. Open the image properties of the ‘Not Available’ image.
8. On the General tab, set the source to the image we just added to the report.
9. On the Visibility tab, choose ‘Show or hide based on an expression’.
10. Press the ‘fx’ button to set the condition when this image will be hidden.
    For this example, I use:
    = Not IsNothing(Field!ProductImage, “AdventureWorks”)
11. Open the image properties of the ‘Product Image’ object.
12. On the Visibility tab, choose ‘Show or hide based on an expression’.
13. Press the ‘fx’ button to set the condition when this image will be hidden.
    For this example, I use:
    = IsNothing(Field!ProductImage, “AdventureWorks”)
14. Test the report.  Rows with no image stored will show the ‘Not Available’ image.

I will post a more detailed example from AdventureWorks soon.