Category: SQL Server

Microsoft Dynamics GP ships with a set of standard SSRS reports which includes many useful reports which clients want to use. However, these reports ship with the language set to en-US which means all the numbers could out as US dollars. For a UK based client this isn’t very useful. There are also lots of reports which would need to be updated.

I looked into this years ago with a colleague and the PowerShell script below is our solution. The script does three things:

1. Download the reports from SSRS to a folder.
2. Changes the language code.
3. Uploads the reports from the folder back into SSRS.

The script will download every rdl from SSRS maintaining the folder structure so that it is able to upload the files back into their original place.

There may be a better way of doing this, but we’re not PowerShell experts and this approach does work and has been used quite a few times now.

The script is hardcoded to convert from en-US to en-GB; I have highlighted the destination language to make finding it easy should you want to change it to another language.

There are two settings which will be prompted for when the script runs:

• ServerName which is the name of the SSRS server.
• fullFolderPath which is a folder on the local PC to which the reports will be downloaded.

/*
Created by Ian Grieve of azurecurve | Ramblings of an IT Professional (http://www.azurecurve.co.uk)
This code is licensed under the Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International (CC BY-NC-SA 4.0 Int).
*/
$ServerName = Read-Host -Prompt 'Please input a server name'
$fullFolderPath = Read-Host -Prompt 'Please input the folder path e.g. H:\SSRS Update\'

#note this is tested on PowerShell v2 and SSRS 2008 R2
[void][System.Reflection.Assembly]::LoadWithPartialName("System.Xml.XmlDocument");
[void][System.Reflection.Assembly]::LoadWithPartialName("System.IO");
 
$ReportServerUri = "http://$($ServerName)/ReportServer/ReportService2005.asmx";
$Proxy = New-WebServiceProxy -Uri $ReportServerUri -Namespace SSRS.ReportingService2005 -UseDefaultCredential ;

#check out all members of $Proxy
#$Proxy | Get-Member
#http://msdn.microsoft.com/en-us/library/aa225878(v=SQL.80).aspx
 
#second parameter means recursive
$items = $Proxy.ListChildren("/", $true) | `
         select Type, Path, ID, Name | `
         Where-Object {$_.type -eq "Report"};
 
#create a new folder where we will save the files
#PowerShell datetime format codes http://technet.microsoft.com/en-us/library/ee692801.aspx
 
#create a timestamped folder, format similar to 2011-Mar-28-0850PM
$folderName = Get-Date -format "yyyy-MMM-dd-hhmmtt";
$fullFolderName = $fullFolderPath + $folderName;
[System.IO.Directory]::CreateDirectory($fullFolderName) | out-null
 
foreach($item in $items)
{
    #need to figure out if it has a folder name
    $subfolderName = split-path $item.Path;
    $reportName = split-path $item.Path -Leaf;
    $fullSubfolderName = $fullFolderName + $subfolderName;
    if(-not(Test-Path $fullSubfolderName))
    {
        #note this will create the full folder hierarchy
        [System.IO.Directory]::CreateDirectory($fullSubfolderName) | out-null
    }
 
    $rdlFile = New-Object System.Xml.XmlDocument;
    [byte[]] $reportDefinition = $null;
    $reportDefinition = $Proxy.GetReportDefinition($item.Path);
 
    #note here we're forcing the actual definition to be 
    #stored as a byte array
    #if you take out the @() from the MemoryStream constructor, you'll 
    #get an error
    [System.IO.MemoryStream] $memStream = New-Object System.IO.MemoryStream(@(,$reportDefinition));
    $rdlFile.Load($memStream);
 
    $fullReportFileName = $fullSubfolderName + "\" + $item.Name +  ".rdl";
    #Write-Host $fullReportFileName;
    $rdlFile.Save( $fullReportFileName);
 
}

function ReplaceText($fileInfo)
 {
     if( $_.GetType().Name -ne 'FileInfo')
     {
         # i.e. reject DirectoryInfo and other types
          return
     }
     $old = 'en-US'
     $new = 'en-GB'
     (Get-Content $fileInfo.FullName) | % {$_ -replace $old, $new} | Set-Content -path $fileInfo.FullName
     "Processed: " + $fileInfo.FullName
 }
 
 function UploadReports ($reportServerName = $(throw "reportServerName is required."), 
    $fromDirectory = $(throw "fromDirectory is required."), $serverPath = $(throw "serverPath is required."))
{
    Write-Output "Connecting to $reportServerName"
    $reportServerUri = "http://{0}/ReportServer/ReportService2005.asmx" -f $reportServerName
    $proxy = New-WebServiceProxy -Uri $reportServerUri -Namespace SSRS.ReportingService2005 -UseDefaultCredential
    
    Write-Output "Inspecting $fromDirectory"
    # coerce the return to be an array with the @ operator in case only one file
    $files = @(get-childitem $fromDirectory *.rdl |where-object {!($_.psiscontainer)})
    
    $uploadedCount = 0
    
    foreach ($fileInfo in $files)
    {
        $file = [System.IO.Path]::GetFileNameWithoutExtension($fileInfo.FullName)        
        $percentDone = (($uploadedCount/$files.Count) * 100)        
        Write-Progress -activity "Uploading to $reportServerName$serverPath" -status $file -percentComplete $percentDone
        Write-Output "%$percentDone : Uploading $file to $reportServerName$serverPath"
        $bytes = [System.IO.File]::ReadAllBytes($fileInfo.FullName)
        $warnings = $proxy.CreateReport($file, $serverPath, $true, $bytes, $null)
        
        if ($warnings)
        {
            foreach ($warn in $warnings)
            {
                Write-Warning $warn.Message
            }
        }
        
        $uploadedCount += 1
    }    
}`

cd $fullFolderName

$files = Get-ChildItem . -recurse
$files | % { ReplaceText( $_ ) }

$UploadFiles = Get-ChildItem . -Recurse -Directory

foreach($uploadFolder in $uploadFiles){

      $uploadFolderPath = $uploadFolder.FullName.Replace($fullFolderName, "")
      UploadReports $ServerName "$($fullFolderName)$($uploadFolderPath)" "$($uploadFolderPath.Replace("\","/"))"  

}