WIDBA

WIDBA - The busiest DBA in Wisconsin, its all I do and I do it well

Thursday, February 14, 2013

Rye Whiskey and an SSRS Inventory Report

In my previous post, Moving SSRS 2012 with Powershell and Bourbon I covered how to change a data source for any or all the reports on the server.  That worked out so well, I poured myself a small taste of the brand new Rye Whisky from http://www.45thparallelspirits.com/ and celebrated.



The next morning I was talking through the results with our SSRS maven @Equerystrian and she requested that each report, when it had a shared data source, have the data source path listed next to the report.  I started thinking about modifying the script that changes data sources and decided I would rather have a script that is just an inventory of SSRS reports and data sources.  The basics are the same for both, but this report is much cleaner for its purpose.

The CSV file created lists the report path and name, connection string, type of data source and the path to the shared data source.

 CLS  
 $SSRS2012Server = "SSRSServer"   #SSRS Server  
 $ReportPathMatch = "*" # To specify certain reports, * for all
 $file = "C:\Users\AleAsylumMakesGoodBeer\Production_SSRS_Inventory.csv"

 #URL to the web service  
 $SSRSurl = "http://{0}/ReportServer/ReportService2010.asmx" -f $SSRS2012Server  
 # Get a handle on the service  
 $SSRS = New-WebServiceProxy -uri $SSRSurl -UseDefaultCredential;  
 # Get a collection of the reports on the server  
 $itemlist = $SSRS.ListChildren("/", $true) | Where-Object {$_.TypeName -eq "Report" -and $_.Path -like $ReportPathMatch -and $_.Path -notlike "/Users Folders*"} ;  
 Write-Host "Reports to review" $itemlist.Count  
 #Creating a report tracker  
 $Reports =@()  
 #Creates a report tracking object  
 function Add-Reports  
 {  
   param([string]$reportPath, [string]$connString, [string]$dataSourceType , [string]$dataSourcePath)  
   $report = New-Object System.Object  
   $report | Add-Member -type NoteProperty -name "Report" -value $reportPath  
   $report | Add-Member -type NoteProperty -name "Connection" -value $connString  
   $report | Add-Member -type NoteProperty -name "DataSourceType" -value $dataSourceType  
   $report | Add-Member -type NoteProperty -name "DataSourcePath" -value $dataSourcePath  
   return $report   
 }  
 #Loop Through Each Report  
 Foreach($item in $itemlist)  
 {  
   Write-Host "Checking " $item.Path  
   try  
   {  
     $dsList = $null #clearing it out explicitly  
     #Any given report may have multiple data sources, grab the collection  
     $dsList = $ssrs.GetItemDataSources($item.Path)  
     #Loop through each DS in the report  
     for($i=0;$i -le $dsList.Length-1;$i++)  
     {  
       $ds = $dsList[$i];  
       #If the DS has a reference it would appear to be a shared datasource  
       if($ds.Item.Reference -ne $null)  
       {  
         $dataSource = $ssrs.GetDataSourceContents($ds.Item.Reference);  
         $Reports += Add-Reports -reportPath $item.Path -connString $dataSource.ConnectString -dataSourceType "Shared" -dataSourcePath $ds.Item.Reference  
       }  
       else #embedded datasource  
       {  
         $dataSource = $ds.Item  
         $Reports += Add-Reports -reportPath $item.Path -connString $dataSource.ConnectString -dataSourceType "Embedded" -dataSourcePath ""  
       }  
     } #for each datasource   
   }  
   catch{  
     Write-Host "Error on " $item.Path $Error[0]  
     $Error.Clear();  
   }  
 } # Foreach Report  
 $Reports | Export-Csv $file -Force  

No comments:

Post a Comment