Find SSRS reports with custom data source

• OkReboots

  Old Hand

  Points: 332

  More actions

  February 24, 2021 at 10:09 pm

  #3849789

  Greetings, I've found a few threads similar to what I'm looking for but not exactly.  I have a couple hundred reports in SSRS, and most of them use a "Shared" data source, but a small handful of them have a "Custom" data source.  I am a novice with SQL.

  I just need to find out which ones don't use a shared data source.  Does anyone have a query I can use to just return a list of reports that use a custom data source, rather than a shared one?  Thank you!

• ratbak

  Default port

  Points: 1407

  More actions

  February 24, 2021 at 11:13 pm

  #3849817

  SELECT
      reports.name AS ReportName,
      Datasource.Name AS datasource
  FROM dbo.Catalog reports
  INNER JOIN dbo.DataSource ON DataSource.ItemID = reports.ItemID
  WHERE reports.Type = 2 
  AND NOT EXISTS (SELECT * FROM dbo.Catalog datasources 
                  WHERE datasources.ItemID = DataSource.Link 
                  -- 5 is shared
                  AND datasources.Type = 5
                  );