Specifying Paths to External Items (Report Builder and SSRS) in SharePoint with PowerShell

[2015-Dec-24] My story: two SSRS items (main report and its sub-report) need to be deployed to SharePoint. Trivial task, and even if I need to locate sub-report to a different SharePoint document library or folder I can easily do that during a development phase or using Report Builder after deployment by specifing relative or absolute path for sub-report within a definition of the main report. And here is a good MSDN resource about this - Specifying Paths to External Items (Report Builder and SSRS).







But with multiple environments to deploy the very same set of SSRS items to adjust will be a time consuming task. However we can prepare and adjust SSRS XML files during the time of deployment and PowerShell is one of the many ways to achieve that.

Here is a top section of the main SSRS RDL file:



And here how this very same section may look like with the absolute path for its sub-report in a targeted deployment SharePoint environment:



And portion of a PowerShell script that does it all is very simple:



So, at the end of the day, it's a win-win situation both for Development and Deployment teams:  less time for deployment and more time to develop other things.


[2015-Dec-30]  A brief update for the PowerShell script. I needed to make it more flexible to support sub-report paths update wherever they are located within their main SSRS report. So I had to change my XML way of dealing with the RDL report file to a text file way (XML file is still a text file :-).

And I changed this part of the code:

# update of the connection string to the found sourcing Excel files

[xml] $xdoc = get-content $rptReference

# Getting to the $rsr_environment XML block

$SubReports = $xdoc.Report.ReportSections.ReportSection.body.ReportItems.Subreport

foreach($SubReport in $SubReports)

{

$SubReport_New = $SubReport

$SubReport_New.ReportName = $targetDataSources+"/"+$SubReportFolder+"/"+$SubReport_New.ReportName+".rdl"

$xdoc.Report.ReportSections.ReportSection.body.ReportItems.ReplaceChild($SubReport_New, $SubReport)

}

                   

$xdoc.Save($rptReference) 

To this:

(Get-Content $rptReference).replace('<ReportName>', '<ReportName>'+$targetDataSources+"/"+$SubReportFolder+"/") | Set-Content $rptReference

(Get-Content $rptReference).replace('</ReportName>', '.rdl</ReportName>') | Set-Content $rptReference 

And it worked like a magic, regardless of how many sub SSRS reports a main report could have and their locations in there!