Extract Report RDL with PowerShell

With all the new features in recent versions of SQL Server, I've been helping clients with upgrading their SQL Server environments lately. One of the upgrade requirements is to upgrade SQL Server Reporting Services (SSRS) as well, and because there's no good way to automate the movement of the report security from one Reporting Services instance to another (I've tried, believe me, I've tried), the best way to do this is to restore the ReportServer database from the older version of SQL Server to the new one.

Once you relocate the database and apply the original encryption key to the new instance, you need to change the existing data sources to point to the new locations of the source databases, and that may require some adjustment to the queries in the reports. (While it would be nice if everyone used stored procedures in all their reports, I've yet to find a case where that was true.)  The quickest way to get the reports up and running after an upgrade is to use a text editor and change the query directly in the RDL (Report Definition Language) file for the report, and upload the modified RDL file to Reporting Services.

Of course, the best way is to get the report developer to make the change, but in a large upgrade project this can take more time and resources than may be available. Therefore, I change the RDL file and let the team know what reports needed to be changed, and what the code is for the new query, and let them work with the report developers to implement those changes.

Now, while Reporting Services provides an easy way to upload an RDL file into Reporting Services, it doesn't provide an easy way to extract the RDL file. Fortunately, you can use the ReportService2010 web service to do this.

First, you need to connect to the web service, and you do this using the WSDL (Web Service Definition Language) interface to the service, like this.

$url = 'http://localhost/ReportServer/ReportService2010.asmx?WSDL'$ssrs = New-WebServiceProxy -uri $url -UseDefaultCredential -Namespace "ReportingWebService"

The ReportService2010 web service has a method called ListChildren() that allows you to work through the folder structure in Reporting Services. The easiest way to know what reports exist and what folder structure they're under, is by querying the Catalog table ReportServer database. There's a column called 'Type', and a value of 2 indicates a report, so I run this query.

SELECT PathFROM dbo.CatalogWHERE Type = 2

This returns a list of each report defined in the instance of Reporting Services, and its path.

Path/AdventureWorks/Person Reports/Contacts/AdventureWorks/Product Reports/Products/AdventureWorks/Sales Reports/Stores/Performance Reports/Performance Analysis Reports/ComparativeAnalysis/Performance Reports/Performance Analysis Reports/PerformanceAnalysis/Performance Reports/Resource Utilization Reports/DatabaseSpaceAnalysis/Performance Reports/Resource Utilization Reports/DiskUsageAnalysis

Now that you know how to find all the reports, you can use that Path value to extract the report you need to change.

Let's say you need to change a query in the ComparativeAnalysis report. The first thing you'll do is use the web service ListChildren() method to get to the report. This method returns a collection, so after getting the results, you pipe the collection to a where-object cmdlet, and look for the object with the report name you want to get the actual report.

$reports = $ssrs.ListChildren('/Performance Reports/Performance Analysis Reports', $false)$r = $reports | where-object {$_.Name -eq 'ComparativeAnalysis'}

Now, the variable $r contains the report object. Using the web services GetItemDefinition() method you can extract the report definition.

$def = $ssrs.GetItemDefinition($r.path)

Finally, using the File.OpenWrite method from the .NET System.IO namespace, you can write the report definition to a file of our choice.

$stream = [system.io.file]::OpenWrite('c:WorkComparativeAnalysis.rdl')$stream.write($def, 0, $def.length)$stream.close()

The RDL file is an XML file with the complete report definition, and you can now edit the query within the file, then upload the modified file back to Reporting Services to implement the change. While this is outside of a proper software development lifecycle, it does enable you to implement the changes necessary to quickly implement the upgrade project. With proper documentation and communication of the changes made, the report developers can make the corresponding changes as time permits.