The CRM Grid – A Microsoft CRM Blog

Greg Owens’ rose-tinted MS-CRM mumblings

SSRS – checking an external image exists

15 July 2011 – Updated with working code.

I’ve just noticed that this post had some comments that it wasn’t working. I fear it may never have worked. I’ve reviewed the installation where I deployed this code originally and the working, valid code is now listed below. Apologies to anyone who lost time trying to use the original.

——-

SSRS supports adding external images to your reports. This is useful if, like my client, you have images stored in a SharePoint Document Library. Using some commonly-found code for structuring their document libraries, most accounts have had an image called “main.jpg” uploaded. If “main.jpg” does not exist, there are good business reasons for this.

The client required a report which should include “main.jpg”, if it exists. Otherwise no image should be present at all. The problem is that SSRS is only aware of the URL to the image as a simple string. I used SQL to construct a URL string for each record, but SSRS can’t natively detect whether an image exists at this URL or not. There is a way around this, however. SSRS allows you to embed your own custom code in your report properties. The following code checks whether an image exists at a given URL and returns true or false. This code can be called from an image control’s “Hidden” property, or combined with an expression in the image controls “Value” property to manipulate the URL:


Function IsValid(ByVal Url As String) As Boolean
Dim sStream As IO.Stream
Dim URLReq As Net.HttpWebRequest
Dim URLRes As Net.HttpWebResponse

Try
URLReq = Net.WebRequest.Create(Url)
URLReq.Credentials = System.Net.CredentialCache.DefaultCredentials
URLRes = URLReq.GetResponse()
sStream = URLRes.GetResponseStream()
Dim reader As String = New IO.StreamReader(sStream).ReadToEnd()
Return True
Catch ex As Exception
Return False
End Try
End Function

To use it, copy the code above to your report properties (Report > Report Properties… > Code), then after inserting an image control (with Image Source of “Web”), add the following code to the Hidden property (Image > Visibility > Hidden > Expression…):

=Code.IsValid(Fields!fieldWithUrlToImage.Value)

Any suggestions, improvements or alternative approaches are welcome!

Advertisements

15 Comments»

  Amie wrote @

This article is exactly what I’m looking for and need. However, I keep getting errors in the code for the Hidden property. The first error is an Unrecognized Identifier for If. If I change it to use IIf, then ImageExists becomes an Unrecognized Identifier. Any help is appreciated. Thanks!

  Special Ed wrote @

When I run this code the I get an error returned from the SSRS server so the check for the image existance always returns false. I changed the function to return the error message instead of boolean value. The error message was:

The remote server returned an error: (401) Unauthorized.

Do you have any ideas how to fix this?

  Greg Owens wrote @

Sorry both for the extended delay. I’ll look into this at the very earliest chance. I’m guessing that I missed a step or pre-requisite out of my instructions. If you spotted what it was yet, then please shout up and save me the time!

  Special Ed wrote @

I have entered a post in a forum on the Simple-Talk website hoping to get more exposure on this. Here is the link to that post. In it I have listed my own testing results that be be of some use to you. Thanks for looking into this.
Ed

http://www.simple-talk.com/community/forums/thread/74989.aspx

  Greg Owens wrote @

Ed – extra info was useful, thanks.

Note that I haven’t tried to fix the code in Visual Studio yet, but nonetheless perhaps you could try adding the following line at line 4 (as per the numbering on the code snippet in the original article):

m_Req.UseDefaultCredentials = True;

## Note – untested! ##

Let me know who you get on 🙂

Greg

  Special Ed wrote @

I added the line you suggested to the function. Here is what I have so far.

Public Function ImageExists(ByRef URL As String) As Boolean
‘Create a web request
Dim m_Req As System.Net.HttpWebRequest = System.Net.HttpWebRequest.Create(URL)
m_Req.UseDefaultCredentials = true

