Tuesday, August 23, 2016

Set SSRS Report Data Source Programmatically

Set SSRS Report Data Source Programmatically


 This script is designed for SSRS 2008 R2 and 2012 in SharePoint 2010/2013 Integrated mode, it will require tweaking for native mode or previous versions. 

This script will accept a SharePoint Document Library url and set the DataSource url for all the reports in that library.


#------------------------------------------------------------------------------------------- 
# Name:            Set-SSRSDataSourceInfo 
# Description:     This script will set the data source url for all reports in a doclib 
# Usage:        Run the function with the DocLibUrl,DataSourceName and DataSourceURL parameters 
# By:             Ivan Josipovic, softlanding.ca 
#------------------------------------------------------------------------------------------- 
Function Set-SSRSDataSourceInfo ($DocLibUrl,$DataSourceName,$DataSourceURL){ 
$DocLibUrl = $DocLibUrl.Replace("%20"," ").tolower(); 
if ($docliburl.EndsWith("/")){  
    $docliburl = $docliburl.Substring(0,$docliburl.length-1); 
} 
$webfound = $false$weburl = $DocLibUrl; 
 
while ($webfound -eq $false) { 
    if ($weburl.Contains("/")){ 
        $weburl = $weburl.Substring(0,$weburl.LastIndexOf("/")); 
        $web = get-spweb -identity $weburl -ea 0; 
        if ($web -ne $null){ 
            $webfound = $true; 
        } 
    }else{ 
        Write-Output -ForegroundColor Red "The Web could not be found"; 
        return -1; 
    } 
} 
$doclib = $web.lists |where {$_.RootFolder.Name.tolower() -eq $docliburl.Substring($docliburl.LastIndexOf("/")+1,$docliburl.length-$docliburl.LastIndexOf("/")-1)}; 
if($doclib -eq $null){ 
Write-Output -ForegroundColor Red "The DocLib could not be found"return; 
} 
$reports = $doclib.items | where {$_.File.Name.tolower().Contains(".rdl")} 
 
$SSRSurl = "$($web.url)/_vti_bin/ReportServer/ReportService2010.asmx"$SSRS = New-WebServiceProxy -uri $SSRSurl -UseDefaultCredential; 
foreach ($report in $reports){ 
    write-output "$($web.url)/$($report.url)"; 
    $dataSources = $ssrs.GetItemDataSources("$($web.url)/$($report.url)"); 
    if ($datasources.count -gt 0){ 
        for ($i = 0; $i -lt $dataSources.count; $i++) { 
            if ($DataSources[$i].name -eq $DataSourceName){ 
                write-output "Updating $($DataSources[$i].name)"; 
                $proxyNamespace = $DataSources[$i].GetType().Namespace; 
                $DataSources[$i].Item = New-Object ("$proxyNamespace.DataSourceReference"); 
                $DataSources[$i].Item.Reference = $DataSourceURL; 
                $SSRS.SetItemDataSources("$($web.url)/$($report.url)"$DataSources[$i]) 
                write-output "Done"; 
            } 
        } 
    } 
    write-output ""; 
 
} 
} 
 
Set-SSRSDataSourceInfo -DocLibUrl "http://test.com/site/reports/Documents" -DataSourceName "BIRepository" -DataSourceURL "http://test.com/site/reports/Data Sources/BIRepository.rsds" 
Set-SSRSDataSourceInfo -DocLibUrl "http://test.com/site/reports/Documents" -DataSourceName "BIOlapStore" -DataSourceURL "http://test.com/site/reports/Data Sources/BIOlapStore.rsds"
That it!

 Hope this help,
Oumaima

Sharepoint Designer 2013, XSLT List View Options ribbon option is not showing

Sharepoint Designer 2013, XSLT List View Options ribbon option is not showing I have an ordinary Wiki Page, also tried making an Article...