Writing Custom Reports with Spira (Part 2)

Cloning a Standard Report

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:

• Name - the name of the report is simply how it will be listed in the main Reports section of the application
• Description - this is the description of what the report is for. It will not be displayed in the report itself, but will be displayed as a tooltip in the Reports section of Spira
• Header - This is a rich text box that you can enter formatted text into. This will appear at the top of the report above any of the different content sections. You can embed images and include tables, lists or other stylistic elements
• Footer -This is a rich text box that you can enter formatted text into. This will appear at the bottom of the report after all of the different content sections. You can embed images and include tables, lists or other stylistic elements
• Active - This simply marks whether this report is ready to be used (active) or not.
• Formats - All of the Spira reports are generated first into HTML and then converted into one of the other formats. This section lets you choose which formats your report will be available in. Note that if your record has a lot of textual data, it may not convert well into a tabular format such as Excel.

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.

Viewing the XML Template

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:

• Name - this is the name of the standard section you want to use in the report. You can choose a different standard section, but you cannot edit the name itself.
• Description - this is the description of what the section is designed to do, this is read only and changes if you select a different section name from the dropdown above.
• Header - This is a rich text box that you can enter formatted text into. This will appear at the top of the section before any of the dynamic content. You can embed images and include tables, lists or other stylistic elements
• Footer -This is a rich text box that you can enter formatted text into. This will appear at the bottom of the section after all of the dynamic content. You can embed images and include tables, lists or other stylistic elements
• Template - This contains the eXtensible StyLesheet Template (XSLT) that is used to transform the raw data coming from Spira into the desired presentation format. XSLT includes both HTML elements (e.g. a list or table) plus XSLT specific tags that select the data from Spira and present it in some way. This is used to generate the dynamic portion of the report section. We shall discuss this next.

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.

Viewing the Data Available for Reporting

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.

Understanding XPATH

(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:

• element
• attribute
• text
• namespace
• processing-instruction
• comment
• document 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:

• root element - <bookstore>
• element node - <book>, <title>, <author>, etc.
• attribute node - lang="en"

Selecting Nodes

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:

In the table below we have listed some path expressions and the result of the expressions if used on our sample document:

Predicates

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:

Selecting Unknown Nodes

XPath wildcards can be used to select unknown XML nodes:

In the table below we have listed some path expressions and the result of the expressions:

Selecting Several Paths

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:

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.

Modifying the Report XML Template

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:

1. Remove the list of test steps from the report
2. Remove the creation date
3. Add a new column that displays for folders the % of tests that passed and the % of test cases that failed

Removing the Test Steps

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.

Removing the Creation Date

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>

Adding a Calculated Column

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:

• % Passed = FolderCountPassed div (FolderCountPassed + FolderCountFailed + FolderCountCaution + FolderCountNotRun + FolderCountBlocked)* 100
• % Failed = FolderCountFailed div (FolderCountPassed + FolderCountFailed + FolderCountCaution + FolderCountNotRun + FolderCountBlocked) * 100

Note: 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:

Conclusion

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.