Created On:  15 November 2011

Problem:

Is it possible to report on the amount of defects logged from SilkCentral to an Issue Tracking profile?

This report should visually be similar to the Issues > Document View, however it should only display defects that have been logged from SilkCentral to the Issue Tracking profile in question.

Resolution:

The SQL query below demonstrates how we can create an advanced custom report, for both SQL Server and Oracle database servers, to achieve the required reporting functionality:

SQL Server:

SELECT ProfileID,SCC_IssueTrackingProfiles.Name,
SUM(CASE WHEN InternStatus='Open' THEN 1 ELSE 0 END) AS 'Open',
SUM(CASE WHEN InternStatus='Fixed' THEN 1 ELSE 0 END) AS 'Fixed',
SUM(CASE WHEN InternStatus='Verified' THEN 1 ELSE 0 END) AS 'Verified',
SUM(CASE WHEN InternStatus='Closed' THEN 1 ELSE 0 END) AS 'Closed',
SUM(CASE WHEN InternStatus='Deferred' THEN 1 ELSE 0 END) AS 'Deferred'
FROM TM_V_Issues
INNER JOIN SCC_IssueTrackingProfiles on (SCC_IssueTrackingProfiles.ProfileID_pk=TM_V_Issues.ProfileID)
WHERE (SCC_IssueTrackingProfiles.ProjectID_fk=${ProjectID|0} OR SCC_IssueTrackingProfiles.ProjectID_fk IS NULL)
GROUP BY ProfileID,SCC_IssueTrackingProfiles.Name


Oracle:

SELECT ProfileID,SCC_IssueTrackingProfiles.Name,
SUM(CASE WHEN InternStatus='Open' THEN 1 ELSE 0 END) AS "Open",
SUM(CASE WHEN InternStatus='Fixed' THEN 1 ELSE 0 END) AS "Fixed",
SUM(CASE WHEN InternStatus='Verified' THEN 1 ELSE 0 END) AS "Verified",
SUM(CASE WHEN InternStatus='Closed' THEN 1 ELSE 0 END) AS "Closed",
SUM(CASE WHEN InternStatus='Deferred' THEN 1 ELSE 0 END) AS "Deferred"
FROM TM_V_Issues
INNER JOIN SCC_IssueTrackingProfiles on (SCC_IssueTrackingProfiles.ProfileID_pk=TM_V_Issues.ProfileID)
WHERE (SCC_IssueTrackingProfiles.ProjectID_fk=${ProjectID|0} OR SCC_IssueTrackingProfiles.ProjectID_fk IS NULL)
GROUP BY ProfileID,SCC_IssueTrackingProfiles.Name