Create a 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 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 Project Group Membership Users from the predefined queries list
  2. You'll see the automatic query generates the list of Users that are members of current Project_Group or Program and their roles
  3. Click Preview Results to see the output the R_ProjectGroup_MembershipUsers custom report view for current program:

     
  4.  This output can be customized to show the list not only programs but also corresponding projects the user is member of, with corresponding program roles. For that insert below ESQL query into the Query field:
    SELECT DISTINCT PG.USER_ID, PG.USER_FULLNAME, PG.USER_NAME, PG.PROJECT_GROUP_ROLE_NAME AS PROGRAM_ROLE, PG.PROJECT_GROUP_NAME AS PROGRAM_NAME
FROM SpiraTestEntities.R_ProjectGroup_MembershipUsers AS PG 
JOIN SpiraTestEntities.R_ProjectMembership AS PM ON PG.USER_ID=PM.USER_ID

    The result will be listing all users in the system that have a program membership with their roles.

  5.  If we add a filtering by program token ${ProjectGroupId} then we will get only users for the currently active program only:
    SELECT DISTINCT PG.USER_ID, PG.USER_FULLNAME, PG.USER_NAME, PG.PROJECT_GROUP_ROLE_NAME AS PROGRAM_ROLE, PG.PROJECT_GROUP_NAME AS PROGRAM_NAME, PM.PROJECT_NAME
JOIN SpiraTestEntities.R_ProjectMembership AS PM ON PG.USER_ID=PM.USER_ID
where PG.PROJECT_GROUP_ID = ${ProjectGroupId}

    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>USER_ID</th><th>USER_FULLNAME</th><th>USER_NAME</th><th>PROGRAM_ROLE</th><th>PROGRAM_NAME</th><th>PROJECT_NAME</th><th>PROJECT_ROLE</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="USER_ID"/></td><td><xsl:value-of select="USER_FULLNAME"/></td><td><xsl:value-of select="USER_NAME"/></td><td><xsl:value-of select="PROGRAM_ROLE"/></td><td><xsl:value-of select="PROGRAM_NAME"/></td>
      </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: