Report Server Query returning non cached reports

USE [ReportServer]




GO






/*

Query 1:

Specify report name looking for and list of parameters for report with timings in milliseconds

ordered by the total number of times parameter selection made in report (occurrences)

*/


DECLARE @ReportName nvarchar(200) = '%REPORTNAME%' --Set Report name

DECLARE @OccurancesOver int = 5 --Set occurance level

DECLARE @PeriodRange datetime = dateadd(month,-1,getdate()) --Last month

SELECT

[ItemPath]

,[Parameters]=replace(replace(cast([Parameters] as nvarchar(max)),'&',' / '),'%26','&')

,[Status]

,TotalOccurances = count(*)

,TimeDataRetrieval=sum([TimeDataRetrieval])

,TimeProcessing=sum([TimeProcessing])

,TimeRendering=sum([TimeRendering])

,TotalTime=sum([TimeDataRetrieval])+sum([TimeProcessing])+sum([TimeRendering])

FROM [dbo].[ExecutionLog3]

WHERE ItemPath like @ReportName

AND [Source] = 'Live' --Live queries only not already caching

AND [Status] = 'rsSuccess' -- ONLY successful

AND [TimeStart] >= @PeriodRange

GROUP BY

[ItemPath]

,replace(replace(cast([Parameters] as nvarchar(max)),'&',' / '),'%26','&')

,[Status]

HAVING count(*) >= @OccurancesOver

ORDER BY count(*) desc




/*

Query 2:

List all subscriptions and status

*/


SELECT




[Description]


,[EventType]

,[LastStatus]

,[LastRunTime]

FROM [dbo].[Subscriptions]

WHERE [Description] <> ''

order by [LastRunTime] desc

Creating an Azure SQL Server DB and Connecting via Management Studio

To create a new SQL Server database instance in Azure follow these steps:

1. Click the SQL databases instance

2. Select Add and enter all database details

You have now created your first Azure SQL Server database. Now in order to connect for Management studio so you can edit the database follow the following steps


3. Click on the database you have created and on the top select Set server firewall

Add Client IP address and set allow access to Azure Services to ON

4. Finally get the details to connect to the server by going to the properties of the database

5. Enter these details with the password you have set into management studio and you should now be able to connect.

Resize iFrame dynamically based on content

iFrame will resize dynamically for content on the same domain.

 

 

<style>.woocommerce-product-gallery{ opacity: 1 !important; }</style>

<script type='text/javascript' src='http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js?ver=1.3.2'></script>
<script type='text/javascript'>
   
    $(function(){
   
        var iFrames = $('iframe');
     
            function iResize() {
           
                    for (var i = 0, j = iFrames.length; i < j; i++) {
                      iFrames[i].style.height = iFrames[i].contentWindow.document.body.offsetHeight + 'px';}
                }
               
                if ($.browser.safari || $.browser.opera) {
               
                   iFrames.load(function(){
                       setTimeout(iResize, 0);
               });
           
                   for (var i = 0, j = iFrames.length; i < j; i++) {
                                var iSource = iFrames[i].src;
                                iFrames[i].src = '';
                                iFrames[i].src = iSource;
               }
              
                } else {
                   iFrames.load(function() {
                       this.style.height = this.contentWindow.document.body.offsetHeight + 'px';
                   });
                }
       
        });

</script>

 

 

 

<style>.woocommerce-product-gallery{ opacity: 1 !important; }</style>

<iframe src="content.html" class="iframe" scrolling="no" frameborder="0"></iframe>

/*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'  

Report Log 3 Report Subscriptions

SELECT USR.UserName AS SubscriptionOwner, b.name AS JobName, CAT.Name AS ReportName, SUB.ModifiedDate, SUB.Description, SUB.EventType, SUB.DeliveryExtension,
                  SUB.LastStatus, SUB.LastRunTime, SCH.NextRunTime, SCH.Name AS ScheduleName, CAT.ItemID, CAT.Path AS ReportPath, CAT.Description AS ReportDescription,
                  SUB.SubscriptionID, SUB.MatchData, 'exec dbo.AddEvent @EventType=''SharedSchedule'', @EventData=' + CAST(SUB.MatchData AS nvarchar(MAX))
                  + '''' AS ExecuteText
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 LEFT OUTER JOIN
                  msdb.dbo.sysjobs AS b ON CAST(RS.ScheduleID AS nvarchar(1000)) = CAST(b.name AS nvarchar(1000))
WHERE  (CAT.Type = 2) AND (replace(CAT.Path, CAT.Name, '') = CASE WHEN @prmPath = 'All' THEN replace(CAT.Path, CAT.Name, '') ELSE @prmPath END)
ORDER BY SubscriptionOwner, ReportPath

SSRS Log: 2 Report Summary

/*dstMain*/
SELECT    
      DATEPART(Year, TimeStart) AS ReportYear
    , DATEPART(Month, TimeStart) AS ReportMonth
    , DATEPART(Day, TimeStart) AS ReportDay
    , DATEPART(Hour, TimeStart) AS ReportHour
    , format(TimeStart,'yyyy-MM-dd') as FullDate
    , Type
    , COUNT(Name) AS ExecutionCount
    , SUM(TimeDataRetrieval) AS TimeDataRetrievalSum
    , SUM(TimeProcessing) AS TimeProcessingSum
    , SUM(TimeRendering) AS TimeRenderingSum
    , SUM(TimeDataRetrieval) + SUM(TimeProcessing) + SUM(TimeRendering)  As TotalTime
    , SUM(ByteCount) AS ByteCountSum
    , SUM([RowCount]) AS RowCountSum
FROM
(
    SELECT TimeStart, Catalog.Type, Catalog.Name, TimeDataRetrieval,
    TimeProcessing, TimeRendering, ByteCount, [RowCount]
    FROM Catalog
 INNER JOIN ExecutionLog ON Catalog.ItemID =
 ExecutionLog.ReportID LEFT OUTER JOIN
    Users ON Catalog.CreatedByID = Users.UserID
    WHERE ExecutionLog.TimeStart >= @prmDate
) AS RE
GROUP BY
      DATEPART(Year, TimeStart)
    , DATEPART(Month, TimeStart)
    , DATEPART(Day, TimeStart)
    , DATEPART(Hour, TimeStart)
   , format(TimeStart,'yyyy-MM-dd')
    , Type
ORDER BY
      ReportYear
    , ReportMonth
    , ReportDay
    , ReportHour
    , format(TimeStart,'yyyy-MM-dd')
    , Type

/*dstTop5Used*/
--Top 5 used reports
SELECT TOP 5  
      COUNT(Name) AS ExecutionCount
    , Name
    , SUM(TimeDataRetrieval) AS TimeDataRetrievalSum
    , SUM(TimeProcessing) AS TimeProcessingSum
    , SUM(TimeRendering) AS TimeRenderingSum
   ,SUM(TimeDataRetrieval)+SUM(TimeProcessing)+SUM(TimeRendering) As TotalTime
    , SUM(ByteCount) AS ByteCountSum
    , SUM([RowCount]) AS RowCountSum
FROM
(
    SELECT TimeStart, Catalog.Type, Catalog.Name,
      TimeDataRetrieval, TimeProcessing, TimeRendering, ByteCount, [RowCount]
    FROM
    Catalog INNER JOIN ExecutionLog ON Catalog.ItemID = ExecutionLog.ReportID
    WHERE ExecutionLog.TimeStart >= @prmDate AND Type = 2
) AS RE
GROUP BY
        Name
ORDER BY
        COUNT(Name) DESC
       ,Name

/*dstUnusedReports*/
SELECT distinct Name, Path, UserName
FROM Catalog INNER JOIN dbo.Users ON Catalog.CreatedByID = Users.UserID
WHERE Type = 2 AND
    Catalog.ItemID NOT IN
    (
        SELECT ExecutionLog.ReportID
        FROM ExecutionLog
        WHERE ExecutionLog.TimeStart >= @prmDate
    )
    ORDER BY Name

SSRS Log:1 Report List

/*dstMainQuery*/
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 replace(C.[Path],C.[Name],'') = case when @prmPath = 'All' then replace(C.[Path],C.[Name],'') else @prmPath end
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

/*dstFilterReportPath*/
SELECT OrderId=1,'All' as ShortPath
UNION ALL
SELECT distinct
       OrderId = 2
    ,ShortPath = replace(C.[Path],C.[Name],'')
FROM dbo.Catalog C
WHERE [Type] = 2
order by OrderId, ShortPath

/*dstConstants*/
SELECT @@SERVERNAME AS ServerName, DB_NAME() AS DatabaseName, @@VERSION AS Version, GETDATE() AS ExecutionTime