Create a report:

This article assumes you are familiar with the basics of writing custom reports in Spira.
In this example we will build a custom report with custom ESQL section.

To create the report you need to:

  1. Go to Administration  > Edit Reports
  2. Click on Create a new Report (at the bottom of the list of existing Reports)
  3. Specify that it should allow generation in MS-Word, Excel, HTML and PDF formats
  4. Choose to add a Custom Section:

SQL Query:

  1. Choose the Task Folders from the predefined queries list
  2. You'll see the automatic query generated filtering available Task folders in this current product
  3. Click Preview Results to see the output 
    The R_TaskFolder custom report view contains a HIERARCHY_LEVEL and INDENT_LEVEL columns as well:

  4. This output can be customized to show the list of the tasks and the name of the containing folder:
SELECT TK.TASK_ID, TK.NAME AS TASK_NAME, TK.TASK_TYPE_NAME, TK.CREATION_DATE, TK.TASK_STATUS_NAME, TK.OWNER_NAME, TK.TASK_PRIORITY_NAME, TK.PROJECT_NAME, TF.NAME AS FOLDER_NAME
FROM SpiraTestEntities.R_Tasks AS TK
INNER JOIN SpiraTestEntities.R_TaskFolders AS TF ON TK.TASK_FOLDER_ID=TF.TASK_FOLDER_ID

 The output of this query execution will be the list of all available tasks in the system, with the containing folder names.

5. In case it is necessary to get the list of tasks for the actual product, then the WHERE filter should be added

SELECT TK.TASK_ID, TK.NAME AS TASK_NAME, TK.TASK_TYPE_NAME, TK.CREATION_DATE, TK.TASK_STATUS_NAME, TK.OWNER_NAME, TK.TASK_PRIORITY_NAME, TK.PROJECT_NAME, TF.NAME AS FOLDER_NAME
FROM SpiraTestEntities.R_Tasks AS TK
INNER JOIN SpiraTestEntities.R_TaskFolders AS TF ON TK.TASK_FOLDER_ID=TF.TASK_FOLDER_ID
WHERE TK.PROJECT_ID = ${ProjectId}

6. Click the 'Create Default Template' option to generate the following XSLT report template or use the generated XSLT 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>TASK_ID</th><th>NAME</th><th>TASK_TYPE_NAME</th><th>CREATION_DATE</th><th>TASK_STATUS_NAME</th><th>OWNER_NAME</th><th>TASK_PRIORITY_NAME</th><th>PROJECT_NAME</th><th>FOLDER_NAME</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="TASK_ID"/></td><td><xsl:value-of select="NAME"/></td><td><xsl:value-of select="TASK_TYPE_NAME"/></td><td><xsl:value-of select="CREATION_DATE"/></td><td><xsl:value-of select="TASK_STATUS_NAME"/></td><td><xsl:value-of select="OWNER_NAME"/></td><td><xsl:value-of select="TASK_PRIORITY_NAME"/></td><td><xsl:value-of select="PROJECT_NAME"/></td><td><xsl:value-of select="FOLDER_NAME"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

7. Save the changes

8. Go to Reporting module to execute the report

Running the report and expected output

And this is what the report should look like: