Reporting from the Report Server Database

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Analysis Server and Reporting Services (2008)

Reporting from the Report Server Database

Author

Topic

theboyholty
Posting Yak Master

226 Posts

Posted - 2011-11-03 : 06:12:51

I'me trying to write a query which will show me slow or long running reports from a particular server. Below is the code I've got so far and it tells me the NAME of the data source as set up in Visual Studio/BIDS but although we have a naming convention for data sources, sometimes reports get set up with the name DataSet1 which could be any server. Ironically, its these reports which are often set up hastily which could be causing problems on the servers.

-- SLOWEST REPORTS RAN TODAY PLUS AVERAGE EXECUTION TIMES (IN SECONDS)
DECLARE @StartDate DATETIME, @EndDate DATETIME

SET @StartDate = CONVERT(VARCHAR, GETDATE(), 112)
SET @EndDate = CONVERT(VARCHAR, GETDATE()+1, 112)

SELECT 
	 c.Name
	,el.TimeStart 
	,c.[path]
	,'https://slnrpt02/Reports/Pages/Report.aspx?ItemPath=' + REPLACE(REPLACE(c.[Path],'/','%2f'),' ','+') + '&ViewMode=Detail' as ReportURL
	,ds.Name as DataSourceServer	
	,el.Username as ReportUser
	,el.TimeDataRetrieval/1000 as TimeDataRetrievalSeconds
	,el.TimeProcessing/1000 as TimeProcessingSeconds
	,el.TimeRendering/1000 as TimeRenderingSeconds
	,(el.TimeDataRetrieval + el.TimeProcessing + el.TimeRendering)/1000 as TotalTimeSeconds
	,ISNULL(at.AverageTime,0) AS AverageTime
	,el.[Status] as ExecStatus

FROM dbo.ExecutionLog el
INNER JOIN dbo.[Catalog] c ON el.reportid=c.itemid
INNER JOIN dbo.DataSource ds ON ds.ItemID=el.ReportID
LEFT JOIN (-- to get average execution time for each report
	SELECT 
		c.[path],AVG((el.TimeDataRetrieval + el.TimeProcessing + el.TimeRendering)/1000) as AverageTime
	FROM dbo.ExecutionLog el
	INNER JOIN dbo.[Catalog] c ON el.reportid=c.itemid
	WHERE
		el.timestart >= @StartDate
	AND el.timestart <= @EndDate
	GROUP BY c.[path]
	) at ON at.[path]=c.[path]
WHERE
	el.timestart >= @StartDate
AND el.timestart <= @EndDate
AND (el.TimeDataRetrieval + el.TimeProcessing + el.TimeRendering) > 11000
ORDER BY el.TimeDataRetrieval + el.TimeProcessing + el.TimeRendering DESC

So my question is, how can I change the above query so it shows the actual server that the report is pointing to?

---------------------------------------------------------------------------------
http://www.mannyroadend.co.uk A Bury FC supporters website and forum

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources