mVerve Partner Article
This KB article was developed by mVerve in consultation with the Inflectra team.
Objective
To create a custom report that visually represents the hierarchical relationship between parent-child requirements in the report, ensuring clear indentation to signify their respective levels of association.
Steps
1. To create the custom report you need to:
Go to Administration > Edit Reports
Create a new Report, give it a name
Specify that it should allow generation in MS-Word, Excel, HTML and PDF formats
Choose to add a Custom Section
2. Below are the Parent-Child Indented Requirements on the Spira UI
3. Create a report using a modified query below.
Query
select
R.REQUIREMENT_ID,
R.NAME,
R.INDENT_LEVEL,
R.PROJECT_ID
from
SpiraTestEntities.R_Requirements as R
where
R.PROJECT_ID = ${ProjectId}
and R.IS_DELETED = False
and R.NAME is not null
Query Explanation
The “IS_DELETED = False” is required to eliminate the results that are not visible in the user interface but still available in the database.
While Requirement Name is mostly filled, if a name is accidentally nullified in the backend, the “R.NAME is not null” clause eliminates this data.
For cross project reports, the “R.PROJECT_ID = ${ProjectId}” clause can be removed. In such cases, join with the SpiraTestEntities.R_Projects on PROJECT_ID and get the Project Name from the projects table.
4. Preview Results
Given below is the data view.
5. To achieve the desired outcome of presenting parent-child relationships in an indented format within the report, we need to make the following adjustments to the XSLT template. This code presented below is useful for visually representing hierarchical data with varying levels of indentation in an HTML table.
<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" xmlns:msxsl="urn:schemas-microsoft-com:xslt" exclude-result-prefixes="msxsl">
<xsl:output method="html"/>
<xsl:template match="/RESULTS">
<html>
<head>
<style>
.DataGrid {
border-collapse: collapse;
width: 100%;
}
.DataGrid th, .DataGrid td {
border: 1px solid black;
padding: 8px;
text-align: left;
}
</style>
</head>
<body>
<table class="DataGrid">
<tr>
<th>REQUIREMENT ID</th>
<th>NAME</th>
<th>INDENT LEVEL</th>
<th>PROJECT ID</th>
</tr>
<xsl:for-each select="ROW">
<tr>
<td><xsl:value-of select="REQUIREMENT_ID"/></td>
<td>
<xsl:call-template name="Indent">
<xsl:with-param name="level" select="string-length(INDENT_LEVEL) div 3"/>
</xsl:call-template>
<xsl:value-of select="NAME"/>
</td>
<td><xsl:value-of select="INDENT_LEVEL"/></td>
<td><xsl:value-of select="PROJECT_ID"/></td>
</tr>
</xsl:for-each>
</table>
</body>
</html>
</xsl:template>
<xsl:template name="Indent">
<xsl:param name="level"/>
<xsl:if test="$level > 0">
<xsl:value-of select="' '"/>
<xsl:call-template name="Indent">
<xsl:with-param name="level" select="$level - 1"/>
</xsl:call-template>
</xsl:if>
</xsl:template>
</xsl:stylesheet>
XSLT Explanation
- The <xsl:template name="Indent"> defines a template named "Indent" that takes a parameter $level, representing the indentation level.
- Inside the "Indent" template, it checks if the $level is greater than 0. If true, it adds a non-breaking space ( ) and recursively calls itself with $level - 1.
- The <xsl:for-each select="ROW"> iterates over each "ROW" element in the XML data.
- For each "ROW", it creates a table row (<tr>) with cells (<td>) for "REQUIREMENT_ID", "NAME", "INDENT_LEVEL", and "PROJECT_ID".
- Inside the "NAME" cell, it calls the "Indent" template with the parameter $level set to the length of "INDENT_LEVEL" divided by 3 (assuming each level of indentation is representedby 3 characters).
- Finally, it outputs the value of "NAME" inside the "NAME" cell, creating an indented display of the "NAME" based on the "INDENT_LEVEL".
Report Output
Given below is the example of a generated report output.
