x

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!

more ▼

asked Oct 12, 2012 at 02:13 PM in Default

avatar image

eghetto
2.1k 18 23 30

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

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

 #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 "------------------------" 
  }

more ▼

answered Nov 01, 2012 at 01:27 PM

avatar image

eghetto
2.1k 18 23 30

(comments are locked)
10|1200 characters needed characters left

@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 ;)

more ▼

answered Oct 15, 2012 at 10:14 AM

avatar image

Usman Butt
13.9k 6 13 21

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

Nov 01, 2012 at 01:24 PM eghetto
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x109
x48
x9
x5

asked: Oct 12, 2012 at 02:13 PM

Seen: 3117 times

Last Updated: Nov 01, 2012 at 01:27 PM

Copyright 2016 Redgate Software. Privacy Policy