Create a 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 ESQL section.

To create the report you need to:

  1. Go to Administration  > Edit Reports
  2. Click on Create a new Report (at the bottom of the list of existing Reports)
  3. Specify that it should allow generation in MS-Word, Excel, HTML and PDF formats
  4. Choose to add a Custom Section:

  5. Paste into the Query section the query itself.

SQL Query:

select R.REQUIREMENT_ID, R.TEST_CASE_ID, R.REQUIREMENT_NAME, R.TEST_CASE_NAME, R.PROJECT_ID as RqProductID, TC.PROJECT_ID AS TestCaseProductID, R.PROJECT_NAME as RqProductName, TC.PROJECT_Name AS TestCaseProductName
	 from SpiraTestEntities.R_RequirementTestCases as R 
INNER JOIN SpiraTestEntities.R_TestCases AS TC ON TC.TEST_CASE_ID=R.TEST_CASE_ID

You can filter results by a specific requirement, for that add the WHERE statement in the end of the query:

where R.REQUIREMENT_ID = 7

With this query, you will be able to see the list of requirements that have a cross-product test cases associated, the ID and name of the product that test cases belong to.

Additionally, you can add some additional details to the output table, like the test case execution status or its priority, owner, etc.:

select R.REQUIREMENT_ID, R.TEST_CASE_ID, R.REQUIREMENT_NAME, R.TEST_CASE_NAME, TC.EXECUTION_STATUS_NAME, TC.TEST_CASE_STATUS_NAME, TC.TEST_CASE_PRIORITY_NAME, TC.OWNER_NAME, R.PROJECT_ID as RqProductID, TC.PROJECT_ID AS TestCaseProductID, R.PROJECT_NAME as RqProductName, TC.PROJECT_Name AS TestCaseProductName
	 from SpiraTestEntities.R_RequirementTestCases as R 
INNER JOIN SpiraTestEntities.R_TestCases AS TC ON TC.TEST_CASE_ID=R.TEST_CASE_ID

where R.REQUIREMENT_ID = 7

6. Then click the 'Create Default Template' option to generate the following XSLT report template or use the generated XSLT template:

<?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>REQUIREMENT_ID</th><th>TEST_CASE_ID</th><th>REQUIREMENT_NAME</th><th>TEST_CASE_NAME</th><th>EXECUTION_STATUS_NAME</th><th>TEST_CASE_STATUS_NAME</th><th>TEST_CASE_PRIORITY_NAME</th><th>OWNER_NAME</th><th>RqProductID</th><th>TestCaseProductID</th><th>RqProductName</th><th>TestCaseProductName</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="REQUIREMENT_ID"/></td><td><xsl:value-of select="TEST_CASE_ID"/></td><td><xsl:value-of select="REQUIREMENT_NAME"/></td><td><xsl:value-of select="TEST_CASE_NAME"/></td><td><xsl:value-of select="EXECUTION_STATUS_NAME"/></td><td><xsl:value-of select="TEST_CASE_STATUS_NAME"/></td><td><xsl:value-of select="TEST_CASE_PRIORITY_NAME"/></td><td><xsl:value-of select="OWNER_NAME"/></td><td><xsl:value-of select="RqProductID"/></td><td><xsl:value-of select="TestCaseProductID"/></td><td><xsl:value-of select="RqProductName"/></td><td><xsl:value-of select="TestCaseProductName"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

7. Click [Save] twice and you can run your report.

Running the report and expected output

As a result of the report execution you should get something like: