Post reply

Find SSRS reports with custom data source

  • 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!

  • 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
                    );

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply