Need to fetch sites reports that uses SSRS reports using PowerShell in SharePoint 2013

sushma Kumari 41 Reputation points
2023-07-11T14:35:43.1166667+00:00

Hello Team,

I need to fetch all sites in web application that uses SSRS report using PowerShell. These data I needed for content migration in SharePoint 2019.

Please response asap

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,321 questions
SharePoint
SharePoint
A group of Microsoft Products and technologies used for sharing and managing content, knowledge, and applications.
9,211 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Yanli Jiang - MSFT 20,221 Reputation points Microsoft Vendor
    2023-07-12T07:53:54.3933333+00:00

    Hi @sushma Kumari ,

    According to your description, this problem has more to do with SQL. I added the relevant tag, hoping that relevant engineers can see it and give a more professional solution to this issue.

    Based on my research, I found a script for your reference:

    Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
    
    $webapp = Get-SPWebApplication "http://yourwebapplicationurl"
    foreach ($site in $webapp.Sites)
    {
        foreach ($web in $site.AllWebs)
        {
            $reportServerUrl = $web.Site.WebApplication.GetResponseUri([Microsoft.SharePoint.Administration.SPUrlZone]::Default).AbsoluteUri + "/_vti_bin/ReportServer/ReportService2010.asmx"
            $rs = New-WebServiceProxy -Uri $reportServerUrl -UseDefaultCredential
            $reports = $rs.ListChildren("/", $true) | Where-Object { $_.TypeName -eq "Report" }
            if ($reports.Count -gt 0)
            {
                Write-Host "Site URL: $($web.Url)"
                Write-Host "Reports:"
                foreach ($report in $reports)
                {
                    Write-Host " - $($report.Path)"
                }
            }
            $web.Dispose()
        }
        $site.Dispose()
    }
    

    This script will iterate through all sites and webs in the web application, and for each web, it will retrieve the Report Server URL and use it to get a list of all reports. If there are any reports, it will output the site URL and a list of the reports.

    Note that this script assumes that the SSRS reports are deployed to the default Report Server URL (/_vti_bin/ReportServer/ReportService2010.asmx). If your SSRS reports are deployed to a different URL, you will need to modify the script accordingly.

    And there is a article, hope it can give you some inspiration:

    https://learn.microsoft.com/en-us/sql/reporting-services/subscriptions/manage-subscription-owners-and-run-subscription-powershell?view=sql-server-ver16


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.


  2. Yanli Jiang - MSFT 20,221 Reputation points Microsoft Vendor
    2023-07-13T09:09:53.6666667+00:00

    Hi @sushma Kumari ,

    Sorry to hear that, please try this:

    Add-PSSnapin Microsoft.SharePoint.PowerShell -ErrorAction SilentlyContinue
    
    $webApp = Get-SPWebApplication "http://sp19:90"
    foreach ($site in $webApp.Sites)
    {
        foreach ($web in $site.AllWebs)
        {
            $ssrsReports = $web.Lists | Where-Object {$_.BaseTemplate -eq "ReportLibrary" -and $_.RootFolder.Name -eq "Forms"} | Select-Object -ExpandProperty ParentWebUrl
            Write-Host "Site: $($web.Url) - SSRS Reports: $($ssrsReports)"
            $web.Dispose()
        }
        $site.Dispose()
    }
    

    The result:

    07134

    Please make sure that the account has permission to the site.

    User's image


    If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

    Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.