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:

  5. Paste into the Query section the query itself.

Report of Source Code permission status

SQL Query:

SELECT X.USERNAME, X.SOURCE_CODE_VIEW_PERMISSION, X.SOURCE_CODE_EDIT_PERMISSION FROM (SELECT DISTINCT US.USER_NAME AS USERNAME
          ,US.FIRST_NAME + ' ' + US.LAST_NAME as FULL_NAME
	  ,PRM.PROJECT_NAME
	  ,PR.NAME AS PRODUCT_ROLE_NAME
          ,(CASE WHEN PR.IS_SOURCE_CODE_VIEW = TRUE THEN 'YES' ELSE 'NO' END) AS SOURCE_CODE_VIEW_PERMISSION
      ,(CASE WHEN  PR.IS_SOURCE_CODE_EDIT  = TRUE THEN 'YES' ELSE 'NO' END) AS SOURCE_CODE_EDIT_PERMISSION
  FROM SpiraTestEntities.R_ProjectMembership AS PRM
  INNER JOIN SpiraTestEntities.R_USERS AS US ON US.USER_ID = PRM.USER_ID
  INNER JOIN SpiraTestEntities.R_PROJECTROLES AS PR ON PRM.PROJECT_ROLE_ID = PR.PROJECT_ROLE_ID 
where PRM.PROJECT_ID = ${ProjectId} AND PR.IS_SOURCE_CODE_EDIT  = FALSE) AS X

You can also filter by the permission status for source code edit operation, lets say who is not allowed to edit the source code but allowed to view.
For that modify the last WHERE statement and replace it with:

where PRM.PROJECT_ID = ${ProjectId} AND PR.IS_SOURCE_CODE_EDIT  = FALSE

6. Then 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>USERNAME</th><th>SOURCE_CODE_VIEW_PERMISSION</th><th>SOURCE_CODE_EDIT_PERMISSION</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="USERNAME"/></td><td><xsl:value-of select="SOURCE_CODE_VIEW_PERMISSION"/></td><td><xsl:value-of select="SOURCE_CODE_EDIT_PERMISSION"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

7. Click [Save] twice and you can run your report.

Running the report and expected output

As a result of the report execution you should get something like:

Report of Bulk Edit permission

SQL Query

In this query we're filtering only those records that have a BulkEdit permission set to True:

SELECT US.USER_NAME AS USERNAME
          ,US.FIRST_NAME + ' ' + US.LAST_NAME as FULL_NAME
	  ,PRM.PROJECT_NAME
	  ,PR.NAME AS PRODUCT_ROLE_NAME
          ,PR.ARTIFACT
          ,(CASE WHEN (PR.PERMISSION) = 'Permission_BulkEdit' THEN 'YES'ELSE 'NO' END) AS BULK_EDIT_PERMISSION
  FROM SpiraTestEntities.R_ProjectMembership AS PRM
  INNER JOIN SpiraTestEntities.R_USERS AS US ON US.USER_ID = PRM.USER_ID
  INNER JOIN SpiraTestEntities.R_PROJECTROLES AS PR ON PRM.PROJECT_ROLE_ID = PR.PROJECT_ROLE_ID 
where PRM.PROJECT_ID = ${ProjectId} AND PR.PERMISSION = 'Permission_BulkEdit'

If you like to get this list for all the products in the system, no matter of the status of BulkEdit privilege status, then just remove the first half of the WHERE statement (PRM.PROJECT_ID = ${ProjectId} AND) so the report will display the list of users with BulkEdit permission in other Products as well.

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>USERNAME</th><th>FULL_NAME</th><th>PROJECT_NAME</th><th>PRODUCT_ROLE_NAME</th><th>ARTIFACT</th><th>BULK_EDIT_PERMISSION</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="USERNAME"/></td><td><xsl:value-of select="FULL_NAME"/></td><td><xsl:value-of select="PROJECT_NAME"/></td><td><xsl:value-of select="PRODUCT_ROLE_NAME"/></td><td><xsl:value-of select="ARTIFACT"/></td><td><xsl:value-of select="BULK_EDIT_PERMISSION"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

Running the report and expected output

As a result of the report execution you should get something like: