Search This Blog

Thursday, December 24, 2015

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!



2 comments:

  1. Thanks for the efforts on gathering useful content on Report Builder and SSRS.

    ReplyDelete
  2. Nice Blog post.More Information Blog. Very Useful Information as Given in this Blog.

    SQL Server Reporting Services (SSRS) is a server-based report generation software system from Microsoft. It is part of suite of Microsoft SQL Server services, including SSIS (SQL Server Integration Services) and SSAS (SQL Server Analysis Services) Readmore..

    ReplyDelete