Handy code snippits for exploring the ssrs database

/*
    Ran instances oof reports with the parameters
*/
SELECT    x.TimeStart
        ,c.Name
        ,x.USERNAME
        ,x.Parameters
        ,c.Type
        ,x.TimeDataRetrieval
        ,x.TimeProcessing
        ,x.TimeRendering
        ,x.ByteCount
        ,x.[RowCount]
        ,c.Path
       
               ,'XX' as 'Catelog_Fields_Start_Here'
               ,c.*
               ,'XX' as 'ExecutionLog_Fields_Start_Here'
               ,x.*
          FROM Catalog c
               INNER JOIN
               ExecutionLog x
                 ON c.ItemID = x.ReportID
         WHERE Type = 2 AND x.USERNAME like '%gerard%'
         order by x.TimeStart DESC
/*
    code to get xml of report from content field
*/
        
--The first CTE gets the content as a varbinary(max)
--as well as the other important columns for all reports,
--data sources and shared datasets.
WITH ItemContentBinaries AS
(
    SELECT
    ItemID,Name,[Type]
        ,CASE Type
        WHEN 2 THEN 'Report'
        WHEN 5 THEN 'Data Source'
        WHEN 7 THEN 'Report Part'
        WHEN 8 THEN 'Shared Dataset'
        ELSE 'Other'
    END AS TypeDescription
    ,CONVERT(varbinary(max),Content) AS Content
    FROM ReportServer.dbo.Catalog
    WHERE Type IN (2,5,7,8)
),
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
    SELECT
    ItemID,Name,[Type],TypeDescription
    ,CASE
        WHEN LEFT(Content,3) = 0xEFBBBF
        THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
    ELSE
            Content
        END AS Content
    FROM ItemContentBinaries
)
--The old outer query is now a CTE to get the content in its xml form only...
,ItemContentXML AS
(
    SELECT
    ItemID,Name,[Type],TypeDescription
    ,CONVERT(xml,Content) AS ContentXML
    FROM ItemContentNoBOM
)
--now use the XML data type to extract the queries, and their command types and text....
SELECT
    ItemID,Name,[Type],TypeDescription,ContentXML
    ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
    ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
FROM ItemContentXML
--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)

-- dsMain
SELECT
ReportID,
Name AS ReportName,
InstanceName,
COALESCE(C.Path, 'Unknown') AS ItemPath,
UserName,
ExecutionId,
CASE(RequestType)
  WHEN 0 THEN 'Interactive'
  WHEN 1 THEN 'Subscription'
  WHEN 2 THEN 'Refresh Cache'
  ELSE 'Unknown'
END AS RequestType, -- SubscriptionId,
Format, Parameters,
CASE(ReportAction) WHEN 1 THEN 'Render'
  WHEN 2 THEN 'BookmarkNavigation'
  WHEN 3 THEN 'DocumentMapNavigation'
  WHEN 4 THEN 'DrillThrough'
  WHEN 5 THEN 'FindString'
  WHEN 6 THEN 'GetDocumentMap'
  WHEN 7 THEN 'Toggle'
  WHEN 8 THEN 'Sort'
  WHEN 9 THEN 'Execute'
  ELSE 'Unknown'
END AS ItemAction,
TimeStart,
TimeEnd,
TimeDataRetrieval,
TimeProcessing,
TimeRendering,
CASE(Source)
  WHEN 1 THEN 'Live'
  WHEN 2 THEN 'Cache'
  WHEN 3 THEN 'Snapshot' WHEN 4 THEN 'History'
  WHEN 5 THEN 'AdHoc'
  WHEN 6 THEN 'Session'
  WHEN 7 THEN 'Rdce'
  ELSE 'Unknown'
END AS Source,
CASE (Status)

  WHEN 'rsHttpRuntimeClientDisconnectionError' THEN 'HttpRuntimeClientDisconnectionError'
  WHEN 'rsReportServerDatabaseError' THEN 'ReportServerDatabaseError'
  WHEN 'rsProcessingAborted' THEN 'Processing Aborted'
  WHEN 'rsSuccess' THEN 'Success'
  WHEN 'rrRenderingError' THEN 'Rendering Error'
  ELSE 'Unknown'
END AS Status,
ByteCount,
[RowCount],
AdditionalInfo
FROM ExecutionLogStorage EL WITH(NOLOCK)
LEFT OUTER JOIN Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)
WHERE ReportID = @ReportList
;

--dsSSRSReportList
Select    ItemID
        ,[Name]  
From    [Catalog] c
Where  [Type] = 2
AND [Hidden] <> 1
AND SubString([Path],1,Len([Path])-(CharIndex('/',Reverse([Path]))-1))  = @FolderList
ORDER BY c.[Name]

--dsSSRSFolderList
--Returns a list of the folder structure on SSRS Server
SELECT DISTINCT SubString([Path],1,Len([Path])-(CharIndex('/',Reverse([Path]))-1)) As [Path] From    [Catalog] c



create VIEW [dbo].[vw_REPORT_JWS_SSRS_ReportUsage]
AS
       SELECT    ReportName,[Path]
             ,Runs
             , DATEDIFF(dd, (select Min(timestart)  from ExecutionLogStorage),GETDATE())+1 AS DaysHistoryPresent
             ,CASE
                WHEN  DATEDIFF(dd, (select Min(timestart)  from ExecutionLogStorage),GETDATE())+1 = 0 OR Runs = 0 THEN 0
                ELSE  Runs/(DATEDIFF(dd, FirstDay,LastDay)+1)
             END AS ApproxRunsPerDay
             , Runs/(DATEDIFF(dd, FirstDay,LastDay)+1)*30  AS ApproxRunsPerMonth
       FROM
       (
          SELECT
             c.Name AS ReportName
             ,c.Path
             ,Count(1) AS Runs
             ,MIN(el.TimeStart) AS FirstDay
             ,MAX(el.TimeStart) AS LastDay
          FROM ExecutionLogStorage EL WITH(NOLOCK)
             LEFT OUTER JOIN Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)
          WHERE   el.Source <> 6
          AND [Name] NOT IN ('Global', 'Default','dsRptFmt','dsRptFmt_Internal','Dummy')
          GROUP BY c.Name,c.Path
       ) AS a
       ORDER BY
       CASE
          WHEN DATEDIFF(dd, FirstDay,LastDay)+1 = 0.000 OR Runs = 0 THEN 0.000
          ELSE (Runs/ DATEDIFF(dd, (select Min(timestart)  from ExecutionLogStorage),GETDATE())+1)
       END DESC
GO
        

SSRS get the run history and parameters used for individual report

USE [ReportServer]
/*
useful code for getting the run history and parameters used of a report
*/
DECLARE @ReportName AS VARCHAR(MAX)
SET @ReportName = '%Report1%'--report name hers surrounded by wildcards

SELECT * FROM
(
    SELECT
    InstanceName,
    COALESCE(C.Path, 'Unknown') AS ItemPath,
    UserName,
    ExecutionId,
    CASE(RequestType)
      WHEN 0 THEN 'Interactive'
      WHEN 1 THEN 'Subscription'
      WHEN 2 THEN 'Refresh Cache'
      ELSE 'Unknown'
    END AS RequestType, -- SubscriptionId,
    Format, Parameters,
    CASE(ReportAction) WHEN 1 THEN 'Render'
      WHEN 2 THEN 'BookmarkNavigation'
      WHEN 3 THEN 'DocumentMapNavigation'
      WHEN 4 THEN 'DrillThrough'
      WHEN 5 THEN 'FindString'
      WHEN 6 THEN 'GetDocumentMap'
      WHEN 7 THEN 'Toggle'
      WHEN 8 THEN 'Sort'
      WHEN 9 THEN 'Execute'
      ELSE 'Unknown'
    END AS ItemAction,
    TimeStart,
    TimeEnd,
    TimeDataRetrieval,
    TimeProcessing,
    TimeRendering,
    CASE(Source)
      WHEN 1 THEN 'Live'
      WHEN 2 THEN 'Cache'
      WHEN 3 THEN 'Snapshot' WHEN 4 THEN 'History'
      WHEN 5 THEN 'AdHoc'
      WHEN 6 THEN 'Session'
      WHEN 7 THEN 'Rdce'
      ELSE 'Unknown'
    END AS Source,
    Status,
    ByteCount,
    [RowCount],
    AdditionalInfo
    FROM ExecutionLogStorage EL WITH(NOLOCK)
    LEFT OUTER JOIN Catalog C WITH(NOLOCK) ON (EL.ReportID = C.ItemID)
)AS a
WHERE ItemPath like @ReportName   

Search and Locate report items the SSRS database (ie which reports did I use the SQL function i am modifying in etc)

Following code will list report objects in a Reporting Server Database. It can be modified to serarch for specific items in specific reports (ie which reports contain reference to a certain data set or paramater list etc)



--The first CTE gets the content as a varbinary(max)
--as well as the other important columns for all reports,
--data sources and shared datasets.
WITH ItemContentBinaries AS
(
  SELECT
     ItemID,Name,[Type]
    ,CASE Type
       WHEN 2 THEN 'Report'
       WHEN 5 THEN 'Data Source'
       WHEN 7 THEN 'Report Part'
       WHEN 8 THEN 'Shared Dataset'
       ELSE 'Other'
     END AS TypeDescription
    ,CONVERT(varbinary(max),Content) AS Content
  FROM ReportServer.dbo.Catalog
  WHERE Type IN (2,5,7,8)
),
--The second CTE strips off the BOM if it exists...
ItemContentNoBOM AS
(
  SELECT
     ItemID,Name,[Type],TypeDescription
    ,CASE
       WHEN LEFT(Content,3) = 0xEFBBBF
         THEN CONVERT(varbinary(max),SUBSTRING(Content,4,LEN(Content)))
       ELSE
         Content
     END AS Content
  FROM ItemContentBinaries
  where Name like '%%' --add filter here
)
--The old outer query is now a CTE to get the content in its xml form only...
,ItemContentXML AS
(
  SELECT
     ItemID,Name,[Type],TypeDescription
    ,CONVERT(xml,Content) AS ContentXML
 FROM ItemContentNoBOM
)
--now use the XML data type to extract the queries, and their command types and text....
SELECT
     ItemID,Name,[Type],TypeDescription,ContentXML
    ,ISNULL(Query.value('(./*:CommandType/text())[1]','nvarchar(1024)'),'Query') AS CommandType
    ,Query.value('(./*:CommandText/text())[1]','nvarchar(max)') AS CommandText
FROM ItemContentXML

--Get all the Query elements (The "*:" ignores any xml namespaces)
CROSS APPLY ItemContentXML.ContentXML.nodes('//*:Query') Queries(Query)