3

I have a report that needs to show images from a Windows folder on the server, which is working (see here ). Now, I am wondering how to get the report to pull images of differing file types, like jpg & tif. (I am using png by default). Is there a relatively easy way to do this? The image names with file extension are not in the SQL database.

EDIT: I entered this in the Custom Code block, from Daniel's help below.

Public Function GetImage(ByRef Filename As String) As String ' Full image path used for testing if it exists Dim ImagePath As String ImagePath = "\\GVSSERVER1\GVSServerD\Acclamare_Images\" + Filename ' Test to see if the file exists as a gif Try If System.IO.File.Exists(ImagePath + ".png") Return "file://" + ImagePath + ".png" ElseIf System.IO.File.Exists(ImagePath + ".jpg") Else Return "file://" + ImagePath + ".jpg" End If Catch ex As Exception Return "Hit an Error" End Try Return "Hit the end" End Function
When I run the report, it fetches the .jpg extension even though the image file is a png, and there isn't a jpg file for that item. Any idea on how to correct that?
EDIT 2: I wasn't having success with the updated custom code, but I could have been missing something, as I'm no expert with custom code. I found this question (see here) which is for a function. I tried it and it works, except for some reason .tif files don't display on the report. I installed Microsoft Picture Manager (from the Sharepoint exe download), but it still doesn't display the .tif files.

itstknecht
  • 67
  • 1
  • 7
  • Do you mean that you will only have a file name but not the extension information available and you have to figure it out? – Lucky Jul 25 '17 at 15:46
  • Correct. We have a folder of images, and they have a few different extensions. – itstknecht Jul 25 '17 at 20:20

1 Answers1

3

The good news is that this is definetely possible, however it takes a bit of custom code and server side tweaking to get ready.

General Idea: Create a code behind function that takes the name of our image and then does file existence tests to determine which file extension type actually exists on the network share.

If you right-click outside of the report area and go to properties, you will see the custom code window where you can paste the following function code.

Custom Code:

Public Function GetImage(ByRef Filename As String) As String
    ' Full image path used for testing if the image exists
    Dim ImagePath As String
    ImagePath = "\\EM-SSRS\ImageTest\" + Filename

    ' Test to see if the file exists as a gif
    If System.IO.File.Exists(ImagePath + ".gif")  THEN
            Return "file://" + ImagePath + ".gif"
    ElseIf System.IO.File.Exists(ImagePath + ".png")  THEN
            Return "file://" + ImagePath + ".png"
    ElseIf System.IO.File.Exists(ImagePath + ".jpg")  THEN
            Return "file://" + ImagePath + ".jpg"
    ElseIf System.IO.File.Exists(ImagePath + ".jpeg")  THEN
            Return "file://" + ImagePath + ".jpeg"
    End If

    Return "No Image Exists"
End Function

You will have to edit the ImagePath variable to contain the network share path for your scenario or even add another parameter to the function to make it more generic if you wish.

Once the code function is created, I would advise creating a dummy textbox expression on the report and using the following value:

=Code.GetImage("Filenmame")

Which will allow you to view the output of the function and tweak things as needed. Note that the "file:// ... " syntax may not work from within report builder or visual studio and may have to be deployed to a report server for testing.

Of course, once it looks like the function is working, add the image, make sure to set the source to external and use the same expression as for the textbox.

Server Side Changes

Upon further testing on my own environment, I've had to make two additional changes to get this to work:

  1. Ensure that the unattended execution account is set to a domain account that has permission to the file share

  2. Edit the SSRS config in the rssrvpolicy.config file to trust custom code and allow the execution of the File.Exists function by specifying "FullTrust"

<CodeGroup
  class="UnionCodeGroup"
  version="1'
  PermissionSetName="FullTrust"
  Name="Report_Expressions_Default_Permissions"
  Description="This code group grants default permissions for code in report expressions and code element."
  ...
</CodeGroup>

I did restart the SSRS service after making these changes (I assume that's required, but did it as a precaution)

Note that I am not an SSRS server admin and someone else may be able to add some additional information about the custom policy change. Glancing through the MS documentation, there is a recommendation about using custom assemblies instead so take this part with a grain of salt.

Daniel
  • 1,364
  • 1
  • 19
  • 34
  • Daniel, please see my edited question. I'm not familiar with custom code, so I could be missing something... – itstknecht Jul 25 '17 at 21:45
  • This is a great solution and I was curious if System.IO.File was supported in SSRS. I ran a quick test, a textbox with the following expression: =IIF(System.IO.File.Exists("FILE.jpg"),1,0). I tested the file:// format, / format, ./, C:/... I have yet to get a true to be thrown. This is based on a file at exists on the SSRS report server. Notably if I insert an image and choose external source, the value is "/FILE.jpg", which doesn't work when submitted to the Exists() function. I put this same code as a function in global code and ran it that way as well with all negative returns. – Lucky Jul 26 '17 at 15:51
  • 1
    @Lucky, this is the same behavior I was experiencing until I made the two server side changes (account/policy config) noted in my answer. I also did not test with the "C:\..." file reference format but instead used a shared folder from the same server for testing. – Daniel Jul 26 '17 at 15:55
  • Oh I didn't see the updated info since yesterday (I started my reply last night and got derailed). Very cool, @Daniel. This is why I love this site! – Lucky Jul 26 '17 at 16:25
  • Hah yeah, never actually done this but it was an interesting experiment to try. – Daniel Jul 26 '17 at 17:25