Create a Report

To create a custom report you need to:

  1. Go to Administration  > Edit Reports
  2. Create a new Report
  3. Specify that it should allow generation in MS-Word, Excel, HTML and PDF formats
  4. Choose to add a Custom Section:
  5. Insert the ESQL query, depending on your need (see specific examples below)

1. List all Capabilities assigned to Program Milestones

To get the information about which capabilities are assigned to which program milestones, you can use a report as below. Insert below query into the window:

select CP.MILESTONE_ID as MilestoneID, CP.MILESTONE_NAME as MilestoneName, CP.NAME as CapabilityName, CP.TYPE_NAME as CapabilityType, CP.STATUS_NAME as CapabilityStatus, CP.OWNER_NAME as Owner, CP.CAPABILITY_ID as CapabilityID
from SpiraTestEntities.R_ProjectGroup_Capabilities as CP
INNER JOIN SpiraTestEntities.R_ProjectGroup_Milestones as R ON R.PROJECT_GROUP_MILESTONE_ID = CP.Milestone_ID

Then click on Create Default Template or simply copy the following 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>MilestoneID</th><th>MilestoneName</th><th>CapabilityName</th><th>CapabilityType</th><th>CapabilityStatus</th><th>Owner</th><th>CapabilityID</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="MilestoneID"/></td><td><xsl:value-of select="MilestoneName"/></td><td><xsl:value-of select="CapabilityName"/></td><td><xsl:value-of select="CapabilityType"/></td><td><xsl:value-of select="CapabilityStatus"/></td><td><xsl:value-of select="Owner"/></td><td><xsl:value-of select="CapabilityID"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

Click on Preview Results, you should be able to see the screen with your data:

Save the report and its ready for use.

2. List all Releases assigned to Program Milestones

This example report shows all the releases that have been assigned to any available program milestones. Insert this query into the Query field:

select MRL.PROJECT_GROUP_MILESTONE_NAME as Milestone, RL.FULL_NAME as Release, RL.RELEASE_STATUS_NAME as ReleaseStatus, RL.START_DATE as ReleaseStartDate, RL.END_DATE as ReleaseEndDate, RL.PROJECT_NAME as Product, 
RL.OWNER_NAME as Owner, RL.RELEASE_ID as ReleaseID from SpiraTestEntities.R_Releases as RL
INNER JOIN SpiraTestEntities.R_ProjectGroup_Milestone_Project_Releases as MRL ON RL.RELEASE_ID = MRL.RELEASE_ID

Click on Create Default Template or copy the below 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>Milestone</th><th>Release</th><th>ReleaseStatus</th><th>ReleaseStartDate</th><th>ReleaseEndDate</th><th>Product</th><th>Owner</th><th>ReleaseID</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="Milestone"/></td><td><xsl:value-of select="Release"/></td><td><xsl:value-of select="ReleaseStatus"/></td><td><xsl:value-of select="ReleaseStartDate"/></td><td><xsl:value-of select="ReleaseEndDate"/></td><td><xsl:value-of select="Product"/></td><td><xsl:value-of select="Owner"/></td><td><xsl:value-of select="ReleaseID"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

Clicking on Preview Results button should display similar information as:

Now save the report and it is ready for use from the Report Center page.

3. List all Requirements assigned to Capabilities

In this final example, we create a custom report that shows all the requirements assigned to any available Capabilities. Insert this query into the Query field:

select 
CR.CAPABILITY_NAME as CapabilityName, 
CR.REQUIREMENT_NAME as RequirementName, 
RQ.REQUIREMENT_STATUS_NAME as RequirementStatus, 
RQ.PROJECT_NAME as Product, 
RQ.OWNER_NAME as RequirementOwner, 
CR.REQUIREMENT_ID as RequirementID 
from SpiraTestEntities.R_Requirements as RQ
INNER JOIN SpiraTestEntities.R_ProjectGroup_Capability_Project_Requirements as CR ON RQ.REQUIREMENT_ID = CR.REQUIREMENT_ID

Click on Create Default Template or just copy this code inside that field: 

<?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>CapabilityName</th><th>RequirementName</th><th>RequirementStatus</th><th>Product</th><th>RequirementOwner</th><th>RequirementID</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="CapabilityName"/></td><td><xsl:value-of select="RequirementName"/></td><td><xsl:value-of select="RequirementStatus"/></td><td><xsl:value-of select="Product"/></td><td><xsl:value-of select="RequirementOwner"/></td><td><xsl:value-of select="RequirementID"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

Preview Results button should display information similar to:

Save the report, its now ready to use.