When taking on a major data migration project it’s important to know what queries are running against your data source from the reports being run. The process of opening up each report to see the queries run can be tedious and time-consuming. Fortunately, with PowerShell, the task is infinitely easier.
About four years ago I wrote an article for the old SQLMag website describing how to extract a Reporting Services RDL (Report Definition Language) file using PowerShell. In the article I described how to access Reporting Services using the WSDL (Web Service Definition Language) interface, and I wrote the retrieved definition to a file via the [system.io.file] object write method.
In this case I don’t need the RDL file, but I do need to reach into the definition to extract the queries used in the reports. Let’s get started.
First we’ll connect to the web service, and then we’ll use the Invoke-SQLCmd2 cmdlet from the wonderful dbatools project to get the list of reports on the reporting server. We also need to initialize two variables, a path variable for each folder we find, so we don’t list the folder contents each time we pull a report definition, and an empty collection variable to store our resultset.
Next we’ll iterate through the reports we found. The results of the query we ran against the ReportServer database returns a DataRow object, and the actual path value we need is stored in the Path property in the DataRow object. We store that property in a string variable, and then use the Split-Path cmdlet to separate the parent folder structure from the report name itself. Note that Reporting Services uses forward slashes (/) to separate the levels, but Split-Path converts those to backslashes (\), so we use the -replace parameter to convert them back.
As we get to each new folder we need to use the ListChildren method to get the list of reports in that folder. Next, we pull the report object for the report that matches the current report name. Once we have that object, we use the GetItemDefinition() method to get the report definition.
The report definition is returned as a byte array, and we need to get it to an XML variable, and that takes a couple of steps. First we use the [System.Text.Encoding]::ASCII.GetString() method to convert the byte array to a string, and then, because that string sometimes contains some lead characters we don’t need, we index to the first occurrence of the ‘<‘ character, which is the start of the actual XML report definition. We then cast that substring to a new XML variable.
PowerShell makes it quite easy to navigate the XML we’ve received. The queries are stored in DataSet objects, and the actual query text is in the CommandText element under the DataSet element. To get to the DataSet collection we just separate the element hierarchy with ‘.’ and navigate directly there ($dsl = $rdl.Report.DataSets.DataSet).
Once we have the DataSet list ($dsl) we iterate through each DataSet and add each query to our resultset. I like to define a system.object variable and add NoteProperty objects for each property I want in the set. Then, when I’ve added everything I want, I add that object to the collection I defined at the beginning of the script.
The last step in the script is to use Doug Finke’s brilliant Export-Excel cmdlet, which takes the contents of my result collection and plugs it into an Excel spreadsheet. (A great feature of this cmdlet is that I don’t need to have Excel installed on the machine where I run the cmdlet!)
Now I have a spreadsheet containing all the queries coded into all the reports in my reporting services server. (Feel free to add additional properties/columns for the report parameters. I didn’t need them.) It’s far easier to evaluate the queries I need to review this way than it would be to open every report and drill into each query, don’t you think?