Prev: Lookup table structure preferences
Next: Solution
From: A. Robinson on 15 Jul 2008 13:08
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:

SELECT DISTINCT
c.Name,
c.Path,
c.CreationDate,
c.Description,
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


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.

Could someone suggest how I can tweak this query to accomplish this?

Thanks!!


From: A. Robinson on 15 Jul 2008 13:20
I'm planning on using Reporting Services for this...but that doesn't solve
the problem of taking into account values that have an rsInternalError
status...

I'm already presenting these results in a SSRS report...

"William Vaughn (MVP)" wrote:

> Why reinvent the wheel? If you create a (simple) RDL report, it can get you
> past all of the computational and grouping issues in a heartbeat. Use the
> ReportViewer control to display the results. See Chapter 14.
>
> "A. Robinson" <ARobinson(a)discussions.microsoft.com> wrote in message
> news:5E417CA8-72E0-4CA8-B27E-45C3C75C7A49(a)microsoft.com...
> > 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:
> >
> > SELECT DISTINCT
> > c.Name,
> > c.Path,
> > c.CreationDate,
> > c.Description,
> > 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
> >
> >
> > 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.
> >
> > Could someone suggest how I can tweak this query to accomplish this?
> >
> > Thanks!!
> >
> >
>
> --
> __________________________________________________________________________
> William R. Vaughn
> President and Founder Beta V Corporation
> Author, Mentor, Dad, Grandpa
> Microsoft MVP
> (425) 556-9205 (Pacific time)
> Hitchhiker's Guide to Visual Studio and SQL Server (7th Edition)
> ____________________________________________________________________________________________
>
>
>
From: Jes�s L�pez on 15 Jul 2008 13:45


WITH ReportBase
AS
(
SELECT
c.Name,
c.Path,
c.CreationDate,
c.Description,
COUNT(e.ReportID) AS [Number of Times Executed],
MAX(e.TimeStart) AS [Last Time Executed],
SUM(CASE WHEN e.Status = 'rsSuccess' THEN 1 ELSE 0 END) AS
SuccessExecutionCount,
SUM(CASE WHEN e.Status = 'rsSuccess' THEN e.[RowCount] ELSE 0 END) AS
SuccessExecutionTotalRowCount,
SUM(CASE WHEN e.Status = 'rsSuccess' THEN e.timedataretrieval ELSE 0
END) AS SuccsessExecutionTotalRetrievalTime,
SUM(CASE WHEN e.Status = 'rsSuccess' THEN e.timeprocessing ELSE 0 END)
AS SuccessExecutionTotalTimeProcessing,
SUM(CASE WHEN e.Statur = 'rsSuccess' THEN e.timerendering ELSE 0 END) AS
SuccessExecutionTotalTimeRendering
FROM ExecutionLog AS e INNER JOIN
Catalog AS c ON e.ReportID = c.ItemID
GROUP BY c.Name, c.Path, c.CreationDate, c.Description
)
SELECT
Name, Path, CreationDate, Description, [Number of Times Executed],[Last
Time Executed],
SuccessExecutionTotalRowCount / SuccessExecutionCount AS [AVG NUMBER OF
ROWS],
SuccsessExecutionTotalRetrievalTime / SuccessExecutionCount AS [AVG DATA
RETRIEVAL TIME],
SuccessExecutionTotalTimeProcessing / SuccessExecutionCount AS [AVG
TIME PROCESSING],
SuccessExecutionTotalTimeRendering / SuccessExecutionCount AS [AVG TIME
RENDERING]
FROM ReportBase

Regards:

Jes�s L�pez
www.solidq.com



"A. Robinson" <ARobinson(a)discussions.microsoft.com> escribi� en el mensaje
news:5E417CA8-72E0-4CA8-B27E-45C3C75C7A49(a)microsoft.com...
> 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:
>
> SELECT DISTINCT
> c.Name,
> c.Path,
> c.CreationDate,
> c.Description,
> 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
>
>
> 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.
>
> Could someone suggest how I can tweak this query to accomplish this?
>
> Thanks!!
>
>




From: A. Robinson on 15 Jul 2008 14:11
Jesus:

Thanks my man!! Just what I was looking for...should have been able to
figure this out for myself but... ;-)

"Jesús López" wrote:

