Custom Report: How to tell if a Test Case has actually changed?

Have o look at this table, it might help you: Available Database Tables - SpiraDocs (inflectra.com)

Thanks Ilya,

The following query will pull the list of test case change entries in date descending:

select value R from SpiraTestEntities.R_HistoryChangeSets as R
where R.PROJECT_ID = ${ProjectId} and R.ARTIFACT_TYPE_ID = 2
order by R.CHANGE_DATE desc

Regards

David

Thanks both of you - that helped me focus my tinkering in the right area.

I settled on this to extract a value i could include in my trace report.

ANYELEMENT(Select value Max(R.Change_Date) from SpiraTestEntities.R_HistoryDetails as R 
                                    where R.Artifact_ID=TSTC.Test_Case_ID and R.New_Value="Approved") as Last_Approval

It seems to get me what I'm looking for in a timely fashion - now off to figure out how to do date comparisons in XSL 1.0...

My advice: try to use JavaScript inside XSLT, it helps my reports a lot. As for me, XSLT is very difficult to find developers and to debug.

Example how to use JS inside XSLT

<?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">
        <script type="text/javascript">
            releases=[];
            <xsl:for-each select="ROW">
                releases.push({
                id:xsltToNumber('<xsl:value-of select='ID'/>'),
                is_summary:xsltToBoolean('<xsl:value-of select='IS_SUMMARY'/>'),
                name:xsltToString('<xsl:value-of disable-output-escaping="yes" select='Name'/>'),
                resource_count:xsltToNumber('<xsl:value-of select='RESOURCE_COUNT'/>'),
                uihd:xsltToNumber('<xsl:value-of select='UIhd'/>'),
                apihd:xsltToNumber('<xsl:value-of select='APIhd'/>'),
                });
            </xsl:for-each>
            <xsl:text disable-output-escaping="yes">
            <![CDATA[
console.log('it works'); // any JS code here that works for example with releases array variable that is defined above
            ]]>
            </xsl:text>
        </script>
    </xsl:template>
</xsl:stylesheet>