Creating Custom Graphs with Spira (Part 4)

January 21st, 2020 by Adam Sandman

One of the maxims I always tell developers is that regardless of what you build, customers will never be satisfied with the reports you offer or the integration that you provide. In fact, the two most underestimated tasks in software development are data feeds and reporting. So one of the nice features in our Spira platform is the ability to do custom graphing, so that you are not limited to just the graphs that ship with the system. This article is the fourth and last one in a series that explains how to use these powerful custom graphing features, which continues on from our third article that explained the differences between Entity SQL (ESQL) and traditional database SQL. In this article we'll discuss some of the more advanced types of query that you will want to use when creating graphs.

Recap on the Queries Used in Spira Graphs

In the first article in this series, we outlined a sample ESQL query to get the count of test runs by execution status:

select R.EXECUTION_STATUS_NAME, COUNT (R.TEST_RUN_ID) as COUNT
from SpiraTestEntities.R_TestRuns as R
where R.PROJECT_ID = ${ProjectId}
group by R.EXECUTION_STATUS_NAME

As we discussed, when using ESQL queries to display custom graphs, there are some restrictions about the select clause of the query:

You need to make the first column in the query the category for the x-axis
The other columns need to be purely numeric, and will be used to populate the data series that will be mapped to the x-axis categories.

We will now be looking at some specific examples of graphs that users have asked us for help with, that we have some suggestions for...

1) Requirements Added/Removed Over Time

For example, lets consider that you want to display a graph of requirements added and removed over time. To get a count of this we can query the SpiraTestEntities.R_HistoryChangeSets view to get a count of the changes, filter by additions and deletions, then use a combination of aggregation and the CAST operator to count the items added/removed:

select
R.CHANGE_DATE as Timestamp,
count(CASE
WHEN R.CHANGETYPE_NAME="Added" THEN 1
WHEN R.CHANGETYPE_NAME="Deleted" THEN -1
END
) AS Sum
from SpiraTestEntities.R_HistoryChangeSets as R
where
  R.ARTIFACT_TYPE_NAME = "Requirement"
group by R.CHANGE_DATE

This will display the following data:

Timestamp	Sum
2019-08-17T02:06:18	0
2019-08-23T02:51:18	0
2020-01-14T11:50:18	5
2020-01-14T11:50:18	7
2020-01-14T11:50:18	5
2020-01-14T11:50:18	9
2020-01-14T11:50:18	7
2020-01-14T11:50:18	6
2020-01-14T11:50:18	5
2020-01-14T11:50:18	7

Which when displayed as a graph would look like:

However suppose you want to display this graph by day, not by unique timestamp (a reasonable request), you would use the TruncateTime canonical EntitySQL function and combine that with a different way of writing the GROUP BY clause:

select
DatePart,
count(CASE
WHEN R.CHANGETYPE_NAME="Added" THEN 1
WHEN R.CHANGETYPE_NAME="Deleted" THEN -1
END
) AS Sum
from SpiraTestEntities.R_HistoryChangeSets as R
where
  R.ARTIFACT_TYPE_NAME = "Requirement"
group by TruncateTime(R.CHANGE_DATE) as DatePart

This would now give the following results instead:

DatePart	Sum
2019-08-17T00:00:00	0
2019-08-23T00:00:00	0
2020-01-14T00:00:00	248

which could be graphed as follows:

2) Aggregating Data Over Time Periods

A common need is the ability to aggregate data over multiple time periods. For example, in the query above, we had the list of requirements aggregated by day:

DatePart	Sum
2019-08-17T00:00:00	0
2019-08-23T00:00:00	0
2020-01-14T00:00:00	248

Suppose we wanted to group the data over a 20 day time period. We would need to modify the query as follows:

select
DatePart,
count(CASE
WHEN R.CHANGETYPE_NAME="Added" THEN 1
WHEN R.CHANGETYPE_NAME="Deleted" THEN -1
END
) AS Sum
from SpiraTestEntities.R_HistoryChangeSets as R
where
  R.ARTIFACT_TYPE_NAME = "Requirement"
group by AddDays(CreateDateTime(Year(R.CHANGE_DATE),1,1,0,0,0), (DayOfYear(R.CHANGE_DATE)/20)*20) as DatePart

Now when you execute the query, the system is using the following functions to combines the dates down into 20 day ranges:

DayOfYear to get the absolute day number this year (1-366)
Integer division and multiplication by 20 days to get the day converted to the first day in each 20 day range
Using AddDays and CreateDateTime to compose the full date time again, adding the total number of days back to the year base.

When executed, this will display: