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 stringDim Request As ObjectDim ff As IntegerDim rc As stringDim UName As String, Upass As StringUName = “specificare qui Utente che accede al report server”Upass = “specificare qui Password Utenza”Dim Result As BooleanResult = FalseRequest = CreateObject(“WinHttp.WinHttpRequest.5.1”)Request.Open(“GET”, url)Request.SetCredentials (UName, Upass,0)Request.Sendrc = Request.StatusText.toString()return rcEnd Function
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:
