Create a Report based on 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 report 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 a predefined query to display all the information that is merged into this particular View:

That generates this query:

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

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

If that looks good to you, click the '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>PROJECT_ID</th><th>PROJECT_GROUP_ID</th><th>NAME</th><th>DESCRIPTION</th><th>CREATION_DATE</th><th>WEBSITE</th><th>WORKING_HOURS</th><th>WORKING_DAYS</th><th>NON_WORKING_HOURS</th><th>TASK_DEFAULT_EFFORT</th><th>PROJECT_GROUP_NAME</th><th>PROJECT_GROUP_DESCRIPTION</th><th>IS_ACTIVE</th><th>IS_TIME_TRACK_INCIDENTS</th><th>IS_TIME_TRACK_TASKS</th><th>IS_EFFORT_INCIDENTS</th><th>IS_EFFORT_TASKS</th><th>IS_TASKS_AUTO_CREATE</th><th>REQ_DEFAULT_ESTIMATE</th><th>REQ_POINT_EFFORT</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>PROJECT_TEMPLATE_ID</th><th>START_DATE</th><th>END_DATE</th><th>PERCENT_COMPLETE</th><th>REQUIREMENT_COUNT</th><th>CUST_01</th><th>CUST_02</th><th>CUST_03</th><th>CUST_04</th><th>CUST_05</th><th>CUST_06</th><th>CUST_07</th><th>CUST_08</th><th>CUST_09</th><th>CUST_10</th><th>CUST_11</th><th>CUST_12</th><th>CUST_13</th><th>CUST_14</th><th>CUST_15</th><th>CUST_16</th><th>CUST_17</th><th>CUST_18</th><th>CUST_19</th><th>CUST_20</th><th>CUST_21</th><th>CUST_22</th><th>CUST_23</th><th>CUST_24</th><th>CUST_25</th><th>CUST_26</th><th>CUST_27</th><th>CUST_28</th><th>CUST_29</th><th>CUST_30</th><th>CONCURRENCY_GUID</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="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="WORKING_HOURS"/></td><td><xsl:value-of select="WORKING_DAYS"/></td><td><xsl:value-of select="NON_WORKING_HOURS"/></td><td><xsl:value-of select="TASK_DEFAULT_EFFORT"/></td><td><xsl:value-of select="PROJECT_GROUP_NAME"/></td><td><xsl:value-of select="PROJECT_GROUP_DESCRIPTION"/></td><td><xsl:value-of select="IS_ACTIVE"/></td><td><xsl:value-of select="IS_TIME_TRACK_INCIDENTS"/></td><td><xsl:value-of select="IS_TIME_TRACK_TASKS"/></td><td><xsl:value-of select="IS_EFFORT_INCIDENTS"/></td><td><xsl:value-of select="IS_EFFORT_TASKS"/></td><td><xsl:value-of select="IS_TASKS_AUTO_CREATE"/></td><td><xsl:value-of select="REQ_DEFAULT_ESTIMATE"/></td><td><xsl:value-of select="REQ_POINT_EFFORT"/></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="PROJECT_TEMPLATE_ID"/></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="CUST_01"/></td><td><xsl:value-of select="CUST_02"/></td><td><xsl:value-of select="CUST_03"/></td><td><xsl:value-of select="CUST_04"/></td><td><xsl:value-of select="CUST_05"/></td><td><xsl:value-of select="CUST_06"/></td><td><xsl:value-of select="CUST_07"/></td><td><xsl:value-of select="CUST_08"/></td><td><xsl:value-of select="CUST_09"/></td><td><xsl:value-of select="CUST_10"/></td><td><xsl:value-of select="CUST_11"/></td><td><xsl:value-of select="CUST_12"/></td><td><xsl:value-of select="CUST_13"/></td><td><xsl:value-of select="CUST_14"/></td><td><xsl:value-of select="CUST_15"/></td><td><xsl:value-of select="CUST_16"/></td><td><xsl:value-of select="CUST_17"/></td><td><xsl:value-of select="CUST_18"/></td><td><xsl:value-of select="CUST_19"/></td><td><xsl:value-of select="CUST_20"/></td><td><xsl:value-of select="CUST_21"/></td><td><xsl:value-of select="CUST_22"/></td><td><xsl:value-of select="CUST_23"/></td><td><xsl:value-of select="CUST_24"/></td><td><xsl:value-of select="CUST_25"/></td><td><xsl:value-of select="CUST_26"/></td><td><xsl:value-of select="CUST_27"/></td><td><xsl:value-of select="CUST_28"/></td><td><xsl:value-of select="CUST_29"/></td><td><xsl:value-of select="CUST_30"/></td><td><xsl:value-of select="CONCURRENCY_GUID"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

Creating a report with modified query

The following Entity SQL (ESQL) should be used in the Spira custom report writer to get brief information about your current Products and Programs:

select 
R.PROJECT_GROUP_ID AS Program_ID,
R.PROJECT_GROUP_NAME as Program_Name,
R.PROJECT_GROUP_DESCRIPTION as Program_Description,
R.PROJECT_ID as Product_ID,
R.NAME as Product_Name,
R.DESCRIPTION as Product_Description,
R.CREATION_DATE as Product_CreationDate

from SpiraTestEntities.R_Projects as R where R.IS_ACTIVE = TRUE

Then 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>Program_ID</th><th>Program_Name</th><th>Program_Description</th><th>Product_ID</th><th>Product_Name</th><th>Product_Description</th><th>Product_CreationDate</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="Program_ID"/></td><td><xsl:value-of select="Program_Name"/></td><td><xsl:value-of select="Program_Description"/></td><td><xsl:value-of select="Product_ID"/></td><td><xsl:value-of select="Product_Name"/></td><td><xsl:value-of select="Product_Description"/></td><td><xsl:value-of select="Product_CreationDate"/></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: