Creating the Custom Report

This article assumes you are familiar with the basics of writing custom reports in Spira.
In this example we will be using a custom report with a custom SQL section.

To create the report you need to:

  1. Go to Administration  > Edit Reports
  2. Create a new Report
  3. Specify that it should allow generation in MS-Word, Excel, HTML and PDF formats
  4. Choose to add a Custom Section:

  5. Then click [Save] twice and you can run your report.

Step1.
Paste into the Query section information below:
(Feel free to change the columns in SELECT statement upon need, do not forget to regenerate the Default Template after any changes made).

 SELECT 
  TC.TEST_CASE_ID, 
  TC.NAME,
  TC.TEST_CASE_TYPE_NAME, 
  TC.TEST_CASE_STATUS_NAME, 
  TC.EXECUTION_STATUS_NAME,
  TC.EXECUTION_DATE,
  TC.OWNER_NAME
  FROM SpiraTestEntities.R_TestCases AS TC
  WHERE 
! EXISTS
  (SELECT 
    TCI.TEST_CASE_ID FROM  SpiraTestEntities.R_TestCases
    INNER JOIN SpiraTestEntities.R_TestCaseIncidents AS TCI ON TC.TEST_CASE_ID = TCI.TEST_CASE_ID)
    AND TC.EXECUTION_STATUS_ID = 1
    AND TC.PROJECT_ID = ${ProjectId}

Step 2.

Click on Create a Default Template or copy the generated template into that section from below:

 

<?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="/RESULTS">
    <table class="DataGrid"><tr><th>TEST_CASE_ID</th><th>NAME</th><th>TEST_CASE_TYPE_NAME</th><th>TEST_CASE_STATUS_NAME</th><th>EXECUTION_STATUS_NAME</th><th>EXECUTION_DATE</th><th>OWNER_NAME</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="TEST_CASE_ID"/></td><td><xsl:value-of select="NAME"/></td><td><xsl:value-of select="TEST_CASE_TYPE_NAME"/></td><td><xsl:value-of select="TEST_CASE_STATUS_NAME"/></td><td><xsl:value-of select="EXECUTION_STATUS_NAME"/></td><td><xsl:value-of select="EXECUTION_DATE"/></td><td><xsl:value-of select="OWNER_NAME"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

Step 3.

Once that is done, you are ready to run your report.

Running the Custom Report

To run the report and generate a program-level RTM, go to the main Reports screen and execute your report.

Now when you run the report (for example in XLS or MS-Excel format) you will see this: