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

Rispondi

Inserisci i tuoi dati qui sotto o clicca su un'icona per effettuare l'accesso:

Logo WordPress.com

Stai commentando usando il tuo account WordPress.com. Chiudi sessione / Modifica )

Foto Twitter

Stai commentando usando il tuo account Twitter. Chiudi sessione / Modifica )

Foto di Facebook

Stai commentando usando il tuo account Facebook. Chiudi sessione / Modifica )

Google+ photo

Stai commentando usando il tuo account Google+. Chiudi sessione / Modifica )

Connessione a %s...