Post reply

Reporting Services

  • November 13, 2009 at 7:02 am

    #140029

    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

    #1078919

    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

    #1078929

    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

    #1078978

    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