Creating a Report of User Activity and Effort

Creating the Custom Report

The following Entity SQL (ESQL) should be used in the Spira custom report writer:

select HC.USER_NAME, HC.ARTIFACT_TYPE_NAME, HC.ARTIFACT_ID, HC.ARTIFACT_DESC as ARTIFACT_NAME, HC.CHANGETYPE_NAME, HC.CHANGE_DATE, HD.FIELD_NAME, HD.OLD_VALUE, HD.NEW_VALUE
from SpiraTestEntities.R_HistoryChangeSets as HC
inner join SpiraTestEntities.R_HistoryDetails as HD on HC.CHANGESET_ID = HD.CHANGESET_ID
where HD.FIELD_NAME = 'ActualEffort' or HD.FIELD_NAME = 'RemainingEffort'
order by HC.USER_NAME, HC.CHANGE_DATE

Then click the 'Create Default Template' option to generate the following XSLT report 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_NAME</th><th>ARTIFACT_TYPE_NAME</th><th>ARTIFACT_ID</th><th>ARTIFACT_NAME</th><th>CHANGETYPE_NAME</th><th>CHANGE_DATE</th><th>FIELD_NAME</th><th>OLD_VALUE</th><th>NEW_VALUE</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="USER_NAME"/></td><td><xsl:value-of select="ARTIFACT_TYPE_NAME"/></td><td><xsl:value-of select="ARTIFACT_ID"/></td><td><xsl:value-of select="ARTIFACT_NAME"/></td><td><xsl:value-of select="CHANGETYPE_NAME"/></td><td><xsl:value-of select="CHANGE_DATE"/></td><td><xsl:value-of select="FIELD_NAME"/></td><td><xsl:value-of select="OLD_VALUE"/></td><td><xsl:value-of select="NEW_VALUE"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

Viewing the Report

If you run this report in Excel or HTML format, it will generate the following output: