Finding SSRS Reports Without A Data Source

When deploying reports to different environments it is possible to have reports not link to a data source.  These reports are unusable and it can be time consuming to find them if there are a lot of reports.  I knew there had to be a query to identify these reports.  I thought I could search for ‘SSRS reports without a data source’ and find the queries.  Nothing came up in the first couple of pages of search results so that led to this blog post.

Case 1) Find reports where the data source doesn’t exist.  This could happen if a data source is deleted. Look for ItemIDs that exist in the catalog table but not the data sources table. 

SELECT Type, ItemID, Name, Path
FROM [ReportServer].[dbo].[Catalog]
WHERE  ItemID NOT IN (SELECT [ItemID]  FROM [ReportServer].[dbo].[DataSource])

On SQL Server 2012 the Type column is available to select only reports and shared data sets.

SELECT Type, ItemID, Name, Path
FROM [ReportServer].[dbo].[Catalog]
WHERE Type IN (2,8) AND ItemID NOT IN (SELECT [ItemID]  FROM [ReportServer].[dbo].[DataSource])

Case 2) Find reports without an un-linked data source.  Look for items in the Catalog and DataSource table with a null link. 

SELECT DISTINCT Catalog.Name, Catalog.Path
  FROM [ReportServer].[dbo].[DataSource],
          [ReportServer].[dbo].[Catalog]
  WHERE DataSource.ItemID = Catalog.ItemID AND Path NOT LIKE ‘%/Data Sources/%’ AND DataSource.Link IS NULL

Here is the SQL Server 2012 version using type instead of filtering on the Path.

SELECT DISTINCT Catalog.Name, Catalog.Path
  FROM [ReportServer].[dbo].[DataSource],
          [ReportServer].[dbo].[Catalog]
  WHERE DataSource.ItemID = Catalog.ItemID AND Type <>5 AND  DataSource.Link IS NULL

Advertisements
This entry was posted in SQL Server Reporting Services and tagged , , . Bookmark the permalink.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s