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:
- Go to Administration > Edit Reports
- Create a new Report
- Specify that it should allow generation in MS-Word, Excel, HTML and PDF formats
- Choose to add a Custom Section:
- 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.