‘Get a web response
Try
Dim HttpWResp As System.Net.HttpWebResponse = CType(m_Req.GetResponse(), System.Net.HttpWebResponse)
If HttpWResp.StatusCode = System.Net.HttpStatusCode.OK
Return True
Else
Return False
End If
Catch ex As System.Net.WebException
If ex.Status = System.Net.WebExceptionStatus.ProtocolError Then
Return False
End If
End Try
Return Nothing
End Function

Then I changed the code for the image visibility to this:

=IIf(Code.ImageExists(Fields!empImagePath.Value),False,True)

My results, in VS on my development system the added line of code works great and I am getting the functionality we are looking for. If the image exists it is displayed, if not the blank image with the red “X” is not visible. GREAT!

And then I deployed the report to the production SSRS server. Then I got the following error message:

The Hidden expression for the image ‘image1’ contains an error: Request for the permission of type ‘System.Net.WebPermission, System, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089’ failed.

I have successfully traded one error for another. Arggg!

If it were easy, then everyone could do it. Back to troubleshooting. Again, I think if my SSRS IIS server allowed anonomous requests this would not be an issue. Alas, mine is setup for windows authentication. Any help is greatly appreciated.

Thank you Greg for looking at this.

Ed

  Greg Owens wrote @

Hi Ed – progress then!

The answer to your problem lies in the area discussed in at the following location: http://blogs.sqlxml.org/bryantlikes/articles/824.aspx

Sadly I don’t have an environment set up to test this (or the time at present). Suffice to say that at the moment, the example I gave used embedded code rather than a custom assembly so some of the items in the link above will need to be done differently (if they can be done at all).

Perhaps the easiest way to deal with this is to change the embedded code to a custom assembly and then follow the steps in the blog post. Also more info here: http://msdn.microsoft.com/en-us/library/aa237680(SQL.80).aspx

Keep us updated. I’m not giving up on this but may be a while before I can investigate 😉

Greg

  Greg Owens wrote @

…and Amie – not forgotten about you. Soon as I get chance to boot up my VPC i’ll try and recreate the issue 🙂

  Amie wrote @

Thanks Greg! Don’t worry about my issue now. I’ve been following the conversation. I am at the same point now as Ed.

  Greg Owens wrote @

Great – how did you solve your issue? Did I miss something in my directions?

  Amie wrote @

I added the line that you recommended and I changed the image name I was calling in the Visibility Expression field. I had to end up using the Value string instead of the Field name in the Expression. My Value string is a concatenation to make up the URL and is not a Field that I can call.

  Shailesh Patel wrote @

I found easy solution to check whether image exists on remote url without writing any server side code.

Here is solution that works
http://shailkpatel.blogspot.com/2009/10/check-whether-image-exists-on-remote.html

  Donald Harman wrote @

Shailesh

I would like to read your solution but can’t view youe post

Shailesh Patel wrote @ January 25, 2010 at 20:05 UTC
I found easy solution to check whether image exists on remote url without writing any server side code.

Here is solution that works
http://shailkpatel.blogspot.com/2009/10/check-whether-image-exists-on-remote.html

  Special Ed wrote @

I would be interested to find out if anyone has gotten this to work with SQL Server Reporting Services. I may be missing something but I do not see how to add the on error attribute to the image control on the report.

  afraid_to_code wrote @

Did anyone ever get this working? I’ve tried using this function which works fine in a console app but which returns “Request for the permission of type System.Net.WebPermission failed.” when calling it from SSRS.

Public Function ImageExists(ByRef URL As String) As String
‘Get a web response
Try
‘Create a web request
Dim m_Req As System.Net.HttpWebRequest = System.Net.HttpWebRequest.Create(URL)
m_Req.Credentials = New System.Net.NetworkCredential(“”, “”, “”)

Dim HttpWResp As System.Net.HttpWebResponse = CType(m_Req.GetResponse(), System.Net.HttpWebResponse)
If HttpWResp.StatusCode = System.Net.HttpStatusCode.OK Then
Return True
Else
Return False
End If
Catch ex As System.Net.WebException
If ex.Status = System.Net.WebExceptionStatus.ProtocolError Then
Return False
End If
Catch ex As Exception
Return ex.Message
End Try
Return Nothing
End Function


Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: