Tag Archives: TSQL

How to get most frequent used top 10 reports on report server

SELECT TOP 10 COUNT(Name) AS ExecutionCount, Name,
SUM(TimeDataRetrieval) AS TimeDataRetrievalSum,
SUM(TimeProcessing) AS TimeProcessingSum,
SUM(TimeRendering) AS TimeRenderingSum,
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 BETWEEN ‘2012-02-01 00:00:00.000’ AND ‘2013-03-28 23:59:00.000’
) AS RE
GROUP BY
Name
ORDER BY
COUNT(Name) DESC,
Name

How to get detailed execution history of reports on Report Server

SELECT  EL.TimeStart , EL.TimeEnd,DATEDIFF(s,EL.TimeStart , EL.TimeEnd) ‘Duration’ ,COALESCE(C.Path, ‘Unknown’) AS ItemPath, EL.UserName,EL.ExecutionId, CASE(EL.RequestType)
  WHEN 0 THEN ‘Interactive’  WHEN 1 THEN ‘Subscription’  WHEN 2 THEN ‘Refresh Cache’  ELSE ‘Unknown’END
   AS RequestType, — SubscriptionId
    EL.Format, Parameters, CASE(EL.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,EL.TimeStart, YEAR(EL.TimeStart)
       AS Start_Year,MONTH(EL.TimeStart) AS Start_Month,DATENAME(MONTH,EL.TimeStart) AS Start_Month_Name,
       DATENAME(DW,EL.TimeStart) AS Start_Day_Of_Week,DATEPART(WEEKDAY,EL.TimeStart)
       AS Start_Day_Number_of_Week,EL.TimeEnd, EL.TimeDataRetrieval, EL.TimeProcessing, EL.TimeRendering,
       CASE(EL.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,EL.Status,
        EL.ByteCount,EL.[RowCount],EL.AdditionalInfo,C.Name,C.CreatedByID,C.ModifiedByID,C.Description,
        C.CreationDate,C.ModifiedDate,CASE   WHEN C.TYPE=1 THEN ‘Folder’  WHEN C.TYPE=2 THEN ‘Report’
        WHEN C.TYPE=3 THEN ‘XML’   WHEN C.TYPE=4 THEN ‘Linked Report’  WHEN C.TYPE=5 THEN ‘Data Source’
         WHEN C.TYPE=6 THEN ‘Model’  WHEN C.TYPE=8 THEN ‘Shared Dataset’  WHEN C.TYPE=9 THEN ‘Report Part’END
         AS Type_Description
          FROM   ExecutionLogStorage AS EL  LEFT OUTER JOIN Catalog AS C ON
          (EL.ReportID = C.ItemID)
          WHERE
          C.Path like ‘%QuickTrend%’
                    AND
          EL.TimeStart BETWEEN ‘2012-02-01 00:00:00.000’ AND ‘2013-02-28 23:59:00.000’
          ORDER By 1

How to get Snapshot associated with the report

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_SQL2008_Prod.dbo.Subscriptions su
JOIN ReportServer_SQL2008_Prod.dbo.Catalog c
ON su.Report_OID = c.ItemID
JOIN ReportServer_SQL2008_Prod.dbo.ReportSchedule rsc
ON rsc.ReportID = c.ItemID
AND rsc.SubscriptionID = su.SubscriptionID
JOIN ReportServer_SQL2008_Prod.dbo.Schedule Sch
ON rsc.ScheduleID = sch.ScheduleID
WHERE c.Name = @ReportName

How to get subscription associated with the reports on Report Server

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_SQL2008_Prod.dbo.Subscriptions su
  JOIN ReportServer_SQL2008_Prod.dbo.Catalog c
    ON su.Report_OID = c.ItemID
  JOIN ReportServer_SQL2008_Prod.dbo.ReportSchedule rsc
    ON rsc.ReportID = c.ItemID
   AND rsc.SubscriptionID = su.SubscriptionID
  JOIN ReportServer_SQL2008_Prod.dbo.Schedule Sch
    ON rsc.ScheduleID = sch.ScheduleID
WHERE c.Name = @ReportName

How to get details of data sources and associated command with reports on Report Server

;WITH XMLNAMESPACES
(DEFAULT ‘http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition’,
http://schemas.microsoft.com/sqlserver/reporting/2008/01/reportdefinition’ AS REP
)
SELECT c.Path ,c.Name ,DataSetXML.value(‘@Name’, ‘varchar(MAX)’) DataSourceName ,
DataSetXML.value(‘REP:Query[1]/REP:CommandText[1]’, ‘varchar(MAX)’) CommandText
FROM ( SELECT ItemID ,CAST(CAST(Content AS VARBINARY(MAX)) AS XML) ReportXML
FROM ReportServer_SQL2008_Prod.[dbo].[Catalog]
WHERE TYPE = 2
) ReportXML
CROSS APPLY ReportXML.nodes(‘//REP:DataSet’) DataSetXML ( DataSetXML )
INNER JOIN [dbo].[Catalog] c ON ReportXML.ItemID = c.ItemID
— Search by part of the query text
WHERE ( DataSetXML.value(‘REP:Query[1]/REP:CommandText[1]’, ‘varchar(MAX)’) ) LIKE ‘%quicktrend%’

How to get the list of parameters used in Reports

SELECT a.name as ReportName,
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_SQL2008_Prod.dbo.Catalog C
WHERE C.Content is not null
AND C.Type = 2
–AND C.Name = ‘%/Dashboard/KnowledgeBase%’
) a
CROSS APPLY ParameterXML.nodes(‘//Parameters/Parameter’) p ( Paravalue )

How to get report level permission on Report Server

SELECT C.Name,U.UserName,R.RoleName,R.Description,U.AuthType FROM ReportServer_SQL2008_Prod.dbo.Users U
JOIN ReportServer_SQL2008_Prod.dbo.PolicyUserRole PUR
ON U.UserID = PUR.UserID
JOIN ReportServer_SQL2008_Prod.dbo.Policies P
ON P.PolicyID = PUR.PolicyID
JOIN ReportServer_SQL2008_Prod.dbo.Roles R
ON R.RoleID = PUR.RoleID
JOIN ReportServer_SQL2008_Prod.dbo.Catalog c
ON C.PolicyID = P.PolicyID

Note- For a specific report use below code
SELECT C.Name,U.UserName,R.RoleName,R.Description,U.AuthType
FROM ReportServer_SQL2008_Prod.dbo.Users U
JOIN ReportServer_SQL2008_Prod.dbo.PolicyUserRole PUR
ON U.UserID = PUR.UserID
JOIN ReportServer_SQL2008_Prod.dbo.Policies P
ON P.PolicyID = PUR.PolicyID
JOIN ReportServer_SQL2008_Prod.dbo.Roles R
ON R.RoleID = PUR.RoleID
JOIN ReportServer_SQL2008_Prod.dbo.Catalog c
ON C.PolicyID = P.PolicyID
WHERE c.Name = @name of your report
ORDER BY U.UserName