2

I am trying to show images for products inside a basic report. The image needs to be dynamic, meaning the image should change based on the SKU value.

Right now I am inserting an image into a table, setting to external, and i've tried:

=Fields!URL.Value
=http://externalwebservername/sku= & Fields!SKU.Value
="http://externalwebservername/sku=" & Fields!SKU.Value

I do not get any images in my table.

My stored proc has all the data, including a URL with the image I wan't to show. Here is a sample of what the URL looks like:

http://externalwebservername/sku=123456

If I enter the URL in the field without "=" it will show that ONE image only.

How should I set up the expression to properly show the external image based on a dynamic URL? Running SQL 2016

tnoons91
  • 41
  • 1
  • 1
  • 3

2 Answers2

2

Alan's answer should work, but in our environment we have strict proxy/firewall rules, so the two servers could not contact each other.

Instead we are navigating to the file stored on our storage system.

We altered the URL column to point to file path in the stored procedure. Insert image, set Source to External and Value set to [URL].

URL= file://server\imagepath.jpg

tnoons91
  • 41
  • 1
  • 1
  • 3
1

As long as the account executing the report has permissions to access the URLs then your 3rd expression should have worked.

I put together a simple example as follows.

I created a new blank report then added a Data Source. It doesn't matter where this points, we won't use it directly.

Then I created a dataset (Dataset1) with the following SQL to give me list of image names.

SELECT        '350x120' AS suffix
UNION SELECT        '200x100' 
UNION SELECT        '500x500'

Actually, these are just parameters for the website http://placehold.it/ which will generate images based on the size you request, but that's not relevant for this exercise.

We'll be showing three images from the following URLs

http://placehold.it/350x120
http://placehold.it/200x100
http://placehold.it/500x500

Next, create a table, I used 3 columns to give me more testing options. Set the DataSetName to DataSet1 if it isn't already.

In the first column the expression is just =Fields!suffix.Value

In the second column I added an image, set it's source property to External and the Value to ="http://placehold.it/" & Fields!suffix.Value

I then added a 3rd column with the same expression as the image Value so I could see what was being used as the image URL. I also added an action that goes to the same URL, just to check the URL did not have any unprintable characters in it that might cause a problem.

The basic report design looks like this. enter image description here

The rendered result looks like this. enter image description here

Alan Schofield
  • 19,839
  • 3
  • 22
  • 35
  • Thank you for your very detailed answer, I did exactly your steps above, and I am getting blank boxes still. Could there be any server settings that need to be set? I've only been using SSRS for a few months now. – tnoons91 Apr 13 '17 at 15:05
  • Can you add an image somewhere on the report and set its value to a fixed URL say `="http://placehold.it/500x500"` If that works, add a new column to the tablix in the test report we built. Insert an image and set it's Value to the same url `="http://placehold.it/500x500"` and see if that works. – Alan Schofield Apr 13 '17 at 18:17
  • Also, what are you building the reports with, Report Builder or Visual Studio? And does the report fail when you preview it or just once it's deployed? It's UK Public holiday tomorrow and Monday so I may not get back to you soon. – Alan Schofield Apr 13 '17 at 18:19
  • I inserted image with Source 'External' and Value '="http://placehold.it/500x500"' in the report column title, and no image appeared in preview or when I saved, and ran. I am using both Report Builder and VS. The report does not fail completely, only the images come back with no image on them, the rest of report renders properly. When I preview report in Report Builder, I get red X's. – tnoons91 Apr 14 '17 at 15:26
  • Not sure if that was a typo but you will need to use the full URL `="http://placehold.it/500x500"` – Alan Schofield Apr 14 '17 at 16:01
  • For some reason stackexchange got rid of the `http://` but it is correct in report. – tnoons91 Apr 14 '17 at 17:28
  • Here is the error in ErrorLog: `processing!ReportServer_0-3!77e9c!04/14/2017-11:08:40:: w WARN: An error occurred retrieving the external resource 'http://placehold.it/200x100' : Unable to connect to the remote server` – tnoons91 Apr 14 '17 at 18:00
  • Sounds more like an access/permissions issue. Can you open the url directly in browser from the pc or SSRS server – Alan Schofield Apr 14 '17 at 18:34