January 21st, 2020 by inflectra
custom reporting custom graphs spira
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.
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:
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...
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:
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:
When executed, this will display:
DatePart | Sum |
---|---|
2019-08-09T00:00:00 | 0 |
2020-01-01T00:00:00 | 248 |
or in graphical form:
Ask an Inflectra expert:
And if you have any questions, please email or call us at +1 (202) 558-6885
To ensure your satisfaction, we provide product support free with every subscription purchase, which guarantees you unlimited access to our knowledge base, customer forums and helpdesk. Review our support policy.
The Inflectra knowledge base includes a wide variety of helpful support articles written by Inflectra's customer support specialists.
Discover great tips, discussions, and technical solutions from fellow customers and Inflectra's technical experts.
If you can't find the answer you're looking for, please get in touch with us: over email, phone, or online.