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:

Getting the list of users by Role and Product

  1. Insert the below query into the Query section:
    (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.

Pulling the Last Activity and Last Login Date

To get the important details about the user activity, such as a Last Login and Last Activity Date, please use the following modified query:

SELECT R.USER_ID, R.FIRST_NAME + ' ' + R.LAST_NAME as FULL_NAME, R.USER_NAME, R.EMAIL_ADDRESS, R.LAST_LOGIN_DATE, R.DEPARTMENT, R.LAST_ACTIVITY_DATE, R.IS_ADMIN, R.IS_ACTIVE
FROM
SpiraTestEntities.R_Users as R 
WHERE R.IS_ACTIVE = True

Save the changes and click on Create Default Template, so to allow creation of the report based on this query from Report Center.

The output should be similar to: