Unanswered: Querying ReportServer Database
I'm writing a quick and dirty report to give to the powers that be showing just how often - or not - certain reports are run. I'm also going to include some other anecdotal information. One of the things I'd like to include are average times (rendering, data retrieval, etc.). Here's what I've got so far that works really well:
While this works great, there's one problem. When calculating the averages, I only want to include those executions that had a status of rsSuccess. For example, one report was run three times. One execution had an rsInternalError with a row count of half a million. The other two times succeeded and had significantly lower row counts. When calculating the averages, I'd like to throw out the "bad" executions, so as not to skew the results.
COUNT(e.ReportID) AS [Number of Times Executed],
MAX(e.TimeStart) AS [Last Time Executed],
AVG(e.[RowCount]) AS [AVG NUMBER OF ROWS],
AVG(e.timedataretrieval) AS [AVG DATA RETRIEVAL TIME],
AVG(e.timeprocessing) AS [AVG TIME PROCESSING],
AVG(e.timerendering) AS [AVG TIME RENDERING]
FROM ExecutionLog AS e INNER JOIN
Catalog AS c ON e.ReportID = c.ItemID
GROUP BY c.Name, c.Path, c.CreationDate, c.Description
ORDER BY c.Name
Could someone suggest how I can tweak this query to accomplish this?
"If I'm curt with you, it's because time is a factor here. I think fast, I talk fast, and I need you guys to act fast if you want to get out of this. So, pretty please - with sugar on top..."