Create Test Case Folder Report

Solution 1: Getting Test folder ID, folder name and parent folder ID

Once you have created your new custom report (from Administration > Reporting > Edit Reports, follow the following steps:

• Add a custom section to the report
• Give this custom section a name (eg "Folders")
• In the query field in the custom section dialog paste the following code

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

• NOTE: this is the standard query that Spira will automatically generate for you if you select "Test Case Folders" from the "Add New Query" dropdown just above the query field.
• Use the XSLT below in the Template field in the custom section dialog
• Make sure you select the reports formats you want

<?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" style="width:100%">
      <tr>
        <th>Folder #</th>
        <th>Name</th>
        <th>Parent Folder #</th>
      </tr>
      <xsl:for-each select="ROW">
        <tr>
          <td>
            <xsl:value-of select="TEST_CASE_FOLDER_ID"/>
          </td>
          <td>
            <xsl:value-of select="NAME"/>
          </td>
          <td>
            <xsl:value-of select="PARENT_TEST_CASE_FOLDER_ID"/>
          </td>
        </tr>
      </xsl:for-each>
    </table>
  </xsl:template>
</xsl:stylesheet>

And this is what the report should look like:

Solution 2: Pulling Tests count in a folder and folder's indent level

In the query field in the custom section dialog paste the following code:

SELECT 
TCF.NAME AS Folder_Name,  TCF.INDENT_LEVEL, COUNT(TC.TEST_CASE_ID) as Number_Of_Tests_In
  FROM SpiraTestEntities.R_TESTCASEFOLDERS AS TCF
  INNER JOIN SpiraTestEntities.R_TESTCASES AS TC ON TC.TEST_CASE_FOLDER_ID=TCF.TEST_CASE_FOLDER_ID
WHERE TC.PROJECT_ID = ${ProjectId}
  GROUP BY TCF.NAME,TCF.INDENT_LEVEL

Use the XSLT below in the Template field in the custom section dialog or click Create Default 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>Folder_Name</th><th>INDENT_LEVEL</th><th>Number_Of_Tests_In</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="Folder_Name"/></td><td><xsl:value-of select="INDENT_LEVEL"/></td><td><xsl:value-of select="Number_Of_Tests_In"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

The output result should look similar to this one: