Document history report

Those of us who work in safety-critical industries are sometimes asked to generate history report for Documents, with its approvals, comments, audit trail and so on. I know that Spira isn't really a DMS (or, god forbid, a QMS) but if it can do it, then why not?

The following report will generate detailed document history report with versions, authors, comments, change history and so on.

ESQL

SELECT
  DOC.ATTACHMENT_ID,
  DOC.FILENAME AS DOCUMENT_NAME,
  DOC.DESCRIPTION,
  DOC.DOCUMENT_STATUS_NAME AS STATUS,
  DOC.DOCUMENT_TYPE_NAME AS TYPE,
  DOC.AUTHOR_NAME,
  DOC.EDITOR_NAME,
  DOC.CURRENT_VERSION,
  DOC.UPLOAD_DATE,
  DOC.EDITED_DATE,
  DOC.PROJECT_ATTACHMENT_FOLDER_NAME AS FOLDER,
  HIST.CHANGESET_ID,
  HIST.USER_NAME AS CHANGED_BY,
  HIST.CHANGE_DATE,
  DET.FIELD_CAPTION,
  DET.OLD_VALUE,
  DET.NEW_VALUE,
  CMT.CREATOR_NAME AS COMMENT_AUTHOR,
  CMT.CREATION_DATE AS COMMENT_DATE,
  CMT.COMMENT_TEXT
FROM SpiraTestEntities.R_Attachments AS DOC
LEFT JOIN SpiraTestEntities.R_HistoryChangeSets AS HIST
  ON DOC.ATTACHMENT_ID = HIST.ARTIFACT_ID
  AND HIST.ARTIFACT_TYPE_ID = 13
  AND HIST.PROJECT_ID = ${ProjectId}
LEFT JOIN SpiraTestEntities.R_HistoryDetails AS DET
  ON HIST.CHANGESET_ID = DET.CHANGESET_ID
LEFT JOIN SpiraTestEntities.R_Comments AS CMT
  ON DOC.ATTACHMENT_ID = CMT.ARTIFACT_ID
  AND CMT.ARTIFACT_TYPE_ID = 13
WHERE DOC.PROJECT_ID = ${ProjectId}

XSLT
 

<?xml version="1.0" encoding="utf-8"?>
<xsl:stylesheet version="1.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform">

  <xsl:key name="DocGroup" match="*[ATTACHMENT_ID]" use="ATTACHMENT_ID" />
  <xsl:key name="HistGroup" match="*[CHANGESET_ID]" use="concat(ATTACHMENT_ID, '-', CHANGESET_ID)" />
  <xsl:key name="CommentGroup" match="*[COMMENT_TEXT]" use="concat(ATTACHMENT_ID, '-', COMMENT_DATE)" />

  <xsl:template match="/">

    <xsl:for-each select="//*[ATTACHMENT_ID][generate-id() = generate-id(key('DocGroup', ATTACHMENT_ID)[1])]">
      <xsl:sort select="FOLDER" />
      <xsl:sort select="DOCUMENT_NAME" />

      <xsl:variable name="CurrentDocID" select="ATTACHMENT_ID" />

      <!-- ==================== DOCUMENT HEADER ==================== -->
      <h2><xsl:value-of select="DOCUMENT_NAME"/></h2>

      <table class="DataGrid" style="width:50%">
        <tr>
          <th style="width:30%">Field</th>
          <th>Value</th>
        </tr>
        <tr>
          <td><strong>Version</strong></td>
          <td><xsl:value-of select="CURRENT_VERSION"/></td>
        </tr>
        <tr>
          <td><strong>Status</strong></td>
          <td><xsl:value-of select="STATUS"/></td>
        </tr>
        <tr>
          <td><strong>Author</strong></td>
          <td><xsl:value-of select="AUTHOR_NAME"/></td>
        </tr>
        <tr>
          <td><strong>Last Editor</strong></td>
          <td><xsl:value-of select="EDITOR_NAME"/></td>
        </tr>
        <tr>
          <td><strong>Creation Date</strong></td>
          <td><xsl:value-of select="UPLOAD_DATE"/></td>
        </tr>
        <tr>
          <td><strong>Last Modified</strong></td>
          <td><xsl:value-of select="EDITED_DATE"/></td>
        </tr>
        <tr>
          <td><strong>Folder</strong></td>
          <td><xsl:value-of select="FOLDER"/></td>
        </tr>
        <tr>
          <td><strong>Type</strong></td>
          <td><xsl:value-of select="TYPE"/></td>
        </tr>
        <tr>
          <td><strong>Description</strong></td>
          <td><xsl:value-of select="DESCRIPTION"/></td>
        </tr>
      </table>

      <!-- ==================== CHANGE HISTORY ==================== -->
      <h3>Change History</h3>

      <xsl:variable name="HasHistory" select="count(key('DocGroup', $CurrentDocID)[CHANGESET_ID != ''])" />

      <xsl:choose>
        <xsl:when test="$HasHistory = 0">
          <p><em>No change history recorded.</em></p>
        </xsl:when>
        <xsl:otherwise>
          <table class="DataGrid" style="width:100%">
            <tr>
              <th>Date</th>
              <th>Changed By</th>
              <th>Field</th>
              <th>Old Value</th>
              <th>New Value</th>
            </tr>
            <xsl:for-each select="key('DocGroup', $CurrentDocID)[CHANGESET_ID != ''][generate-id() = generate-id(key('HistGroup', concat(ATTACHMENT_ID, '-', CHANGESET_ID))[1])]">
              <xsl:sort select="CHANGE_DATE" />
              <tr>
                <td><xsl:value-of select="CHANGE_DATE"/></td>
                <td><xsl:value-of select="CHANGED_BY"/></td>
                <td><xsl:value-of select="FIELD_CAPTION"/></td>
                <td><xsl:value-of select="OLD_VALUE"/></td>
                <td><xsl:value-of select="NEW_VALUE"/></td>
              </tr>
            </xsl:for-each>
          </table>
        </xsl:otherwise>
      </xsl:choose>

      <!-- ==================== COMMENTS ==================== -->
      <h3>Comments</h3>

      <xsl:variable name="HasComments" select="count(key('DocGroup', $CurrentDocID)[COMMENT_TEXT != ''])" />

      <xsl:choose>
        <xsl:when test="$HasComments = 0">
          <p><em>No comments recorded.</em></p>
        </xsl:when>
        <xsl:otherwise>
          <table class="DataGrid" style="width:100%">
            <tr>
              <th>Date</th>
              <th>Author</th>
              <th>Comment</th>
            </tr>
            <xsl:for-each select="key('DocGroup', $CurrentDocID)[COMMENT_TEXT != ''][generate-id() = generate-id(key('CommentGroup', concat(ATTACHMENT_ID, '-', COMMENT_DATE))[1])]">
              <xsl:sort select="COMMENT_DATE" />
              <tr>
                <td><xsl:value-of select="COMMENT_DATE"/></td>
                <td><xsl:value-of select="COMMENT_AUTHOR"/></td>
                <td><xsl:value-of select="COMMENT_TEXT" disable-output-escaping="yes"/></td>
              </tr>
            </xsl:for-each>
          </table>
        </xsl:otherwise>
      </xsl:choose>

      <!-- Divider between documents -->
      <hr/>

    </xsl:for-each>

  </xsl:template>

</xsl:stylesheet>

Let me know if you've found any issues with it!