Exporting Incident Change History to Excel

Saturday, August 26, 2017 7:43:52 AM

We had the following request from a customer:

I'd like to export the Incident Change History to Excel for reporting purposes.

In particular I'm interested in seeing the Status Changes of all incidents in my project.

How can I implement such a report?

1 Replies
Saturday, August 26, 2017 8:33:02 AM
Avatar
re: inflectra.adam on Saturday, August 26, 2017 7:43:52 AM

The following Entity SQL (ESQL) should be used to generate the report:

select C.CHANGESET_ID as ChangeSetId, C.CHANGE_DATE as ChangeDate, C.ARTIFACT_ID as IncidentId, C.ARTIFACT_DESC as IncidentName,
D.FIELD_CAPTION as FieldName, D.OLD_VALUE as OldValue, D.NEW_VALUE as NewValue
from SpiraTestEntities.R_HistoryChangeSets as C join SpiraTestEntities.R_HistoryDetails as D on C.CHANGESET_ID = D.CHANGESET_ID
where C.PROJECT_ID = ${ProjectId} and C.ARTIFACT_TYPE_ID = 3

The automated XSLT template that Spira will generate will look like:

<?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>ChangeSetId</th><th>ChangeDate</th><th>IncidentId</th><th>IncidentName</th><th>FieldName</th><th>OldValue</th><th>NewValue</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="ChangeSetId"/></td><td><xsl:value-of select="ChangeDate"/></td><td><xsl:value-of select="IncidentId"/></td><td><xsl:value-of select="IncidentName"/></td><td><xsl:value-of select="FieldName"/></td><td><xsl:value-of select="OldValue"/></td><td><xsl:value-of select="NewValue"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

And the final output looks like:

ChangeSetIdChangeDateIncidentIdIncidentNameFieldNameOldValueNewValue
52005-03-04T14:17:006The book listing screen doesn't sortStatusNewOpen
62005-03-04T16:54:007Cannot add a new book to the systemTypeIncidentBug
72005-03-05T10:32:006The book listing screen doesn't sortTypeIncidentBug
82005-03-05T10:35:007Cannot add a new book to the systemStatusNewOpen
132006-05-02T16:34:007Cannot add a new book to the systemStatusOpenAssigned
Statistics
  • Started: Saturday, August 26, 2017 7:43:52 AM
  • Last Reply: Saturday, August 26, 2017 8:33:03 AM
  • Replies: 1
  • Views: 274