I have written an availability report with the help of many of the posts here on Thwack.
SELECT TOP 10000 CONVERT(DateTime,LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)),101) AS SummaryMonth,
AVG(ResponseTime.Availability) AS AVERAGE_of_Availability,
Nodes.Caption AS NodeName
FROM
Nodes INNER JOIN ResponseTime ON (Nodes.NodeID = ResponseTime.NodeID)
WHERE
( DateTime BETWEEN 40177 AND 40207.9999884259 )
AND
(
(Nodes.Vendor = 'Cisco')
)
GROUP BY CONVERT(DateTime, LTRIM(MONTH(DateTime)) + '/01/' + LTRIM(YEAR(DateTime)), 101),
Nodes.Caption
ORDER BY SummaryMonth ASC, 3 ASC
This report produces a nice little3 column report that has the average availability by node. What I need to satisfy some Sr Mgmt requirements is a way to total this availability and give an overal availability for the Cisco devices for the months on the same report. The only way I could think of was to add a second select and perform the math but how can I do this in report writer? OR is there a better way to get this type of report? I still want the individual device availabilities on the report but I need a way show the cumulative total for overal availability.
Any Advice?
