Overview

How can you create a custom graph that shows how many requirements were delivered each year for a product?
To do this we get the count of requirement attached Releases that:

  1. have an end date in the past
  2. are not deleted
  3. do not have a status 'Canceled'

Writing the SQL Query

  1. Follow the reference link "Custom Graphs Tutorial" to create a new graph
  2. Use the following SQL Query
  3. Click on "Display Data Grid" to check the results
  4. If the results are satisfactory, use one of the pre-existing chart formats

SQL Query

We create a query to find the right releases and the counts of requirements in those releases. We then turn this into a sub query so we can group the results by year.
This is a powerful technique that let’s us build simple looking results that have a lot of complexity underneath

SELECT SQLSUB.EndDate, SUM (SQLSUB.Total) AS Total_Requirements 
FROM (SELECT YEAR(RL.END_DATE) AS EndDate, SUM(RL.REQUIREMENT_COUNT) AS Total
FROM SpiraTestEntities.R_Releases AS RL
WHERE DiffDays(CurrentDateTime(), RL.END_DATE) < 0 
AND RL.IS_DELETED = false 
AND RL.RELEASE_STATUS_ID = 4 
AND RL.PROJECT_ID = ${ProjectId}
GROUP BY RL.END_DATE
) AS SQLSUB
GROUP BY SQLSUB.EndDate

Graph Output

  1. The looks like this:

    2. Note that output of both the "Display Data Grid" and the actual chart (Bar chart) are shown for illustration.

 

Adding on Incidents

What if you  want to sum the total number of Requirements and Incidents combined by year?
To do so, use the ESQL below This also uses a subquery, but has a join to combine the incident and requirement data together:

SELECT SQLSUB.EndDate, SUM (SQLSUB.Total) AS Total_Count 
FROM (SELECT YEAR(RL.END_DATE) AS EndDate,
Count (INC.INCIDENT_ID) + Count(RL.REQUIREMENT_COUNT) AS Total
FROM SpiraTestEntities.R_Releases AS RL
FULL JOIN SpiraTestEntities.R_Incidents AS INC ON RL.RELEASE_ID INC.RESOLVED_RELEASE_ID
WHERE DiffDays(CurrentDateTime(), RL.END_DATE) < 0 
AND RL.IS_DELETED = false AND RL.RELEASE_STATUS_ID = 4 AND RL.PROJECT_ID = ${ProjectId}
GROUP BY RL.END_DATE
) AS SQLSUB
GROUP BY SQLSUB.EndDate

And the chart will display summary value for Requirements and Incidents:

 

Query Explanation:

  1. SELECT YEAR(RL.END_DATE) AS EndDate fetches only the YEAR part from the Release end date (e.g. 2022 from 2022-12-31T23:11:01)
  2. The Count (INC.INCIDENT_ID) + Count(RL.REQUIREMENT_COUNT) AS Total expression summarizes the count of Incidents and Requirements together
  3. WHERE DiffDays(CurrentDateTime(), RL.END_DATE) < 0 filters all the records that have a Release end date earlier than today
  4. The IS_DELETED is used to limit the data to active records only
  5. RL.RELEASE_STATUS_ID = 4 extracts all records assigned to the closed release (Release status 4 = Closed)
  6. Using a subquery (Select into Select statement) is necessary to summarize the filtered results into one column per year