/*dstMain*/

SELECT C.[ItemID]
      ,C.[Name]
      ,C.[Path]
   ,C.[Type]
   ,TypeName       = case C.[Type]
           when 1 then 'Folder'
           when 2 then 'Report'
           when 3 then 'Resources'
           when 4 then 'Linked Report'
           when 5 then 'Data Source'
           when 6 then 'Report Model'
           when 7 then 'Report Part (SQL 2008 R2, unverified)'
           when 8 then 'Shared Dataset (SQL 2008 R2)'
          end
       ,ExecutionCount    = COUNT(C.Name)
    ,SubscriptionCount   = COUNT(distinct SUB.[Description])
    ,LastRan      = MAX(el.TimeStart)
       --Total Time
    ,TimeDataRetrievalTotal  = SUM(el.TimeDataRetrieval)
       ,TimeProcessingTotal   = SUM(el.TimeProcessing)
       ,TimeRenderingTotal   = SUM(el.TimeRendering)
       ,TimeTotal     = SUM(el.TimeDataRetrieval)+SUM(el.TimeProcessing)+SUM(el.TimeRendering)
    --Average Time
     ,TimeDataRetrievalAverage = AVG(el.TimeDataRetrieval)
       ,TimeProcessingAverage  = AVG(el.TimeProcessing)
       ,TimeRenderingAverage  = AVG(el.TimeRendering)
       ,TimeTotalAverage   = AVG(el.TimeDataRetrieval)+AVG(el.TimeProcessing)+AVG(el.TimeRendering)
    --Max Time
     ,TimeDataRetrievalMax  = MAX(el.TimeDataRetrieval)
       ,TimeProcessingMax   = MAX(el.TimeProcessing)
       ,TimeRenderingMax   = MAX(el.TimeRendering)
       ,TimeTotalMax    = MAX(el.TimeDataRetrieval)+MAX(el.TimeProcessing)+MAX(el.TimeRendering)
    --Min Time
     ,TimeDataRetrievalMin  = MIN(el.TimeDataRetrieval)
       ,TimeProcessingMin   = MIN(el.TimeProcessing)
       ,TimeRenderingMin   = MIN(el.TimeRendering)
       ,TimeTotalMin    = MIN(el.TimeDataRetrieval)+MIN(el.TimeProcessing)+MIN(el.TimeRendering)
    ,ByteCountSum    = SUM(el.ByteCount)
       ,RowCountSum     = SUM(el.[RowCount])
   ,CreatedBy     = U.UserName
      ,CreationDate     = C.CreationDate
      ,ModifiedBy     = UM.UserName
      ,ModifiedDate     = C.ModifiedDate
  FROM dbo.Catalog C
  JOIN dbo.Users U
    ON C.CreatedByID = U.UserID
  JOIN dbo.Users UM
    ON c.ModifiedByID = UM.UserID
LEFT JOIN ExecutionLog el
       ON C.ItemID = el.ReportID
LEFT JOIN dbo.Subscriptions SUB
ON SUB.Report_OID = c.ItemID
WHERE C.[Type] = 2 --Reports
AND c.ItemID = @prmReport
AND el.TimeStart >= @prmDate
--AND Name like @ReportName
GROUP BY
C.[ItemID]
,C.[Name]
,C.[Path]
,C.[Type]
,U.UserName
,C.CreationDate
,UM.UserName
,C.ModifiedDate

/*dstReportSubscriptions*/
SELECT USR.UserName AS SubscriptionOwner, SUB.ModifiedDate, SUB.Description, SUB.EventType, SUB.DeliveryExtension, SUB.LastStatus, SUB.LastRunTime, SCH.NextRunTime,
                  SCH.Name AS ScheduleName, CAT.Path AS ReportPath, CAT.Description AS ReportDescription, SUB.SubscriptionID, SUB.MatchData,
