Create a report:

This article assumes you are familiar with the basics of writing custom reports in Spira.
In this example we will build a custom report with 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:

SQL Query:

Into the Custom Query section paste this ESQL query: 

select R.TEST_CASE_ID ,R.NAME, R.EXECUTION_STATUS_NAME AS TESTCASE_EXECUTION_STATUS, R.DESCRIPTION, TCF.NAME AS FOLDER, TR.TEST_RUN_ID, TR.EXECUTION_STATUS_NAME AS TEST_RUN_EXEC_STATUS, TR.RELEASE_NAME, TR.RUNNER_STACK_TRACE, TR.RUNNER_MESSAGE
from SpiraTestEntities.R_TestCases as R
INNER JOIN SpiraTestEntities.R_TestCaseFolders AS TCF ON R.TEST_CASE_FOLDER_ID=TCF.TEST_CASE_FOLDER_ID
left join SpiraTestEntities.R_TestRuns as TR on TR.TEST_CASE_ID = R.TEST_CASE_ID  and TR.TEST_RUN_ID  = SUM ( SELECT VALUE max(TR.TEST_RUN_ID) 
FROM SpiraTestEntities.R_TestRuns as TR where TR.TEST_CASE_ID = R.TEST_CASE_ID ) 
WHERE R.PROJECT_ID = ${ProjectId}

Click on Create Default Template or simply copy and paste below XSLT:

<?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>TESTCASE_EXECUTION_STATUS</th><th>DESCRIPTION</th><th>FOLDER</th><th>TEST_RUN_ID</th><th>TEST_RUN_EXEC_STATUS</th><th>RELEASE_NAME</th><th>RUNNER_STACK_TRACE</th><th>RUNNER_MESSAGE</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="TESTCASE_EXECUTION_STATUS"/></td><td><xsl:value-of select="DESCRIPTION"/></td><td><xsl:value-of select="FOLDER"/></td><td><xsl:value-of select="TEST_RUN_ID"/></td><td><xsl:value-of select="TEST_RUN_EXEC_STATUS"/></td><td><xsl:value-of select="RELEASE_NAME"/></td><td><xsl:value-of select="RUNNER_STACK_TRACE"/></td><td><xsl:value-of select="RUNNER_MESSAGE"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

 

Output Results:

You should be able to get some preliminary results by checking the query with Preview Results button:

Click Save button twice.
Now this custom report is available for all the users that have access to Report center: