question

eghetto avatar image
eghetto asked

Retrieve Dataset Definitions from SSRS Report

Hi! I would like to see all dataset defintions of a SSRS Report which is deployed on my report server. How can I do this (with powershell)? thanks!
ssrs-2008-r2powershelldatasetdefinition
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

eghetto avatar image
eghetto answered
He we are with Powershell. Idea given by Usman Butt. See also: Allen White - [ http://sqlblog.com/blogs/allen_white/archive/2012/10/16/search-for-a-query-in-rdl-files-with-powershell.aspx][1] #Set variables: $reportserver = "myServer"; $reportFolderPath = "/myFolder" #------------------------------------------------------------------------ $url = "http://$($reportserver)/reportserver/reportservice2005.asmx?WSDL"; $ssrs = New-WebServiceProxy -uri $url -UseDefaultCredential $reports = $ssrs.ListChildren($reportFolderPath, $false) | Where-Object {$_.Type -eq 'Report'} $reports | ForEach-Object { $reportPath = $_.path Write-Host "Report: " $reportPath [byte[]]$defbytes = $ssrs.GetReportDefinition($_.path); $stream = New-Object System.IO.MemoryStream(, $defbytes) $xmldoc = New-Object System.Xml.XmlDocument $xmldoc.Load($stream) $DataSets = $xmldoc.Report.DataSets.Dataset ForEach($ds in $DataSets){ Write-Host "Dataset: " $ds.Name " --> " $ds.Query.CommandText } Write-Host "------------------------" } [1]: http://sqlblog.com/blogs/allen_white/archive/2012/10/16/search-for-a-query-in-rdl-files-with-powershell.aspx
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Usman Butt avatar image
Usman Butt answered
@eghetto I know you need a powershell script but just for the sake of having a TSQL solution (one can read the RDL file stored in XML format) something like DECLARE @xml xml SELECT @xml = CONVERT(xml, BulkColumn, 2) FROM OPENROWSET(Bulk 'C:\ReportName.rdl', SINGLE_BLOB) [rowsetresults]; ;WITH XMLNAMESPACES(DEFAULT ' http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition' ,' http://schemas.microsoft.com/SQLServer/reporting/reportdesigner' AS rd ) SELECT a.r.value('(CommandText)[1]','varchar(4000)') QueryText, y.r.value('(@Name)[1]','varchar(4000)') DataSetName, z.r.value('(@Name)[1]','varchar(4000)') FieldName, z.r.value('(DataField)[1]','varchar(4000)') DataField, z.r.value('(rd:TypeName)[1]','varchar(4000)') DataField FROM @xml.nodes('//Report/DataSets') x (r) CROSS APPLY x.r.nodes('DataSet') y(r) CROSS APPLY y.r.nodes('Fields/Field') z(r) CROSS APPLY y.r.nodes('Query')a(r) But this reminds me that I should better start learning powershell ASAP ;)
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1. Thanks Usman! Your xml idea led me to the final solution!
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.