Creating a Custom Requirement Summary Graph using ESQL

Requirements Summary Graph

The standard requirements summary graph in Spira does not use ESQL, but is created using a combination of multiple queries iterated over using a C#/.NET web service:

However we can use ESQL and its UNION and Aggregation functions to create something very similar.

Creating the Custom Graph

To create a similar custom graph, you simply need to create an aggregated ESQL command for each status, COUNTing the # requirements by release for that status. Then you combine the same clause for each status you want in the graph using a UNION. Finally you GROUP and SUM the entire data data set across all the unions to get the final data set. A sample query would be:

select GR.RELEASE, SUM(GR.Requested) as Requested, SUM(GR.Planned) as Planned, SUM(GR.InProgress) as InProgress, SUM(GR.Developed) as Developed, SUM(GR.Tested) as Tested, SUM(GR.Completed) as Completed
from
(
(select CASE WHEN RQ.RELEASE_VERSION_NUMBER IS NULL THEN '(None)' ELSE RQ.RELEASE_VERSION_NUMBER END as RELEASE, COUNT(RQ.REQUIREMENT_ID) as Requested, 0 as Planned, 0 as InProgress, 0 as Developed, 0 as Tested, 0 as Completed
from SpiraTestEntities.R_Requirements as RQ
where RQ.PROJECT_ID = ${ProjectId} AND RQ.REQUIREMENT_STATUS_NAME = 'Requested'
group by RQ.RELEASE_VERSION_NUMBER)
union
(select CASE WHEN RQ.RELEASE_VERSION_NUMBER IS NULL THEN '(None)' ELSE RQ.RELEASE_VERSION_NUMBER END as RELEASE, 0 as Requested, COUNT(RQ.REQUIREMENT_ID) as Planned, 0 as InProgress, 0 as Developed, 0 as Tested, 0 as Completed
from SpiraTestEntities.R_Requirements as RQ
where RQ.PROJECT_ID = ${ProjectId} AND RQ.REQUIREMENT_STATUS_NAME = 'Planned'
group by RQ.RELEASE_VERSION_NUMBER)
union
(select CASE WHEN RQ.RELEASE_VERSION_NUMBER IS NULL THEN '(None)' ELSE RQ.RELEASE_VERSION_NUMBER END as RELEASE, 0 as Requested, 0 as Planned, COUNT(RQ.REQUIREMENT_ID) as InProgress, 0 as Developed, 0 as Tested, 0 as Completed
from SpiraTestEntities.R_Requirements as RQ
where RQ.PROJECT_ID = ${ProjectId} AND RQ.REQUIREMENT_STATUS_NAME = 'In Progress'
group by RQ.RELEASE_VERSION_NUMBER)
union
(select CASE WHEN RQ.RELEASE_VERSION_NUMBER IS NULL THEN '(None)' ELSE RQ.RELEASE_VERSION_NUMBER END as RELEASE, 0 as Requested, 0 as Planned, 0 as InProgress, COUNT(RQ.REQUIREMENT_ID) as Developed, 0 as Tested, 0 as Completed
from SpiraTestEntities.R_Requirements as RQ
where RQ.PROJECT_ID = ${ProjectId} AND RQ.REQUIREMENT_STATUS_NAME = 'Developed'
group by RQ.RELEASE_VERSION_NUMBER)
union
(select CASE WHEN RQ.RELEASE_VERSION_NUMBER IS NULL THEN '(None)' ELSE RQ.RELEASE_VERSION_NUMBER END as RELEASE, 0 as Requested, 0 as Planned, 0 as InProgress, 0 as Developed, COUNT(RQ.REQUIREMENT_ID) as Tested, 0 as Completed
from SpiraTestEntities.R_Requirements as RQ
where RQ.PROJECT_ID = ${ProjectId} AND RQ.REQUIREMENT_STATUS_NAME = 'Tested'
group by RQ.RELEASE_VERSION_NUMBER)
union
(select CASE WHEN RQ.RELEASE_VERSION_NUMBER IS NULL THEN '(None)' ELSE RQ.RELEASE_VERSION_NUMBER END as RELEASE, 0 as Requested, 0 as Planned, 0 as InProgress, 0 as Developed, 0 as Tested, COUNT(RQ.REQUIREMENT_ID) as Completed
from SpiraTestEntities.R_Requirements as RQ
where RQ.PROJECT_ID = ${ProjectId} AND RQ.REQUIREMENT_STATUS_NAME = 'Completed'
group by RQ.RELEASE_VERSION_NUMBER)) as GR
group by GR.RELEASE
order by GR.RELEASE asc

When you preview the datagrid, you'd see something like:

and when you run the graph, you will see: