CODEWITHSUNDEEP
sql-serverreporting-services
jw11432
jw11432

Reputation: 593

SSRS: Display image from varbinary column in the database

My apologies if I've missed something, but from what I've found here on SOF as well as other sites, I cannot find my issue as having been discussed. I need to display an image that exists in SQL Server as a varbinary column.

SELECT 
    photo = CASE WHEN event_id = 47 THEN e.data ELSE NULL END

enter image description here

Here's the first 8 bytes: 0x2D00000030303030 and I've been told this is a JPEG.

My attempts so far include the following SSRS image setup:

enter image description here

However, I still get the red X in the report itself.

enter image description here

Now, I have seen the page that describes putting the images in a separate table, but I don't believe that's going to work for my purposes because I need an image to display in the same tablix with specific rows only. My current understanding of SSRS presumes only one data set per tablix, so I can't "combine" two tables into one tablix. Would the solution be to use this separate table approach, but then join with my existing dataset?

EDIT 1: Upon getting the pictures to show, I have erroneous red X's where nothing should be showing, probably on account of the expression I'm using. NOTE: It turned out to be base64 from which I had to decode. System.Convert.FromBase64String(Fields!photo.Value)

I have tried the following expression to get rid of the X's, but it doesn't work: =iif(isnothing(Fields!photo.Value)=1,nothing,System.Convert.FromBase64String(Fields!photo.Value))

enter image description here

Any ideas?

Upvotes: 2

Views: 4173

Answers (1)

Alan Schofield
Alan Schofield

Reputation: 21683

Try using the following in the "Use this Field" expression

=System.Convert.ToBase64String(Fields!myImageColumn.Value)

I don't know if your image is of this type but I had the same issue many years ago and converting it using the above which worked. Also, try changing the MIME type to image/png just in case you have been misinformed.

Edit after update from OP

Now that the image issue has been resolved, you will still get a red x where you have no image to show. You could try two things.

  1. Set the visibility of the image placeholder based on the image column content (empty or not). I can't see why would not work but just in case...
  2. Add an empty image to the database and use that if no actual image exists.

Upvotes: 3

Related Questions