Creating the Custom Report

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
  3. Specify that it should allow generation in MS-Word, Excel, HTML and PDF formats
  4. Choose to add a Custom Section:

  5. Into the Query section paste the below script:
    (Feel free to change the columns in SELECT statement upon need, do not forget to regenerate the Default Template after any changes made)
SELECT 
PM.USER_ID, (US.FIRST_NAME + ' ' + US.LAST_NAME) as UserName, PM.PROJECT_ID, PM.PROJECT_NAME, PM.PROJECT_ROLE_NAME
FROM  SpiraTestEntities.R_ProjectMembership AS PM
INNER JOIN  SpiraTestEntities.R_Users AS US ON US.USER_ID=PM.USER_ID

Click on Create a Default Template or copy the generated template into that section from below:

<?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>USER_ID</th><th>UserName</th><th>PROJECT_ID</th><th>PROJECT_NAME</th><th>PROJECT_ROLE_NAME</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="USER_ID"/></td><td><xsl:value-of select="UserName"/></td><td><xsl:value-of select="PROJECT_ID"/></td><td><xsl:value-of select="PROJECT_NAME"/></td><td><xsl:value-of select="PROJECT_ROLE_NAME"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

Once done, give a report name and click Save twice. Now its ready for use.

You may need to filter only one specific Role sometimes - this can be done using Excel after exporting all the users and roles OR you can get already a filtered data.
Basic example is here - adding WHERE statement in the end of the initial query lets you get a filtered data - only Product Owners will be included in the list:

SELECT 
PM.USER_ID, (US.FIRST_NAME + ' ' + US.LAST_NAME) as UserName, PM.PROJECT_ID, PM.PROJECT_NAME, PM.PROJECT_ROLE_NAME
FROM  SpiraTestEntities.R_ProjectMembership AS PM
INNER JOIN  SpiraTestEntities.R_Users AS US ON US.USER_ID=PM.USER_ID
WHERE PM.PROJECT_ROLE_NAME='Product Owner'

You can sort the results by any column using the ORDER BY statement:

SELECT (US.FIRST_NAME + ' ' + US.LAST_NAME) as UserName, PM.PROJECT_ID, PM.PROJECT_NAME, PM.PROJECT_ROLE_NAME
FROM [SpiraPlan].[dbo].[RPT_PROJECT_MEMBERSHIP] AS PM

INNER JOIN [dbo].[RPT_USERS] AS US ON US.[USER_ID]=PM.USER_ID

WHERE PM.PROJECT_ROLE_NAME='Product Owner'
ORDER BY PM.PROJECT_ID desc

Do not forget to generate a new XSLT template for the modified query, otherwise it will just not include the modified request in the output.