SSRS / PowerBI Report Server - Update Hyperlinks in RDL via Powershell

Question

I have a number of reports on my PowerBI report server that contain hyperlinks to other reports. The URLS contain the hostname of the local and I want to update them (Either to the name of the new server or to localhost)

I have managed to pull together the following Powershell code that loops through all the reports (I've narrowed it down to a single folder for testing purposes), gets their defintion, finds all the hyperlinks and updates their value. The only thing I have not worked out is how to "save" the update I have made:

Clear-Host

$DestinationReportServerURI = "http://NewServer/reports";
$reportServerUri = "http://NewServer/reportserver/ReportService2010.asmx?wsdl";
$rs = New-WebServiceProxy -Uri $reportServerUri -UseDefaultCredential -Namespace "SSRS";
$SourceServer = "OldServer"
$DestServer = "NewServer"

$reports = Get-RsFolderContent -RsFolder '/Path/To/Reports' -ReportServerUri $DestinationReportServerURI -Recurse | Where-Object TypeName -EQ "Report";

# loop through all reports
foreach ($r in $reports)
{
    #Get the item definition (returns a byte array)
    $def = $rs.GetItemDefinition($r.Path)

    $a = [System.Text.Encoding]::ASCII.GetString($def)
    $idx = $a.IndexOf('<')
    [xml]$rdl = $a.Substring($idx,($a.length - $idx))
  
    $rdl.save("C:\Test\$($r.Name)_old.rdl") #verify what the RDL was like before update

    $namespace = @{
    rd="http://schemas.microsoft.com/SQLServer/reporting/reportdesigner"
    df="http://schemas.microsoft.com/sqlserver/reporting/2016/01/reportdefinition"
    }

    # get all the Hyperlink nodes for this report
    $hyperlinks = $rdl | Select-Xml '//df:Hyperlink' -Namespace $namespace | select -Expand Node

    foreach ($h in $hyperlinks)
    {
        if ($h.'#text' -like "*$SourceServer*")
        {
            $hypUdated = $true
            $h.'#text' = $($h.'#text').ToLower().Replace($SourceServer.ToLower(),$DestServer.ToLower())
        }
    }   

    if ($hypUdated -eq $true)
    {
        $rdl.save("C:\Test\$($r.Name).rdl")
        echo "$($r.Path) hyperlink updated"
        $b = [System.Text.Encoding]::ASCII.GetBytes($rdl)
        $rs.SetItemDefinition($r.Path,$b,$null)
    } 
};

I get

Exception calling "SetItemDefinition" with "3" argument(s): "The definition of this report is not valid or supported by this version of Reporting Services. The report definition may have been created with a later version of Reporting Services, or contain content that is not well-formed or not valid based on Reporting Services schemas. Details: Data at the root level is invalid. Line 1, position 1."

from the code above, however I save the rdl's before and after the update so I can compare them for troubleshooting purposes and Notepad++ compare plugin only reports differences on the lines where the hyperlinks have been updated. Furthermore, I can upload the updated RDL to the server manually using the GUI and it works (and the hyperlink is updated when I open it)

I have noticed if I change

$rs.SetItemDefinition($r.Path,$b,$null)

to

$rs.SetItemDefinition($r.Path,$def,$null)

(Running SetItemDefinition with the original byte array) I get no error so it must be something to do with the way I am altering it

What am I doing wrong?

SEarle1986 SEarle1986 · Accepted Answer · 2020-07-16T22:55:45

I have fixed it. The problem was that I was converting the XML type to a byte array rather than it's string representation. The solution was

foreach ($h in $hyperlinks)
{
    if ($h.'#text' -like "*$SourceServer*")
    {
        $hypUdated = $true
        $h.'#text' = $($h.'#text').ToLower().Replace($SourceServer.ToLower(),$DestServer.ToLower())
    }
}  

if ($hypUdated -eq $true)
{   
    $rdl.save("C:\Test\$($r.Name).rdl")
    Write-Host "$($r.Path) hyperlink updated"

    $b = [System.Text.Encoding]::ASCII.GetBytes($rdl.OuterXml)
    $rs.SetItemDefinition($r.Path,$b,$null)
}

SSRS / PowerBI Report Server - Update Hyperlinks in RDL via Powershell

1 Answers