Create a 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 basic report without any filtering yet you need to:

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

You can choose predefined query to display all the information that is merged into this particular View:

The query execution results the list of the Products including its all columns, that might not be very useful.

Here is the modified query to get the brief information report about the products in the system:

SELECT PR.PROJECT_ID
      ,PR.PROJECT_GROUP_ID
      ,PR.PROJECT_TEMPLATE_ID
      ,PR.NAME
      ,PR.DESCRIPTION
      ,PR.CREATION_DATE
      ,PR.WEBSITE
      ,(CASE WHEN (CAST (PR.IS_ACTIVE as String)) = '1' THEN 'YES' ELSE 'NO' END) AS IS_ACTIVE 
      ,(CASE WHEN (CAST(PR.IS_REQ_STATUS_BY_TASKS as String)) = '1' THEN 'YES' ELSE 'NO' END) AS IS_REQ_STATUS_BY_TASKS 
      ,(CASE WHEN (CAST(PR.IS_REQ_STATUS_BY_TEST_CASES as String)) = '1' THEN 'YES' ELSE 'NO' END) AS IS_REQ_STATUS_BY_TEST_CASES
      ,(CASE WHEN (CAST(PR.IS_EFFORT_TEST_CASES as String)) = '1' THEN 'YES' ELSE 'NO' END) AS IS_EFFORT_TEST_CASES
      ,(CASE WHEN (CAST(PR.IS_REQ_STATUS_AUTO_PLANNED as String)) = '1' THEN 'YES' ELSE 'NO' END) AS IS_REQ_STATUS_AUTO_PLANNED
      ,PR.START_DATE
      ,PR.END_DATE
      ,PR.PERCENT_COMPLETE
      ,PR.REQUIREMENT_COUNT
      ,PR.PROJECT_GROUP_NAME AS PROGRAM_NAME
FROM SpiraTestEntities.R_PROJECTS AS PR
WHERE PR.IS_ACTIVE = True

The CASE statement here translates 1 and 0s to YES and NO, so to make the column value more meaningful.

The list can be also filtered by active and inactive status of the products, by adding a WHERE statement in the end of the above query:

WHERE PR.IS_ACTIVE = True

So finally, that can be checked by clicking on the Preview Results button (1):

If all good then it is required to create a default template (2) so the report can be used by other users from the report module.

Otherwise, just copy and paste below given code:

<?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_GROUP_ID</th><th>PROJECT_TEMPLATE_ID</th><th>NAME</th><th>DESCRIPTION</th><th>CREATION_DATE</th><th>WEBSITE</th><th>IS_ACTIVE</th><th>IS_REQ_STATUS_BY_TASKS</th><th>IS_REQ_STATUS_BY_TEST_CASES</th><th>IS_EFFORT_TEST_CASES</th><th>IS_REQ_STATUS_AUTO_PLANNED</th><th>START_DATE</th><th>END_DATE</th><th>PERCENT_COMPLETE</th><th>REQUIREMENT_COUNT</th><th>PROGRAM_NAME</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="PROJECT_ID"/></td><td><xsl:value-of select="PROJECT_GROUP_ID"/></td><td><xsl:value-of select="PROJECT_TEMPLATE_ID"/></td><td><xsl:value-of select="NAME"/></td><td><xsl:value-of select="DESCRIPTION"/></td><td><xsl:value-of select="CREATION_DATE"/></td><td><xsl:value-of select="WEBSITE"/></td><td><xsl:value-of select="IS_ACTIVE"/></td><td><xsl:value-of select="IS_REQ_STATUS_BY_TASKS"/></td><td><xsl:value-of select="IS_REQ_STATUS_BY_TEST_CASES"/></td><td><xsl:value-of select="IS_EFFORT_TEST_CASES"/></td><td><xsl:value-of select="IS_REQ_STATUS_AUTO_PLANNED"/></td><td><xsl:value-of select="START_DATE"/></td><td><xsl:value-of select="END_DATE"/></td><td><xsl:value-of select="PERCENT_COMPLETE"/></td><td><xsl:value-of select="REQUIREMENT_COUNT"/></td><td><xsl:value-of select="PROGRAM_NAME"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

 

Output result

The result of the execution should be similar to this: