SQL Server Mentalist

Hi Folks,

In this article we are going to cover How to Find the details of the given report name by querying Report Server Database.

In this post we are going to discuss following points:

• Problem Statement of SQL Script:
• Description of SQL Script:
• SQL Script Output Column
• Input Parameter of SQL Script
• SQL Script Code
• SQL Script Output Screenshot
• User Level to execute

Problem Statement of SQL Script:

How to find the details of the given report name by querying Report Server Database?

Description of SQL Script:

The procedure gives following result sets:

1. Report creation and modification.

2. Parameters.

3. Data Sources.

4. Datasets with command text and data fields.

5. Subscriptions on the report.

6. Snapshots on the report.

7. Users having access to the report and their roles.

8. Execution Log of the report if Show execution log parameter is specified as 1.

SQL Script Output Column



Input Parameter of SQL Script

Report Name- Report Name for which the details to be viewed.

ShowExecutionLog- If set to 1 , the execution log for the report also will be displayed. Default value 0.

SQL Script Code

IF OBJECT_ID('sp_HelpSSRSReport', 'P') IS NOT NULL
    DROP PROC sp_HelpSSRSReport
GO

CREATE PROC sp_HelpSSRSReport @ReportName NVARCHAR(850)
    ,@ShowExecutionLog BIT = 0
AS
DECLARE @Namespace NVARCHAR(500)
DECLARE @SQL VARCHAR(max)

SELECT @Namespace = SUBSTRING(x.CatContent, x.CIndex, CHARINDEX(
            '"', x.CatContent, x.CIndex + 7) - x.CIndex)
FROM (
    SELECT CatContent = CONVERT(NVARCHAR(MAX), CONVERT(XML, 
                CONVERT(VARBINARY(MAX), C.Content)))
        ,CIndex = CHARINDEX('xmlns="', CONVERT(NVARCHAR(MAX), 
                CONVERT(XML, CONVERT(VARBINARY(MAX), C.Content
                    ))))
    FROM Reportserver.dbo.CATALOG C
    WHERE C.Content IS NOT NULL
        AND C.Type = 2
    ) X

SELECT @Namespace = REPLACE(@Namespace, 'xmlns="', '') + ''

SELECT NAME
    ,CreatedBy = U.UserName
    ,CreationDate = C.CreationDate
    ,ModifiedBy = UM.UserName
    ,ModifiedDate
FROM Reportserver.dbo.CATALOG C
JOIN Reportserver.dbo.Users U ON C.CreatedByID = U.UserID
JOIN Reportserver.dbo.Users UM ON c.ModifiedByID = UM.UserID
WHERE NAME = @ReportName

SELECT NAME = Paravalue.value('Name[1]', 'VARCHAR(250)')
    ,Type = Paravalue.value('Type[1]', 'VARCHAR(250)')
    ,Nullable = Paravalue.value('Nullable[1]', 'VARCHAR(250)')
    ,AllowBlank = Paravalue.value('AllowBlank[1]', 
        'VARCHAR(250)')
    ,MultiValue = Paravalue.value('MultiValue[1]', 
        'VARCHAR(250)')
    ,UsedInQuery = Paravalue.value('UsedInQuery[1]', 
        'VARCHAR(250)')
    ,Prompt = Paravalue.value('Prompt[1]', 'VARCHAR(250)')
    ,DynamicPrompt = Paravalue.value('DynamicPrompt[1]', 
        'VARCHAR(250)')
    ,PromptUser = Paravalue.value('PromptUser[1]', 
        'VARCHAR(250)')
    ,STATE = Paravalue.value('State[1]', 'VARCHAR(250)')
FROM (
    SELECT C.NAME
        ,CONVERT(XML, C.Parameter) AS ParameterXML
    FROM ReportServer.dbo.CATALOG C
    WHERE C.Content IS NOT NULL
        AND C.Type = 2
        AND C.NAME = @ReportName
    ) a
CROSS APPLY ParameterXML.nodes('//Parameters/Parameter') p(
        Paravalue)

