The following Entity SQL (ESQL) should be used in the Spira custom report writer:
select 'PR' + cast(R.PROJECT_ID as String) as PROJECT_ID, R.PROJECT_NAME, 'TC' + cast(R.TEST_CASE_ID as String) as TEST_CASE_ID, R.NAME as TEST_CASE_NAME, R.EXECUTION_STATUS_NAME, R.EXECUTION_DATE
from SpiraTestEntities.R_TestCases as R
where R.PROJECT_GROUP_ID = ${ProjectGroupId}
order by R.PROJECT_NAME, R.NAME
Then click the 'Create Default Template' option to generate the following XSLT report 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>Project ID</th><th>Project Name</th><th>Test Case ID</th><th>Test Case Name</th><th>Execution Status</th><th>Last Executed</th></tr>
<xsl:for-each select="ROW">
<tr><td><xsl:value-of select="PROJECT_ID"/></td><td><xsl:value-of select="PROJECT_NAME"/></td><td><xsl:value-of select="TEST_CASE_ID"/></td><td><xsl:value-of select="TEST_CASE_NAME"/></td><td><xsl:value-of select="EXECUTION_STATUS_NAME"/></td><td><xsl:value-of select="EXECUTION_DATE"/></td>
</tr>
</xsl:for-each>
</table>
</xsl:template>
</xsl:stylesheet>
Viewing the Report
If you run this report in Excel or HTML format, it will generate the following output:
