Created On:  04 May 2012

Problem:

SilkCentral provides a report, "Custom Measure Trend Report", that allows a user to view the trends of a particular Custom Measure for a SilkPerformer Test Definition.  This report is limited to a singular custom measure.  Is there any way in which to report on the trend of all custom measures for a given SilkPerformer Test Definition?

Resolution:

The TM_PerfReportMeasure database table provides no field that indicates if a particular measure was a custom timer.

To workaround this limitation there are 2 solutions.


1. To exclude those measures whose names are generated by SilkPerformer as highlighted below:

SELECT e.TestDefID_fk "TestDefID", e.TestDefName "TestDefName", CAST(e.TestDefDescription AS nvarchar(max)) "TestDefDescription", p.ProjectName as "SPProject", p.ProjectDescription as "SPProjectDescr",
m.Name as "Transaction", p.ExecutionTimestamp as "ExecutionTimeStamp", edr.BuildName, m.Minimum as "min", m.Maximum as "max",
(case when m.CountMeasured=0 then 0 else (m.MeasureSum/m.CountMeasured) end) "avg", e.ErrorCount as "Errors"
FROM TM_TestDefExecutions e
INNER JOIN TM_PerformerResults r ON e.TestDefExecID_pk_fk = r.TestDefExecID_pk_fk
INNER JOIN TM_PerfReport p ON p.ReportID_pk = r.PerfReportID_fk
INNER JOIN TM_PerfReportUserType u ON p.ReportID_pk = u.ReportID_fk
INNER JOIN TM_PerfReportMeasure m ON u.UserTypeID_pk = m.UserTypeID_fk
INNER JOIN TM_ExecDefinitionRuns edr ON edr.ExecDefRunID_pk_fk = e.ExecDefRunID_fk
where e.TestDefID_fk = ${tdProp_Id_0|72|Test Definition Id} and p.ExecutionTimestamp>='${dateFrom|01-JAN-2000|Date From (DD-MON-YYYY)}' and p.ExecutionTimestamp<='${dateTo|01-JAN-2020|Date To (DD-MON-YYYY)}'
and e.ErrorCount < ${excludeErrors|10|Exclude runs with more than errors}
and (m.Name<>'#Overall Response Time#' AND m.Name<>'TInit' AND m.Name <>'Summary General' AND m.Name<>'Summary Internet' AND m.Name<>'Summary Web' AND m.Name<>'TmyTrans1')
group by m.Name,e.TestDefID_fk,e.TestDefName,CAST(e.TestDefDescription AS nvarchar(max)),p.ProjectName,p.ProjectDescription,p.ExecutionTimestamp,edr.BuildName,m.Minimum,m.Maximum,m.CountMeasured,m.MeasureSum,e.ErrorCount
order by p.ExecutionTimestamp, edr.BuildName



2. To name your custom timers so that they match a particular pattern, e.g. SCTM_MyCustomTimer1, SCTM_AnotherCustomerTimer. A report can then be created to look for measures whose name matches a specified pattern, for example in the above scenario "SCTM_". Again this is highlighted below:

SELECT e.TestDefID_fk "TestDefID", e.TestDefName "TestDefName", CAST(e.TestDefDescription AS nvarchar(max)) "TestDefDescription", p.ProjectName as "SPProject", p.ProjectDescription as "SPProjectDescr",
m.Name as "Transaction", p.ExecutionTimestamp as "ExecutionTimeStamp", edr.BuildName, m.Minimum as "min", m.Maximum as "max",
(case when m.CountMeasured=0 then 0 else (m.MeasureSum/m.CountMeasured) end) "avg", e.ErrorCount as "Errors"
FROM TM_TestDefExecutions e
INNER JOIN TM_PerformerResults r ON e.TestDefExecID_pk_fk = r.TestDefExecID_pk_fk
INNER JOIN TM_PerfReport p ON p.ReportID_pk = r.PerfReportID_fk
INNER JOIN TM_PerfReportUserType u ON p.ReportID_pk = u.ReportID_fk
INNER JOIN TM_PerfReportMeasure m ON u.UserTypeID_pk = m.UserTypeID_fk
INNER JOIN TM_ExecDefinitionRuns edr ON edr.ExecDefRunID_pk_fk = e.ExecDefRunID_fk
where e.TestDefID_fk = ${tdProp_Id_0|72|Test Definition Id} and p.ExecutionTimestamp>='${dateFrom|01-JAN-2000|Date From (DD-MON-YYYY)}' and p.ExecutionTimestamp<='${dateTo|01-JAN-2020|Date To (DD-MON-YYYY)}'
and e.ErrorCount < ${excludeErrors|10|Exclude runs with more than errors}
and m.Name LIKE 'SCTM_%'
group by m.Name,e.TestDefID_fk,e.TestDefName,CAST(e.TestDefDescription AS nvarchar(max)),p.ProjectName,p.ProjectDescription,p.ExecutionTimestamp,edr.BuildName,m.Minimum,m.Maximum,m.CountMeasured,m.MeasureSum,e.ErrorCount
order by p.ExecutionTimestamp, edr.BuildName