Report Metadata

/******************************************************************************** * PROCEDURE NAME: dbo.usp_ReportGlossary * PROCEDURE DESC: Provides details around reports including connections, users, data sources and report glossary details @QueryPart provides particular section of detail -- 1 = Created by -- 2 = Parameters -- 3 = Data Sources, Sets, Commands and Fields -- 4 = Subscriptions -- 5 = Snapshot -- 6 = Authorised Users -- 7 = Execution Log -- 8 = Report Glossary Details *********************************************************************************/ CREATE PROCEDURE [dbo].[usp_ReportGlossary] ( @ReportName VARCHAR(850), @QueryPart INT ) AS BEGIN SET NOCOUNT ON DECLARE @Namespace VARCHAR(500) DECLARE @SQL VARCHAR(MAX) DECLARE @ReportID VARCHAR(50) = (SELECT C.ItemID FROM [ReportServer].[dbo].[Catalog] C WHERE C.Content is not null AND C.Type = 2 AND C.Name = @ReportName) 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="','') + '' --------------------------------- -- Get report creation details --------------------------------- IF @QueryPart = 1 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 --------------------------------- -- Get parameters of the report --------------------------------- IF @QueryPart = 2 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 ) ---------------------------------------------------------------------------------------------------------------------------------------------------------- -- Get Data Sets , Command , Data fields Associated with the report ---------------------------------------------------------------------------------------------------------------------------------------------------------- IF @QueryPart = 3 SELECT @SQL = 'WITH XMLNAMESPACES ( DEFAULT ''' + @Namespace + ''', ''http://schemas.microsoft.com/SQLServer/reporting/reportdesigner'' AS rd ) SELECT DISTINCT ReportName = name ,DataSetName = x.value(''(@Name)[1]'', ''VARCHAR(250)'') ,DataSourceName = x.value(''(Query/DataSourceName)[1]'',''VARCHAR(250)'') ,CommandText = x.value(''(Query/CommandText)[1]'',''VARCHAR(MAX)'') 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) ---------------------------------------------------------------------------------------------------------------------------------------------------------- -- Get subscription Associated with the report ---------------------------------------------------------------------------------------------------------------------------------------------------------- IF @QueryPart = 4 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 ---------------------------------------------------------------------------------------------------------------------------------------------------------- -- Get Snapshot associated with the report ---------------------------------------------------------------------------------------------------------------------------------------------------------- IF @QueryPart = 5 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 ---------------------------------------------------------------------------------------------------------------------------------------------------------- -- Get Users List having access to reports and tasks they can perform on the report ---------------------------------------------------------------------------------------------------------------------------------------------------------- IF @QueryPart = 6 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 ---------------------------------------------------------------------------------------------------------------------------------------------------------- -- Execution Log fo the report ---------------------------------------------------------------------------------------------------------------------------------------------------------- IF @QueryPart = 7 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 ---------------------------------------------------------------------------------------------------------------------------------------------------------- -- Report Glossary ---------------------------------------------------------------------------------------------------------------------------------------------------------- IF @QueryPart = 8 SELECT rg.ReportGlossaryID,rg.ReportID, rg.TypeID, rgt.Description , rg.context, rg.CreatedOn, rg.ModifiedOn FROM [DW_CTL].[dbo].[ReportGlossary] rg LEFT JOIN [DW_CTL].[dbo].[ReportGlossaryType] rgt ON rgt.TypeID = rg.TypeID WHERE ReportID IN(@ReportID) END