ExecuteText = 'exec dbo.AddEvent @EventType=''SharedSchedule'', @EventData=' + cast(SUB.MatchData as nvarchar(max)) + ''''
FROM     Subscriptions AS SUB INNER JOIN
                  Users AS USR ON SUB.OwnerID = USR.UserID INNER JOIN
                  Catalog AS CAT ON SUB.Report_OID = CAT.ItemID INNER JOIN
                  ReportSchedule AS RS ON SUB.Report_OID = RS.ReportID AND SUB.SubscriptionID = RS.SubscriptionID INNER JOIN
                  Schedule AS SCH ON RS.ScheduleID = SCH.ScheduleID
WHERE  (CAT.Type = 2) AND (CAT.ItemID = @prmReport)
ORDER BY SubscriptionOwner, ReportPath

/*dstDataSource*/
DECLARE @SQL nvarchar(max)
Declare @Namespace NVARCHAR(500)
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 @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  dbo.Catalog C
                          WHERE  C.Content is not null
                            AND  C.Type  = 2
                            AND  C.ItemID  = ''' + @prmReport + '''
                  ) a
                  CROSS APPLY reportXML.nodes(''/Report/DataSources/DataSource'') r ( x )
                ORDER BY name ;'

EXEC(@SQL)

/*dstDataSets*/
DECLARE @SQL nvarchar(max)
Declare @Namespace NVARCHAR(500)
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 @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)'')
       --,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  dbo.Catalog C
          WHERE  C.Content is not null
            AND  C.Type = 2
            AND  C.ItemID  = ''' + @prmReport + '''
       ) a
  CROSS APPLY reportXML.nodes(''/Report/DataSets/DataSet'') r ( x )
  --CROSS APPLY x.nodes(''Fields/Field'') f(df) 
ORDER BY name '
EXEC(@SQL)

/*dstTimings*/
DECLARE @prmReport nvarchar(max) = '6D0597A6-5A64-4AF5-8AB8-AE5E29B78363'
DECLARE @prmDate datetime = '2017-01-01'
SELECT  [Typeorder]=1
       ,[Type] = 'Max'
       ,TimeDataRetrievalMax  = MAX(el.TimeDataRetrieval)
       ,TimeProcessingMax   = MAX(el.TimeProcessing)
       ,TimeRenderingMax   = MAX(el.TimeRendering)
       ,TimeTotalMax    = MAX(el.TimeDataRetrieval)+MAX(el.TimeProcessing)+MAX(el.TimeRendering)
  FROM dbo.Catalog C
LEFT JOIN ExecutionLog el
       ON C.ItemID = el.ReportID
WHERE C.[Type] = 2 --Reports
AND cast(c.ItemID as nvarchar(max)) = @prmReport
AND el.TimeStart >= @prmDate
UNION ALL
SELECT  [Typeorder]=2
       ,[Type] = 'Min'
       ,TimeDataRetrievalMax  = MIN(el.TimeDataRetrieval)
       ,TimeProcessingMax   = MIN(el.TimeProcessing)
       ,TimeRenderingMax   = MIN(el.TimeRendering)
       ,TimeTotalMax    = MIN(el.TimeDataRetrieval)+MIN(el.TimeProcessing)+MIN(el.TimeRendering)
  FROM dbo.Catalog C
LEFT JOIN ExecutionLog el
       ON C.ItemID = el.ReportID
WHERE C.[Type] = 2 --Reports
AND cast(c.ItemID as nvarchar(max)) = @prmReport
AND el.TimeStart >= @prmDate
UNION ALL
SELECT  [Typeorder]=3
       ,[Type] = 'Average'
       ,TimeDataRetrievalMax  = AVG(el.TimeDataRetrieval)
       ,TimeProcessingMax   = AVG(el.TimeProcessing)
       ,TimeRenderingMax   = AVG(el.TimeRendering)
       ,TimeTotalMax    = AVG(el.TimeDataRetrieval)+AVG(el.TimeProcessing)+AVG(el.TimeRendering)
  FROM dbo.Catalog C
LEFT JOIN ExecutionLog el
       ON C.ItemID = el.ReportID
WHERE C.[Type] = 2 --Reports
AND cast(c.ItemID as nvarchar(max)) = @prmReport
AND el.TimeStart >= @prmDate
UNION ALL
SELECT  [Typeorder]=4
       ,[Type] = 'Cached'
       ,TimeDataRetrievalMax  = AVG(el.TimeDataRetrieval)
       ,TimeProcessingMax   = AVG(el.TimeProcessing)
       ,TimeRenderingMax   = AVG(el.TimeRendering)
       ,TimeTotalMax    = AVG(el.TimeDataRetrieval)+AVG(el.TimeProcessing)+AVG(el.TimeRendering)
  FROM dbo.Catalog C
LEFT JOIN [dbo].[ExecutionLog3] el
       ON C.[Path] = el.ItemPath
WHERE C.[Type] = 2 --Reports
AND cast(c.ItemID as nvarchar(max)) = @prmReport
AND el.TimeStart >= @prmDate
AND [Source] = 'Cache'