SSRS Report with image, if not exist the image?

When in a Reporting Services report you want to add an external image Often you find yourself in a situation where the image does not yet exist or has been removed , causing the appearance of an unsightly red “X” in the report .

To avoid this situation , you can implement a script in VBA within a report that checks if the image exists or not , and then set a rule on the image visibility , hiding it if the script returns a negative result .

To add a script in vba a SSRS report should go into the properties of the report- > Code:

Function URLExists(url As String) As string
 Dim Request As Object
    Dim ff As Integer
    Dim rc As string
 Dim UName As String, Upass As String
 UName = “specificare qui Utente che accede al report server”
 Upass = “specificare qui Password Utenza”​​​

Dim Result As Boolean
Result = False
 Request = CreateObject(“WinHttp.WinHttpRequest.5.1”)
 Request.Open(“GET”, url)
 Request.SetCredentials (UName, Upass,0)
     Request.Send
      rc = Request.StatusText.toString()
return rc
End Function
At this point in the propriety of image select Visibility e add value:

=iif(code.URLExists(“Https://…Immagine.jpg”) = “OK”, False, True)

As a final step , so that the script works , you must ensure that this code written in vba can access without restriction to an external element such as may be a web site .

We must therefore change the following three xml files , changing permissions from ” Execution” to ” FullTrust ” as follows :

  • RSPreviewPolicy  in this path: C:\Program Files (x86)\Microsoft Visual Studio 9.0\Common7\IDE\PrivateAssemblies
  • rsmgrpolicy in this path:  C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportManager
  • rssrvpolicy in this path:  C:\Program Files\Microsoft SQL Server\MSRS10_50.MSSQLSERVER\Reporting Services\ReportServer

and change in this area from Execution to 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>

<CodeGroup

class=”FirstMatchCodeGroup”

version=”1″

PermissionSetName=”FullTrust”

Description=”This code group grants MyComputer code Execution permission. “>

<IMembershipCondition

class=”ZoneMembershipCondition”

version=”1″

Zone=”MyComputer” />

</CodeGroup>

After that if the image not exist the report show the text value:

 

ImageReplaceByDescription

Annunci

Error vi​​sual st​​udio 2012 Boolean DTS Design

Sometimes happens to have this error:

Method not found: ‘Boolean
Microsoft.SqlServer.Dts.Design.VisualStudio2012Utils.IsVisualStudio2012ProInstalled()’.
(Microsoft.DataTransformationServices.VsIntegration)

If it happens, you have a problem with Visual studio, to repare you have 3 different Solution:

  1. Delete and install Visual studio
  2. Install il sp2
  3. Use a script
To use the script you have to use visual studio 12 developer Command Prompt. If you want some info where find this tool on your server refer to this link.
At shit point, insert and launch this command, suggest you to open as administrator :

cd “C:\Program Files (x86)\Microsoft Visual Studio 11.0\Common7\IDE\PrivateAssemblies”

gacutil /if Microsoft.SqlServer.Dts.Design.dll
The assemblies position change if you haven’t install visual studio in the default folder. If all works visual studio give you back a message that the dll is correctly installed in cache.
If you use visual studio 2013 the first command is different:
cd “C:\Program Files (x86)\Microsoft Visual Studio 12.0\Common7\IDE\PrivateAssemblies”