September 11th, 2015 by inflectra
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 SpiraTeam is the ability to do custom reporting, so that you are not limited to just the reports that ship with the system. This article is the second in a series that explains how to use these powerful custom reporting features.
In this article we shall be taking one of the standard reports and using the standard section XML editor to make some changes to the XSLT template to hide some columns and add a new calculated column.
The first thing we need to do is take one of the standard reports (i.e. the one that you want to make changes to) and make a copy of it that we can modify. For your safety, Spira won't let you modify the original copy of the report in case you don't like the chnages you have made (!). To do this, go to Administration > System > Edit Reports and click on the 'Copy' hyperlink next to the report you want to modify. For this example we shall make a copy of the Test Case Summary Report:
Once you have copied the report, click on the 'Edit' link for this report and you will now be taken to the report editing page:
You can now change the following fields:
For this example we shall be modifying the second Standard Section of the Test Case Summary Report. We will be removing a couple of columns that we don't need and adding a new calculated column instead.
Under the list of 'Standard Sections', click on the 'Customize' hyperlink next to the 'Test Case List' section. That will display the dialog box that lets you edit this specific section of the report:
In this dialog box you can edit the following parts of the report section:
Feel free to edit the Header and Footer to make your section more readable, for example include a section heading or some introductory text. You might want to add a horizontal line (<HR>) to the footer to mark the end the report section.
If you copy and paste the contents of the Template section into a text editor, it will look like the following:
<?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:template match="/TestCaseData">
<table class="DataGrid" style="width:100%">
<tr>
<th>Test #</th>
<th>Name</th>
<th>Description</th>
<th>Priority</th>
<xsl:if test="TestCase/TestSteps">
<th>Test Step</th>
<th>Test Step Description</th>
<th>Test Step Expected Result</th>
<th>Test Step Sample Data</th>
</xsl:if>
<th>Status</th>
<th>Author</th>
<th>Owner</th>
<th>Automation Engine</th>
<th>Est. Duration</th>
<th>Created On</th>
<th>Last Modified</th>
<th>Last Executed</th>
<xsl:for-each select="TestCase[1]/CustomProperties/CustomProperty">
<th>
<xsl:value-of select="Alias"/>
</th>
</xsl:for-each>
</tr>
<xsl:for-each select="TestCase">
<tr>
<td>
<xsl:value-of select="TestCaseId"/>
</td>
<td>
<xsl:attribute name="style">
padding-left: <xsl:value-of select="string-length(IndentLevel)*2"/>px;
</xsl:attribute>
<xsl:if test="FolderYn='Y'">
<b>
<xsl:value-of select="Name"/>
</b>
</xsl:if>
<xsl:if test="FolderYn='N'">
<xsl:value-of select="Name"/>
</xsl:if>
</td>
<td>
<xsl:value-of select="Description" disable-output-escaping="yes"/>
</td>
<td>
<xsl:value-of select="TestCasePriorityName"/>
</td>
<xsl:if test="TestSteps">
<td></td>
<td></td>
<td></td>
<td></td>
</xsl:if>
<td>
<xsl:value-of select="ExecutionStatusName"/>
</td>
<td>
<xsl:value-of select="AuthorName"/>
</td>
<td>
<xsl:value-of select="OwnerName"/>
</td>
<td>
<xsl:value-of select="AutomationEngineName"/>
</td>
<td class="Timespan">
<xsl:value-of select="EstimatedDuration"/>
</td>
<td class="Date">
<xsl:call-template name="format-date">
<xsl:with-param name="datetime" select="CreationDate" />
</xsl:call-template>
</td>
<td class="Date">
<xsl:call-template name="format-date">
<xsl:with-param name="datetime" select="LastUpdateDate" />
</xsl:call-template>
</td>
<td class="Date">
<xsl:call-template name="format-date">
<xsl:with-param name="datetime" select="ExecutionDate" />
</xsl:call-template>
</td>
<xsl:for-each select="CustomProperties/CustomProperty">
<td>
<xsl:value-of select="Value"/>
</td>
</xsl:for-each>
</tr>
<xsl:for-each select="TestSteps/TestStep">
<tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td>
<xsl:value-of select="position()"/>
</td>
<td>
<xsl:value-of select="Description" disable-output-escaping="yes"/>
<xsl:value-of select="' '"/>
<xsl:value-of select="LinkedTestCaseName"/>
</td>
<td>
<xsl:value-of select="ExpectedResult" disable-output-escaping="yes"/>
</td>
<td>
<xsl:value-of select="SampleData" disable-output-escaping="yes"/>
</td>
<td>
<xsl:value-of select="ExecutionStatusName"/>
</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</xsl:for-each>
</xsl:for-each>
</table>
</xsl:template>
<xsl:template name="format-date">
<xsl:param name="datetime"/>
<xsl:variable name="date" select="substring-before($datetime, 'T')" />
<xsl:variable name="year" select="substring-before($date, '-')" />
<xsl:variable name="month" select="substring-before(substring-after($date, '-'), '-')" />
<xsl:variable name="day" select="substring-after(substring-after($date, '-'), '-')" />
<xsl:variable name="time" select="substring-before(substring-after($datetime, 'T'), '.')" />
<xsl:variable name="monthname">
<xsl:choose>
<xsl:when test="$month='01'">
<xsl:value-of select="'Jan'"/>
</xsl:when>
<xsl:when test="$month='02'">
<xsl:value-of select="'Feb'"/>
</xsl:when>
<xsl:when test="$month='03'">
<xsl:value-of select="'Mar'"/>
</xsl:when>
<xsl:when test="$month='04'">
<xsl:value-of select="'Apr'"/>
</xsl:when>
<xsl:when test="$month='05'">
<xsl:value-of select="'May'"/>
</xsl:when>
<xsl:when test="$month='06'">
<xsl:value-of select="'Jun'"/>
</xsl:when>
<xsl:when test="$month='07'">
<xsl:value-of select="'Jul'"/>
</xsl:when>
<xsl:when test="$month='08'">
<xsl:value-of select="'Aug'"/>
</xsl:when>
<xsl:when test="$month='09'">
<xsl:value-of select="'Sep'"/>
</xsl:when>
<xsl:when test="$month='10'">
<xsl:value-of select="'Oct'"/>
</xsl:when>
<xsl:when test="$month='11'">
<xsl:value-of select="'Nov'"/>
</xsl:when>
<xsl:when test="$month='12'">
<xsl:value-of select="'Dec'"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="''" />
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:value-of select="concat($day, '-' ,$monthname, '-', $year , ' ', $time)" />
</xsl:template>
</xsl:stylesheet>
This is the underlying template that reads the data in Spira and turns it into a simple HTML table containing all of the columns and rows to be reported on. As you can see, it includes the HTML elements for the table:
<table class="DataGrid" style="width:100%">
plus also XSLT selectors for looping through all of the test cases in the Spira project:
<xsl:for-each select="TestCase">
So before we can successfully modify the report, we need to understand what data is being returned by Spira.
To see the data that is available for reporting on, you need to open up another browser tab and then go to the Reports section of Spira:
Now click on the 'Test Case Summary' report from the left-hand navigation. This displays the Report Configuration page:
Choose the 'XML' output format for the report. Leave all of the other filters alone and uncheck the 'Test Steps' report element. Now click on the [Create Report] botton and Spira will generate the report in 'raw XML format':
<Report>
<Title> Test Case Summary Report </Title> <ProjectData> <Project> <ArtifactPrefix>PR</ArtifactPrefix> <ArtifactType>Project</ArtifactType> <ArtifactToken>PR-1</ArtifactToken> <ArtifactId>1</ArtifactId> <ProjectId>1</ProjectId> <ProjectGroupId>2</ProjectGroupId> <Name>Library Information System</Name> <Description>Sample application that allows users to manage books, authors and lending records for a typical branch library</Description> <CreationDate>2005-11-30T19:00:00</CreationDate> <Website>www.libraryinformationsystem.org</Website> <IsActive>True</IsActive> </Project> </ProjectData> <TestCaseData> <TestCase> <TestCaseId>1</TestCaseId> <ProjectId>1</ProjectId> <ExecutionStatusId>4</ExecutionStatusId> <AuthorId>2</AuthorId> <OwnerId>2</OwnerId> <TestCasePriorityId/> <AutomationEngineId/> <AutomationAttachmentId/> <Name>Functional Tests</Name> <Description/> <IndentLevel>AAA</IndentLevel> <ExecutionDate>2003-11-30T19:00:00</ExecutionDate> <CreationDate>2003-11-30T19:00:00</CreationDate> <LastUpdateDate>2003-11-30T19:00:00</LastUpdateDate> <ConcurrencyDate>2003-11-30T19:00:00</ConcurrencyDate> <EstimatedDuration/> <VisibleYn>Y</VisibleYn> <FolderYn>Y</FolderYn> <ExpandedYn>Y</ExpandedYn> <ActiveYn>Y</ActiveYn> <AttachmentsYn>N</AttachmentsYn> <TestStepsYn>N</TestStepsYn> <FolderCountPassed>1</FolderCountPassed> <FolderCountFailed>3</FolderCountFailed> <FolderCountCaution>1</FolderCountCaution> <FolderCountBlocked>1</FolderCountBlocked> <FolderCountNotRun>0</FolderCountNotRun> <FolderCountNotApplicable>0</FolderCountNotApplicable> <ExecutionStatusName>N/A</ExecutionStatusName> <AuthorName>Fred Bloggs</AuthorName> <OwnerName>Fred Bloggs</OwnerName> <ProjectName/> <TestCasePriorityName/> <AutomationEngineName/> <Custom_01/> <Custom_02/> ... <Custom_30/> <IsDeleted>False</IsDeleted> <CustomProperties> <CustomProperty> <Alias>URL</Alias> <Name>Custom_01</Name> <Type>Text</Type> </CustomProperty> <CustomProperty> <Alias>Test Type</Alias> <Name>Custom_02</Name> <Type>List</Type> </CustomProperty> </CustomProperties> <Discussions/> </TestCase> ... <TestCaseData> </Report>
This fragment of the report lets you see all of the data that is available for displaying in your report. You can navigate this hierarchy of information using the special XSLT selection language called XPATH. This lets you query the data returned from Spira to retrieve specific data elements that can be displayed in the report. Before we start modifying the report XSLT to use this data, we first need to get a basic understanding of XPATH itself.
(this section includes material from the website:http://www.whoishostingthis.com/resources/xslt/)
XPath is used to navigate through elements and attributes in an XML document. XPath uses path expressions to select nodes or node-sets in an XML document. These path expressions look very much like the expressions you see when you work with a traditional computer file system.
In XPath, there are seven kinds of nodes:
XML documents are treated as trees of nodes. The topmost element of the tree is called the root element.
In the examples that follow we shall be using the following simple XML document:
<?xml version="1.0" encoding="UTF-8"?>
<bookstore>
<book>
<title lang="en">Harry Potter</title>
<author>J K. Rowling</author>
<year>2005</year>
<price>29.99</price>
</book>
</bookstore>
This document contains the following node types:
XPath uses path expressions to select nodes in an XML document. The node is selected by following a path or steps. The most useful path expressions are listed below:
Expression | Description |
---|---|
nodename | Selects all nodes with the name "nodename" |
/ | Selects from the root node |
// | Selects nodes in the document from the current node that match the selection no matter where they are |
. | Selects the current node |
.. | Selects the parent of the current node |
@ | Selects attributes |
In the table below we have listed some path expressions and the result of the expressions if used on our sample document:
Path Expression | Result |
---|---|
bookstore | Selects all nodes with the name "bookstore" |
/bookstore | Selects the root element bookstore
Note: If the path starts with a slash ( / ) it always represents an absolute path to an element! |
bookstore/book | Selects all book elements that are children of bookstore |
//book | Selects all book elements no matter where they are in the document |
bookstore//book | Selects all book elements that are descendant of the bookstore element, no matter where they are under the bookstore element |
//@lang | Selects all attributes that are named lang |
Predicates are used to find a specific node or a node that contains a specific value. Predicates are always embedded in square brackets.
In the table below we have listed some path expressions with predicates and the result of the expressions:
Path Expression | Result |
---|---|
/bookstore/book[1] | Selects the first book element that is the child of the bookstore element. |
/bookstore/book[last()] | Selects the last book element that is the child of the bookstore element |
/bookstore/book[last()-1] | Selects the last but one book element that is the child of the bookstore element |
/bookstore/book[position()<3] | Selects the first two book elements that are children of the bookstore element |
//title[@lang] | Selects all the title elements that have an attribute named lang |
//title[@lang='en'] | Selects all the title elements that have a "lang" attribute with a value of "en" |
/bookstore/book[price>35.00] | Selects all the book elements of the bookstore element that have a price element with a value greater than 35.00 |
/bookstore/book[price>35.00]/title | Selects all the title elements of the book elements of the bookstore element that have a price element with a value greater than 35.00 |
XPath wildcards can be used to select unknown XML nodes:
Wildcard | Description |
---|---|
* | Matches any element node |
@* | Matches any attribute node |
node() | Matches any node of any kind |
In the table below we have listed some path expressions and the result of the expressions:
Path Expression | Result |
---|---|
/bookstore/* | Selects all the child element nodes of the bookstore element |
//* | Selects all elements in the document |
//title[@*] | Selects all title elements which have at least one attribute of any kind |
By using the | operator in an XPath expression you can select several paths.
In the table below we have listed some path expressions and the result of the expressions:
Path Expression | Result |
---|---|
//book/title | //book/price | Selects all the title AND price elements of all book elements |
//title | //price | Selects all the title AND price elements in the document |
/bookstore/book/title | //price | Selects all the title elements of the book element of the bookstore element AND all the price elements in the document |
Now that we understand the basics of XPath we can use that knowledge to modify our XSLT template to change the data that is included in our report.
In the standard report it will display a list of test cases with various standard fields plus all of the custom properties (it uses an XSLT for-each loop to dynamically add all of the custom properties). For our example, we want to do the following:
To remove the test steps, delete the following sections from the XSLT template:
<xsl:if test="TestCase/TestSteps">
<th>Test Step</th>
<th>Test Step Description</th>
<th>Test Step Expected Result</th>
<th>Test Step Sample Data</th>
</xsl:if>
and
<xsl:if test="TestSteps">
<td></td>
<td></td>
<td></td>
<td></td>
</xsl:if>
This removes the four columns related to test steps.
To remove the creation date, delete the header cell and body cell:
<th>Created On</th>
and
<td class="Date">
<xsl:call-template name="format-date">
<xsl:with-param name="datetime" select="CreationDate" />
</xsl:call-template>
</td>
Now to add the cell headers, we just need to add two <th> tags to the header of the table. This is done by adding:
<th>% Passed</th>
<th>% Failed</th>
Now to actually get the data, we need to use the following XPATH queries:
FolderCountPassed
div (FolderCountPassed
+ FolderCountFailed + FolderCountCaution + FolderCountNotRun + FolderCountBlocked)
* 100FolderCountFailed
div (FolderCountPassed
+ FolderCountFailed + FolderCountCaution + FolderCountNotRun + FolderCountBlocked)
* 100Note: the mathematical operators for XPATH are: + (add), * (multiply), - (subtract) and div (division). The slash is not used for division because it is already used as a node path separator.
So the section we need to add to the table body in the report would be:
<td> <xsl:value-of select="FolderCountPassed div (FolderCountPassed + FolderCountFailed + FolderCountCaution + FolderCountNotRun + FolderCountBlocked) * 100" />% </td>
<td> <xsl:value-of select="FolderCountFailed div (FolderCountPassed + FolderCountFailed + FolderCountCaution + FolderCountNotRun + FolderCountBlocked) * 100" />% </td>
Now that have make the changes, the complete XSLT template will be:
<?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:template match="/TestCaseData">
<table class="DataGrid" style="width:100%">
<tr>
<th>Test #</th>
<th>Name</th>
<th>Description</th>
<th>Priority</th>
<th>Status</th>
<th>Author</th>
<th>Owner</th>
<th>Automation Engine</th>
<th>Est. Duration</th>
<th>% Passed</th>
<th>% Failed</th>
<th>Last Modified</th>
<th>Last Executed</th>
<xsl:for-each select="TestCase[1]/CustomProperties/CustomProperty">
<th>
<xsl:value-of select="Alias"/>
</th>
</xsl:for-each>
</tr>
<xsl:for-each select="TestCase">
<tr>
<td>
<xsl:value-of select="TestCaseId"/>
</td>
<td>
<xsl:attribute name="style">
padding-left: <xsl:value-of select="string-length(IndentLevel)*2"/>px;
</xsl:attribute>
<xsl:if test="FolderYn='Y'">
<b>
<xsl:value-of select="Name"/>
</b>
</xsl:if>
<xsl:if test="FolderYn='N'">
<xsl:value-of select="Name"/>
</xsl:if>
</td>
<td>
<xsl:value-of select="Description" disable-output-escaping="yes"/>
</td>
<td>
<xsl:value-of select="TestCasePriorityName"/>
</td>
<td>
<xsl:value-of select="ExecutionStatusName"/>
</td>
<td>
<xsl:value-of select="AuthorName"/>
</td>
<td>
<xsl:value-of select="OwnerName"/>
</td>
<td>
<xsl:value-of select="AutomationEngineName"/>
</td>
<td class="Timespan">
<xsl:value-of select="EstimatedDuration"/>
</td>
<td>
<xsl:value-of select="FolderCountPassed div (FolderCountPassed + FolderCountFailed + FolderCountCaution + FolderCountNotRun + FolderCountBlocked) * 100" />%
</td>
<td>
<xsl:value-of select="FolderCountFailed div (FolderCountPassed + FolderCountFailed + FolderCountCaution + FolderCountNotRun + FolderCountBlocked) * 100" />%
</td>
<td class="Date">
<xsl:call-template name="format-date">
<xsl:with-param name="datetime" select="LastUpdateDate" />
</xsl:call-template>
</td>
<td class="Date">
<xsl:call-template name="format-date">
<xsl:with-param name="datetime" select="ExecutionDate" />
</xsl:call-template>
</td>
<xsl:for-each select="CustomProperties/CustomProperty">
<td>
<xsl:value-of select="Value"/>
</td>
</xsl:for-each>
</tr>
<xsl:for-each select="TestSteps/TestStep">
<tr>
<td></td>
<td></td>
<td></td>
<td></td>
<td>
<xsl:value-of select="position()"/>
</td>
<td>
<xsl:value-of select="Description" disable-output-escaping="yes"/>
<xsl:value-of select="' '"/>
<xsl:value-of select="LinkedTestCaseName"/>
</td>
<td>
<xsl:value-of select="ExpectedResult" disable-output-escaping="yes"/>
</td>
<td>
<xsl:value-of select="SampleData" disable-output-escaping="yes"/>
</td>
<td>
<xsl:value-of select="ExecutionStatusName"/>
</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</xsl:for-each>
</xsl:for-each>
</table>
</xsl:template>
<xsl:template name="format-date">
<xsl:param name="datetime"/>
<xsl:variable name="date" select="substring-before($datetime, 'T')" />
<xsl:variable name="year" select="substring-before($date, '-')" />
<xsl:variable name="month" select="substring-before(substring-after($date, '-'), '-')" />
<xsl:variable name="day" select="substring-after(substring-after($date, '-'), '-')" />
<xsl:variable name="time" select="substring-before(substring-after($datetime, 'T'), '.')" />
<xsl:variable name="monthname">
<xsl:choose>
<xsl:when test="$month='01'">
<xsl:value-of select="'Jan'"/>
</xsl:when>
<xsl:when test="$month='02'">
<xsl:value-of select="'Feb'"/>
</xsl:when>
<xsl:when test="$month='03'">
<xsl:value-of select="'Mar'"/>
</xsl:when>
<xsl:when test="$month='04'">
<xsl:value-of select="'Apr'"/>
</xsl:when>
<xsl:when test="$month='05'">
<xsl:value-of select="'May'"/>
</xsl:when>
<xsl:when test="$month='06'">
<xsl:value-of select="'Jun'"/>
</xsl:when>
<xsl:when test="$month='07'">
<xsl:value-of select="'Jul'"/>
</xsl:when>
<xsl:when test="$month='08'">
<xsl:value-of select="'Aug'"/>
</xsl:when>
<xsl:when test="$month='09'">
<xsl:value-of select="'Sep'"/>
</xsl:when>
<xsl:when test="$month='10'">
<xsl:value-of select="'Oct'"/>
</xsl:when>
<xsl:when test="$month='11'">
<xsl:value-of select="'Nov'"/>
</xsl:when>
<xsl:when test="$month='12'">
<xsl:value-of select="'Dec'"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="''" />
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:value-of select="concat($day, '-' ,$monthname, '-', $year , ' ', $time)" />
</xsl:template>
</xsl:stylesheet>
Click on the [Save] button to save your section and then the main [Save] button to save the report. You can now run the report through the main reports center and get something like:
Test # | Name | Description | Priority | Status | Author | Owner | Automation Engine | Est. Duration | % Passed | % Failed | Last Modified | Last Executed |
---|---|---|---|---|---|---|---|---|---|---|---|---|
1 | Functional Tests | N/A | Fred Bloggs | Fred Bloggs | 16% | 50% | 30-Nov-2003 | 30-Nov-2003 |
Now we have learned how to modify one of the standard reports and use XSLT, XPATH and a 'standard section' to reformat how the data appears. You can use your knowledge of XPATH and XSLT to make more sophisticated changes. For example you could delete the entire XSLT default template and create a new template that displays a simple list of test cases, or a table of just test case names and IDs.
In the third and final installment of this series we will be looking at how to create 'custom sections' in the report to perform more customized querying and reporting of the data.
And if you have any questions, please email or call us at +1 (202) 558-6885