Search SSRS Reports for Keywords

2022-05-13 · by Hannah Vernon · in tools

Microsoft’s SQL Server Reporting Services, colloquially known as SSRS, provides a great way to expose data from SQL Server in human-consumable form, typically via a web site. Generally, it’s a good idea to use a stored procedure as the source of data for SSRS Reports. In a large SSRS environment, it can be challenging to determine which reports use a specific stored procedure.

This code provides details about SSRS Reports where the definition of the report includes any specific keyword, such as the name of a stored procedure.

Fashion Plate (Walking Dress) by John Bell, 1822

You’ll need access to the ReportServer database to run this code.

USE [ReportServer];
GO

/*
    Searches ReportServer item definitions for the @search_term

    Hannah Vernon   2022-05-11
*/

DECLARE @search_term nvarchar(max) = N'%account_summary_by_age%';

SELECT
      [item_name]               = c.[Name]
    , [path]                    = c.[Path]
    , [description]             = c.[Description]
    , [is_hidden]               = c.[Hidden]
    , [parent_item_id]          = COALESCE(
                                            (
                                                SELECT TOP(1) pc.[Name] 
                                                FROM dbo.[Catalog] pc 
                                                WHERE pc.ItemID = c.ParentID
                                            )
                                          , N'')
    , [object_type] =
                                CASE c.[Type]
                                    WHEN 1 THEN 'Folder'
                                    WHEN 2 THEN 'Report'
                                    WHEN 3 THEN 'File'
                                    WHEN 4 THEN 'Linked Report'
                                    WHEN 5 THEN 'Data Source'
                                    WHEN 6 THEN 'Report Model'
                                    WHEN 7 THEN 'Report Part'
                                    WHEN 8 THEN 'Shared Data Set'
                                    WHEN 9 THEN 'Image'
                                    ELSE CONVERT(varchar(20), c.[Type])
                                END
    , [report_definition_xml]   = CONVERT(xml, c.[Content])
    , [link_source_id]          = c.[LinkSourceID]
    , [created_by]              = uc.[UserName]
    , [creation_date]           = c.[CreationDate]
    , [modified_by]             = um.[UserName]
    , [modified_date]           = c.[ModifiedDate]
    , [item_id]                 = c.[ItemID]
FROM dbo.[Catalog] c
    INNER JOIN dbo.[Users] AS uc ON c.[CreatedByID] = uc.[UserID]
    INNER JOIN dbo.[Users] AS um ON c.[ModifiedByID] = um.[UserID]
WHERE c.[Content] IS NOT NULL
    AND CONVERT(nvarchar(max), CONVERT(xml, c.[Content], 0), 0) LIKE @search_term
ORDER BY c.[Name];

USE [ReportServer];

Searches ReportServer item definitions for the @search_term

Hannah Vernon 2022-05-11

DECLARE @search_term nvarchar(max) = N'%account_summary_by_age%';

SELECT

[item_name] = c.[Name]

, [path] = c.[Path]

, [description] = c.[Description]

, [is_hidden] = c.[Hidden]

, [parent_item_id] = COALESCE(

(

SELECT TOP(1) pc.[Name]

FROM dbo.[Catalog] pc

WHERE pc.ItemID = c.ParentID

)

, N'')

, [object_type] =

CASE c.[Type]

WHEN 1 THEN 'Folder'

WHEN 2 THEN 'Report'

WHEN 3 THEN 'File'

WHEN 4 THEN 'Linked Report'

WHEN 5 THEN 'Data Source'

WHEN 6 THEN 'Report Model'

WHEN 7 THEN 'Report Part'

WHEN 8 THEN 'Shared Data Set'

WHEN 9 THEN 'Image'

ELSE CONVERT(varchar(20), c.[Type])

END

, [report_definition_xml] = CONVERT(xml, c.[Content])

, [link_source_id] = c.[LinkSourceID]

, [created_by] = uc.[UserName]

, [creation_date] = c.[CreationDate]

, [modified_by] = um.[UserName]

, [modified_date] = c.[ModifiedDate]

, [item_id] = c.[ItemID]

FROM dbo.[Catalog] c

INNER JOIN dbo.[Users] AS uc ON c.[CreatedByID] = uc.[UserID]

INNER JOIN dbo.[Users] AS um ON c.[ModifiedByID] = um.[UserID]

WHERE c.[Content] IS NOT NULL

AND CONVERT(nvarchar(max), CONVERT(xml, c.[Content], 0), 0) LIKE @search_term

ORDER BY c.[Name];

Thanks for visiting our site, and be sure to check out the other tools we’ve made available.

Tags: ssrs

Search SSRS Reports for Keywords

Related

Leave a ReplyCancel reply

Search

Categories

Pages

Meta