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

eghetto gravatar image

eghetto
2.1k 16 18 24

(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

eghetto gravatar image

eghetto
2.1k 16 18 24

(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

Usman Butt gravatar image

Usman Butt
13.9k 6 8 14

+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.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

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:

x84
x28
x8
x5

asked: Oct 12, 2012 at 02:13 PM

Seen: 1783 times

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