SELECT @SQL = 'WITH XMLNAMESPACES ( DEFAULT ''' + @Namespace + 
    ''', ''http://schemas.microsoft.com/SQLServer/

reporting/reportdesigner'' AS rd ) 
                SELECT  ReportName         = name 
                       ,DataSourceName     = x.value

(''(@Name)[1]'', ''VARCHAR(250)'')  
                       ,DataProvider     = x.value

(''(ConnectionProperties/DataProvider)[1]'',''VARCHAR(250)'') 
                       ,ConnectionString = x.value

(''(ConnectionProperties/ConnectString)[1]'',''VARCHAR(250)'') 
                  FROM (  SELECT C.Name,CONVERT

(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML 
                           FROM  ReportServer.dbo.Catalog C 
                          WHERE  C.Content is not null 
                            AND  C.Type  = 2 
                            AND  C.Name  = ''' 
    + @ReportName + 
    ''' 
                  ) a 
                  CROSS APPLY reportXML.nodes

(''/Report/DataSources/DataSource'') r ( x ) 
                ORDER BY name ;'

EXEC (@SQL)

SELECT @SQL = 'WITH XMLNAMESPACES ( DEFAULT ''' + @Namespace + 
    ''', ''http://schemas.microsoft.com

/SQLServer/reporting/reportdesigner'' AS rd ) 
SELECT  ReportName        = name 
       ,DataSetName        = x.value

(''(@Name)[1]'', ''VARCHAR(250)'')  
       ,DataSourceName    = x.value

(''(Query/DataSourceName)[1]'',''VARCHAR(250)'') 
       ,CommandText        = x.value

(''(Query/CommandText)[1]'',''VARCHAR(250)'') 
       ,Fields            = df.value(''(@Name)[1]'',''VARCHAR(250)'') 
       ,DataField        = df.value(''(DataField)[1]'',''VARCHAR(250)'') 
       ,DataType        = df.value(''(rd:TypeName)[1]'',''VARCHAR(250)'') 
  FROM (  SELECT C.Name,CONVERT

(XML,CONVERT(VARBINARY(MAX),C.Content)) AS reportXML 
           FROM  ReportServer.dbo.Catalog C 
          WHERE  C.Content is not null 
            AND  C.Type = 2 
            AND  C.Name = ''' 
    + @ReportName + 
    ''' 
       ) a 
  CROSS APPLY reportXML.nodes(''/Report/DataSets/DataSet'') r ( x ) 
  CROSS APPLY x.nodes(''Fields/Field'') f(df)  
ORDER BY name '

EXEC (@SQL)

SELECT Reportname = c.NAME
    ,SubscriptionDesc = su.Description
    ,Subscriptiontype = su.EventType
    ,su.LastStatus
    ,su.LastRunTime
    ,Schedulename = sch.NAME
    ,ScheduleType = sch.EventType
    ,ScheduleFrequency = CASE sch.RecurrenceType
        WHEN 1
            THEN 'Once'
        WHEN 2
            THEN 'Hourly'
        WHEN 4
            THEN 'Daily/Weekly'
        WHEN 5
            THEN 'Monthly'
        END
    ,su.Parameters
FROM Reportserver.dbo.Subscriptions su
JOIN Reportserver.dbo.CATALOG c ON su.Report_OID = c.ItemID
JOIN Reportserver.dbo.ReportSchedule rsc ON rsc.ReportID = c.
    ItemID
    AND rsc.SubscriptionID = su.SubscriptionID
JOIN Reportserver.dbo.Schedule Sch ON rsc.ScheduleID = sch.
    ScheduleID
WHERE c.NAME = @ReportName

SELECT C.NAME
    ,H.SnapshotDate
    ,S.Description
    ,ScheduleForSnapshot = ISNULL(Sc.NAME, 
        'No Schedule available for Snapshot')
    ,ScheduleType = sc.EventType
    ,ScheduleFrequency = CASE sc.RecurrenceType
        WHEN 1
            THEN 'Once'
        WHEN 2
            THEN 'Hourly'
        WHEN 4
            THEN 'Daily/Weekly'
        WHEN 5
            THEN 'Monthly'
        END
    ,sc.LastRunTime
    ,sc.LastRunStatus
    ,ScheduleNextRuntime = SC.NextRunTime
    ,S.EffectiveParams
    ,S.QueryParams
FROM Reportserver.dbo.History H
JOIN Reportserver.dbo.SnapshotData S ON H.SnapshotDataID = S.
    SnapshotDataID
JOIN Reportserver.dbo.CATALOG c ON C.ItemID = H.ReportID
LEFT JOIN Reportserver.dbo.ReportSchedule Rs ON RS.ReportID = H.
    ReportID
    AND RS.ReportAction = 2
LEFT JOIN Reportserver.dbo.Schedule Sc ON Sc.ScheduleID = rs.
    ScheduleID
WHERE C.NAME = @ReportName

SELECT C.NAME
    ,U.UserName
    ,R.RoleName
    ,R.Description
    ,U.AuthType
FROM Reportserver.dbo.Users U
JOIN Reportserver.dbo.PolicyUserRole PUR ON U.UserID = PUR.UserID
JOIN Reportserver.dbo.Policies P ON P.PolicyID = PUR.PolicyID
JOIN Reportserver.dbo.Roles R ON R.RoleID = PUR.RoleID
JOIN Reportserver.dbo.CATALOG c ON C.PolicyID = P.PolicyID
WHERE c.NAME = @ReportName
ORDER BY U.UserName

IF @ShowExecutionLog = 1
    SELECT C.NAME
        ,CASE E.Requesttype
            WHEN 1
                THEN 'Subscription'
            WHEN 0
                THEN 'Report Launch'
            ELSE ''
            END
        ,E.TimeStart
        ,E.TimeProcessing
        ,E.TimeRendering
        ,E.TimeEnd
        ,E.STATUS
        ,E.InstanceName
        ,E.UserName
    FROM Reportserver.dbo.ExecutionLog E
    JOIN Reportserver.dbo.CATALOG C ON E.ReportID = C.ItemID
    WHERE C.NAME = @ReportName
    ORDER BY E.TimeStart DESC
GO

SQL Script Output Screenshot



User Level to execute

300

Hope you will like to How to Find the details of the given report name by querying Report Server Database.

If you really like reading my blog and understood at least few thing then please don’t forget to subscribe my blog.

If you want daily link and analysis or interesting link go to following website which will give @ your inbox please subscribe our following link resource blog :

Link Resource Website

For More information related to BI World visit my Mentalist Blog

SQL Server Mentalist >> SQL Learning Blog

Business Intelligence Mentalist >> BI World

Infographic Mentalist >> Image worth explaining thousand Words

Microsoft Mentalist >> MVC,ASP.NET, WCF & LinQ

DBA Mentalist >>Advance SQL Server Blog

Microsoft BI Mentalist >> MS BI Development Update

Connect With me on

| FaceBook |Twitter | linkedIn| Google+ | WordPress | RSS |