Thursday, August 16, 2012

SSRS Report Image from A CRM Note Attachment

A saw a CRM Development forum question about displaying an image that was uploaded in a SQL Report and thought I would look into it. Turns out it is pretty easy to do.
For the sake of this example I uploaded 1 .gif, 1 .jpg, 1 .bmp, and 1 .png to a Note record for testing. I used this query to get the required data – we are interested in the DocumentBody and MimeType.
SELECT DocumentBody, MimeType
FROM AnnotationBase 
WHERE MimeType LIKE '%png' 
    OR MimeType LIKE '%gif' 
    OR MimeType LIKE '%jpeg'
    OR MimeType LIKE '%bmp'

To test I added a table and bound it to the resulting DataSet and added an image control. In the properties of the image control I set the following:

Image Source = Database

Field = DocumentBody from the query result

Mime Type = MimeType from the query result

Image1

Here is my result:

Image2

Looks like the jpeg file didn’t make it. Looking at the Error List for the report preview I saw this:

[rsInvalidMIMEType] The value of the MIMEType property for the image ‘Image2’ is “image/pjpeg”, which is not a valid MIMEType.

So it appears that ‘image/pjpeg’ the type CRM saves a jpeg image as is not valid for a SQL Report. To work around this you could either fix the Mime Type to ‘image/png’ and it seems to render all the types or you can create an expression based on the returned MimeType to replace the value as such:
=Iif(Fields!MimeType.Value = "image/pjpeg", "image/jpeg", 
Fields!MimeType.Value)