November 13, 2009 at 7:02 am
Why I ask is that I have written 22 Financial reports for our finance and sales departments.
The IT Dept dedicated 2 months of my time, for me to educate myself on the Financial and sales data and produce these reports. Initial request was for 5 Reports. How it got up to 22 I do not know.
But the bottom line is now that they are written I do not believe they are being used.
I would like a simple way of knowing if these reports are being used?
November 13, 2009 at 7:29 am
This query will give you the info
SELECT
ex.UserName, ex.Format, ex.TimeStart, cat.Name, ex.Parameters, CONVERT(nvarchar(10), ex.TimeStart, 101) AS rundate
FROM ExecutionLog AS ex INNER JOIN
Catalog AS cat ON ex.ReportID = cat.ItemID
ORDER BY ex.TimeStart DESC
Ref: http://www.mssqltips.com/tip.asp?tip=1306
Hope this is what you are looking for
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
November 13, 2009 at 7:40 am
Check the [Total Executions] and other statistics for each report:
SELECT Catalog.Name AS Report_Name,
AVG(ExecutionLog.TimeDataRetrieval) AS AVG_QueryTime,
MIN(ExecutionLog.TimeDataRetrieval) AS MIN_QueryTime, MAX(ExecutionLog.TimeDataRetrieval) AS MAX_QueryTime,
STDEV(ExecutionLog.TimeDataRetrieval) AS SD_QueryTime, AVG(ExecutionLog.TimeProcessing) AS AVG_ProcessTime,
MIN(ExecutionLog.TimeProcessing) AS MIN_ProcessTime, MAX(ExecutionLog.TimeProcessing) AS MAX_ProcessTime,
STDEV(ExecutionLog.TimeProcessing) AS SD_ProcessTime, AVG(ExecutionLog.TimeRendering) AS AVG_RenderTime,
MIN(ExecutionLog.TimeRendering) AS MIN_RenderTime, MAX(ExecutionLog.TimeRendering) AS MAX_RenderTime,
STDEV(ExecutionLog.TimeRendering) AS SD_RenderTime, AVG(ExecutionLog.ByteCount) AS AVG_ReportrSize, AVG(ExecutionLog.[RowCount])
AS AVG_RowCount, MIN(ExecutionLog.[RowCount]) AS MIN_RowCount, MAX(ExecutionLog.[RowCount]) AS MAX_RowCount,
COUNT(ExecutionLog.ReportID) AS [Total Executions]
FROM ExecutionLog INNER JOIN
Catalog ON ExecutionLog.ReportID = Catalog.ItemID
WHERE Catalog.Type=2 -- Report only
GROUP BY Catalog.Name
November 13, 2009 at 8:39 am
Awesome guys
Thank you!
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply