Sample Report

Below is the code of a pre-installed report called Project Status Report. By default, this report displays overall health values for all projects during a specified time span, from a beginning time (Begin|'01/01/2010 00:00'})}) to an end time (End|'01/01/2030 23:59'})}).

Detailed values for availability, accuracy, and performance are included:

(tsd.MeasureName = 'Overall Health' 
OR tsd.MeasureName = 'Availability' 
OR tsd.MeasureName = 'Accuracy' 
OR tsd.MeasureName = 'Performance')

An aggregation level is set:

WHERE tsd.AggregationDescription = ${Aggregation|'Week'}

Here is the complete SQL code for the report:

SELECT projects.ProjectID_pk ProjectID, projects.ProjectName, tsd.MeasureName MName,
 SUM(tsd.ValCount) CountSeriesTime, SUM(tsd.ValSum)/SUM(tsd.ValCount) AvgValue

FROM SCC_Projects projects
INNER JOIN (SELECT DISTINCT pg.ProjectID_pk_fk
FROM SCC_Projects_Groups pg
INNER JOIN SCC_UserGroupRoles ugr
ON pg.GroupID_pk_fk = ugr.GroupID_pk_fk
WHERE ugr.UserID_pk_fk = ${$USERID}) p2
ON (projects.ProjectID_pk = p2.ProjectID_pk_fk)
INNER JOIN SV_V_Monitors_TimeSeriesData tsd
ON projects.ProjectID_pk = tsd.ProjectID

WHERE tsd.AggregationDescription = ${Aggregation|'Week'}
AND (tsd.MeasureName = 'Overall Health' 
OR tsd.MeasureName = 'Availability' 
OR tsd.MeasureName = 'Accuracy' 
OR tsd.MeasureName = 'Performance')
AND tsd.SeriesTime >= ${$DATETIME(${pmResults_Begin|'01/01/2000 00:00'})}
AND tsd.SeriesTime <= ${$DATETIME(${pmResults_End|'01/01/2020 23:59'})}

GROUP BY projects.ProjectID_pk, projects.ProjectName, tsd.MeasureName

ORDER BY projects.ProjectID_pk ASC