>
>
> WITH ReportBase
> AS
> (
> SELECT
> c.Name,
> c.Path,
> c.CreationDate,
> c.Description,
> COUNT(e.ReportID) AS [Number of Times Executed],
> MAX(e.TimeStart) AS [Last Time Executed],
> SUM(CASE WHEN e.Status = 'rsSuccess' THEN 1 ELSE 0 END) AS
> SuccessExecutionCount,
> SUM(CASE WHEN e.Status = 'rsSuccess' THEN e.[RowCount] ELSE 0 END) AS
> SuccessExecutionTotalRowCount,
> SUM(CASE WHEN e.Status = 'rsSuccess' THEN e.timedataretrieval ELSE 0
> END) AS SuccsessExecutionTotalRetrievalTime,
> SUM(CASE WHEN e.Status = 'rsSuccess' THEN e.timeprocessing ELSE 0 END)
> AS SuccessExecutionTotalTimeProcessing,
> SUM(CASE WHEN e.Statur = 'rsSuccess' THEN e.timerendering ELSE 0 END) AS
> SuccessExecutionTotalTimeRendering
> FROM ExecutionLog AS e INNER JOIN
> Catalog AS c ON e.ReportID = c.ItemID
> GROUP BY c.Name, c.Path, c.CreationDate, c.Description
> )
> SELECT
> Name, Path, CreationDate, Description, [Number of Times Executed],[Last
> Time Executed],
> SuccessExecutionTotalRowCount / SuccessExecutionCount AS [AVG NUMBER OF
> ROWS],
> SuccsessExecutionTotalRetrievalTime / SuccessExecutionCount AS [AVG DATA
> RETRIEVAL TIME],
> SuccessExecutionTotalTimeProcessing / SuccessExecutionCount AS [AVG
> TIME PROCESSING],
> SuccessExecutionTotalTimeRendering / SuccessExecutionCount AS [AVG TIME
> RENDERING]
> FROM ReportBase
>
> Regards:
>
> Jesús López
> www.solidq.com
>
>
>
> "A. Robinson" <ARobinson(a)discussions.microsoft.com> escribió en el mensaje
> news:5E417CA8-72E0-4CA8-B27E-45C3C75C7A49(a)microsoft.com...
> > 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:
> >
> > SELECT DISTINCT
> > c.Name,
> > c.Path,
> > c.CreationDate,
> > c.Description,
> > 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
> >
> >
> > 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.
> >
> > Could someone suggest how I can tweak this query to accomplish this?
> >
> > Thanks!!
> >
> >
>
>
>
>
>
From: A. Robinson on 15 Jul 2008 14:15
....although one quick question...

I'm getting a "divide by zero" error - which I'm assuming comes when I
calculate the averages. Do I need to use CASE statements for those as well?

"Jesús López" wrote:

>
>
> WITH ReportBase
> AS
> (
> SELECT
> c.Name,
> c.Path,
> c.CreationDate,
> c.Description,
> COUNT(e.ReportID) AS [Number of Times Executed],
> MAX(e.TimeStart) AS [Last Time Executed],
> SUM(CASE WHEN e.Status = 'rsSuccess' THEN 1 ELSE 0 END) AS
> SuccessExecutionCount,
> SUM(CASE WHEN e.Status = 'rsSuccess' THEN e.[RowCount] ELSE 0 END) AS
> SuccessExecutionTotalRowCount,
> SUM(CASE WHEN e.Status = 'rsSuccess' THEN e.timedataretrieval ELSE 0
> END) AS SuccsessExecutionTotalRetrievalTime,
> SUM(CASE WHEN e.Status = 'rsSuccess' THEN e.timeprocessing ELSE 0 END)
> AS SuccessExecutionTotalTimeProcessing,
> SUM(CASE WHEN e.Statur = 'rsSuccess' THEN e.timerendering ELSE 0 END) AS
> SuccessExecutionTotalTimeRendering
> FROM ExecutionLog AS e INNER JOIN
> Catalog AS c ON e.ReportID = c.ItemID
> GROUP BY c.Name, c.Path, c.CreationDate, c.Description
> )
> SELECT
> Name, Path, CreationDate, Description, [Number of Times Executed],[Last
> Time Executed],
> SuccessExecutionTotalRowCount / SuccessExecutionCount AS [AVG NUMBER OF
> ROWS],
> SuccsessExecutionTotalRetrievalTime / SuccessExecutionCount AS [AVG DATA
> RETRIEVAL TIME],
> SuccessExecutionTotalTimeProcessing / SuccessExecutionCount AS [AVG
> TIME PROCESSING],
> SuccessExecutionTotalTimeRendering / SuccessExecutionCount AS [AVG TIME
> RENDERING]
> FROM ReportBase
>
> Regards:
>
> Jesús López
> www.solidq.com
>
>
>
> "A. Robinson" <ARobinson(a)discussions.microsoft.com> escribió en el mensaje
> news:5E417CA8-72E0-4CA8-B27E-45C3C75C7A49(a)microsoft.com...
> > 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:
> >
> > SELECT DISTINCT
> > c.Name,
> > c.Path,
> > c.CreationDate,
> > c.Description,
> > 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
> >
> >
> > 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.
> >
> > Could someone suggest how I can tweak this query to accomplish this?
> >
> > Thanks!!
> >
> >
>
>
>
>
>
 |  Next  |  Last
Pages: 1 2
Prev: Lookup table structure preferences
Next: Solution