Writing a Report to Show the Electronic Signatures in SpiraTeam

The following Entity SQL (ESQL) query can be used to display such reports:

select H.CHANGESET_ID, H.ARTIFACT_TYPE_NAME, H.ARTIFACT_ID, H.ARTIFACT_DESC, H.CHANGETYPE_NAME, H.USER_NAME, H.SIGNATURE_HASH, C.COMMENT_TEXT as MEANING
from SpiraTestEntities.R_HistoryChangeSets as H
join SpiraTestEntities.R_Comments as C on C.CREATION_DATE = H.CHANGE_DATE
and H.ARTIFACT_ID = C.ARTIFACT_ID
and H.ARTIFACT_TYPE_ID = C.ARTIFACT_TYPE_ID
and H.SIGNATURE_HASH is not null

You enter that in the 'Query' section, then click the 'Create Default Template' and click Save.

If you want to also include the date on the report, please use this modified version:

select H.CHANGESET_ID, H.ARTIFACT_TYPE_NAME, H.ARTIFACT_ID, H.ARTIFACT_DESC, H.CHANGETYPE_NAME, H.USER_NAME, H.SIGNATURE_HASH, C.COMMENT_TEXT as MEANING, H.CHANGE_DATE
from SpiraTestEntities.R_HistoryChangeSets as H
join SpiraTestEntities.R_Comments as C on C.CREATION_DATE = H.CHANGE_DATE
and H.ARTIFACT_ID = C.ARTIFACT_ID
and H.ARTIFACT_TYPE_ID = C.ARTIFACT_TYPE_ID
and H.SIGNATURE_HASH is not null

To format the XML date as something more readable, you could use the following 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>CHANGESET_ID</th><th>ARTIFACT_TYPE_NAME</th><th>ARTIFACT_ID</th><th>ARTIFACT_DESC</th><th>CHANGETYPE_NAME</th><th>USER_NAME</th><th>SIGNATURE_HASH</th><th>MEANING</th><th>CHANGE_DATE</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="CHANGESET_ID"/></td><td><xsl:value-of select="ARTIFACT_TYPE_NAME"/></td><td><xsl:value-of select="ARTIFACT_ID"/></td><td><xsl:value-of select="ARTIFACT_DESC"/></td><td><xsl:value-of select="CHANGETYPE_NAME"/></td><td><xsl:value-of select="USER_NAME"/></td><td><xsl:value-of select="SIGNATURE_HASH"/></td><td><xsl:value-of select="MEANING"/></td>
<td>
  <xsl:call-template name="format-date">
    <xsl:with-param name="datetime" select="CreationDate" />
  </xsl:call-template>
</td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
  <xsl:template name="format-date">
    <xsl:param name="datetime"/>
    <xsl:variable name="date" select="substring-before($datetime, 'T')" />
    <xsl:variable name="year" select="substring-before($date, '-')" />
    <xsl:variable name="month" select="substring-before(substring-after($date, '-'), '-')" />
    <xsl:variable name="day" select="substring-after(substring-after($date, '-'), '-')" />
    <xsl:variable name="time" select="substring-before(substring-after($datetime, 'T'), '.')" />
    <xsl:variable name="monthname">
      <xsl:choose>
        <xsl:when test="$month='01'">
          <xsl:value-of select="'Jan'"/>
        </xsl:when>
        <xsl:when test="$month='02'">
          <xsl:value-of select="'Feb'"/>
        </xsl:when>
        <xsl:when test="$month='03'">
          <xsl:value-of select="'Mar'"/>
        </xsl:when>
        <xsl:when test="$month='04'">
          <xsl:value-of select="'Apr'"/>
        </xsl:when>
        <xsl:when test="$month='05'">
          <xsl:value-of select="'May'"/>
        </xsl:when>
        <xsl:when test="$month='06'">
          <xsl:value-of select="'Jun'"/>
        </xsl:when>
        <xsl:when test="$month='07'">
          <xsl:value-of select="'Jul'"/>
        </xsl:when>
        <xsl:when test="$month='08'">
          <xsl:value-of select="'Aug'"/>
        </xsl:when>
        <xsl:when test="$month='09'">
          <xsl:value-of select="'Sep'"/>
        </xsl:when>
        <xsl:when test="$month='10'">
          <xsl:value-of select="'Oct'"/>
        </xsl:when>
        <xsl:when test="$month='11'">
          <xsl:value-of select="'Nov'"/>
        </xsl:when>
        <xsl:when test="$month='12'">
          <xsl:value-of select="'Dec'"/>
        </xsl:when>
        <xsl:otherwise>
          <xsl:value-of select="''" />
        </xsl:otherwise>
      </xsl:choose>
    </xsl:variable>
    <xsl:value-of select="concat($day, '-' ,$monthname, '-', $year , ' ', $time)" />
  </xsl:template>
</xsl:stylesheet>