Create a custom report based on a built-in query

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:

That auto-generates following query:

select value R from SpiraTestEntities.R_Requirements as R where R.PROJECT_ID = ${ProjectId}

You can run it instantly to see the results by clicking on Preview Results:

This query displays all the Requirements in the particular Product - with all its parameters, unfiltered.

If that is ok for you, use the 'Create Default Template' option to generate the following XSLT report template and then Save the report.

Creating a report with a modified query

The following Entity SQL (ESQL) example modifies the custom report above to now additionally filter the results to only get requirements where the first custom property (called CUST_01) has a value of "Y" - this is used for filtering boolean custom fields to only get "true" results:

select value R from SpiraTestEntities.R_Requirements as R where R.CUST_01 = 'Y' AND R.PROJECT_ID = ${ProjectId}

This displays all the information from the Requirement view, including the information you might not need.
You can make this report to look more like a Requirement list page in UI, but filtered by the same CUST_01 property (or replace it with the Custom Property that applies to your particular case):

select 
R.REQUIREMENT_ID AS ID,
R.Name,
R.RELEASE_VERSION_NUMBER as Release,
R.IMPORTANCE_Name as Importance,
R.REQUIREMENT_STATUS_NAME as Status,
R.REQUIREMENT_TYPE_NAME as Type,
R.OWNER_NAME as Owner,
R.IS_SUMMARY as IS_Parent,
R.Cust_01 as Flag
from SpiraTestEntities.R_Requirements as R 
where 
R.CUST_01 = 'Y' AND R.PROJECT_ID = ${ProjectId}

To get the number of the Custom Property you'd like to pull you should check the Requirement's custom properties list to get this Field #:

The result of the above query execution will generate the following output:

Click to 'Create Default Template' option to generate the following XSLT report template and then Save the report:

<?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>ID</th><th>Name</th><th>Release</th><th>Importance</th><th>Status</th><th>Type</th><th>Owner</th><th>IS_Parent</th><th>Flag</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="ID"/></td><td><xsl:value-of select="Name"/></td><td><xsl:value-of select="Release"/></td><td><xsl:value-of select="Importance"/></td><td><xsl:value-of select="Status"/></td><td><xsl:value-of select="Type"/></td><td><xsl:value-of select="Owner"/></td><td><xsl:value-of select="IS_Parent"/></td><td><xsl:value-of select="Flag"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

Viewing the Report

Assuming that the Requirements list page looks as below 

Then if you run the report in Excel or HTML format, it will generate the following output: