- T-SQL functions to convert Strings to Tables
- Batch Process to rename multiple files using Windows DOS
- SSIS Multiple Lookups in one
- mysqldump: Got error: 2049: Connection using old (pre-4.1.1) authentication protocol refused (client option 'secure_auth' enabled) when trying to connect
- AHK Countdown Tooltip
Last Updated on Tuesday, 15 November 2011
We want some photos taken from another website of ours to display in our report. These are not stored locally on the Reporting Server because other services use these photos on our Intranet and we only want one location to upload the photos. These images are provided via a website address (http) or network share (file).
Before you ask, we have several MS Sharepoint sites but we didn't go down the route of uploading 10000+ images into a Sharepoint database though we are still considering it; so this article is more about just getting external images to display when they are provided over an authenticated URL (You shouldn't be having any issues with this if the image is available to anonymous users - eg. Google Logo).
- Windows XP Workstation
- Business Intelligence Development Studio 2008 (BIDS)
- SQL Server 2008 R2 Reporting Server (SSRS) running on Windows Server 2003
- Team Foundation Server 2010 (TFS)
Problem #1: Image does not appear within SSRS
When previewing the report in BIDS, the photo appears fine. If we deploy the report to our Reporting Server and attempt to view it using a Web-Browser, the image does not display:
- Get report to see images hosted on an external URL
- Check if image exists to display alternative placeholding image.
Solution #1: Image does not appear within SSRS
Configure the Unattended Execution Account: According to Microsoft and Stack Overflow, your reporting server needs to have the "unattended execution account" configured. Some sources are specific saying that the account will need to be able to logon to the external server holding the files.
When the report is previewed in Report Designer, preview uses the credentials of the user to display the image. When the report is run on the report server, the report server uses the unattended execution account to retrieve the image.
So here's my checklist:
- Test adding an external image that doesn't need credentials (such as Google's logo @ http://www.google.co.uk/images/srpr/logo3w.png)
- Test disabling the "Unattended User Account" and seeing if Google's logo still appears.
- Test browsing to your images when you are not using one of your Intranet computers or user accounts (eg. try Local Admin Account on Computername as domain).
- Ensure the "Unattended User Account" can see the photos. - This solved the problem for us
- Some forums suggested to put this user also in the "Browser" roles of the Reporting Manager
Problem #2: Alternative image if file does not exist
We don't have photos for all ~20000 students at all times and these get updated/uploaded/added to about once a week. In cases where there are no photos, SSRS displays a mini ugly red cross. On the system that used to do this, we could use the HTML attribute "OnError" to load an alternative image. We would like something similar for SSRS.
Solution #2: Alternative image if file does not exist
More of a workaround than a solution but by asking some colleagues who developed the previous version (using 3rd-party components other than Microsoft SSRS) that the existence of a photo depended on the status of a student in any case. This meant that I could use the same Oracle PL/SQL query they used to determine whether to display an image or not. Bit of a cop-out I know but after two weeks of trying to find a solution, I would definitely recommend seeing if you can't check at the database level first. I will in future.
Fallback to debug methods; create a textbox that will be visible on the executed report (obviously replace the field name with the name of your field containing the location of the image - ours is "PhotoURL"):
- =" The file " & First(Fields!PhotoURL.Value, "PhotoDetails") & IIF(Code.ImageExists(First(Fields!PhotoURL.Value, "PhotoDetails")), " exists!", " does NOT exist.")
Additional Error (if you get this)
We didn't get this error as the problem was down to not having a "unattended user account" who could access the images.
Can apparently be solved by adding this to the top of your web.config file.
- Request for the permission of type 'System.Net.WebPermission, System, Version=22.214.171.124, Culture=neutral, PublicKeyToken=b77a5c561934e089' failed.
- <location allowOverride="false">
- <trust level="Full" originUrl="originUrl" />
Searches that got me nowhere
- ssrs check if file exists
- ssrs hide image with red cross
- ssrs alternative image on error