Sample Project Baseline Custom Reports

1. Systemwide Baseline Report

This report provides a list of all baselines across the entire system with info about baseline name, product, release, and date:

select 'BH' + cast(B.BASELINE_ID as string) as BASELINE_ID, 'PR' + cast(B.PROJECT_ID as string) as PROJECT_ID, 'RL' + cast(B.RELEASE_ID as string) as RELEASE_ID, B.NAME, B.DESCRIPTION, B.CREATION_DATE, B.IS_APPROVED
from SpiraTestEntities.R_Baselines as B
where B.IS_DELETED = False
and B.IS_ACTIVE = True

If you use the option to Create the Default Template you will get:

<?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>BASELINE_ID</th><th>PROJECT_ID</th><th>RELEASE_ID</th><th>NAME</th><th>DESCRIPTION</th><th>CREATION_DATE</th><th>IS_APPROVED</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="BASELINE_ID"/></td><td><xsl:value-of select="PROJECT_ID"/></td><td><xsl:value-of select="RELEASE_ID"/></td><td><xsl:value-of select="NAME"/></td><td><xsl:value-of select="DESCRIPTION"/></td><td><xsl:value-of select="CREATION_DATE"/></td><td><xsl:value-of select="IS_APPROVED"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

The sample output will look like:

2. Project Baseline Report

This report provides a list of all baselines in a specific product, with info about the baseline name, description, release, and date:

select 'BH' + cast(B.BASELINE_ID as string) as BASELINE_ID, 'RL' + cast(B.RELEASE_ID as string) as RELEASE_ID, B.NAME, B.DESCRIPTION, B.CREATION_DATE, B.IS_APPROVED
from SpiraTestEntities.R_Baselines as B
where B.PROJECT_ID = ${ProjectId}
and B.IS_DELETED = False
and B.IS_ACTIVE = True

If you use the option to Create the Default Template you will get:

<?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>BASELINE_ID</th><th>RELEASE_ID</th><th>NAME</th><th>DESCRIPTION</th><th>CREATION_DATE</th><th>IS_APPROVED</th></tr>
      <xsl:for-each select="ROW">
        <tr><td><xsl:value-of select="BASELINE_ID"/></td><td><xsl:value-of select="RELEASE_ID"/></td><td><xsl:value-of select="NAME"/></td><td><xsl:value-of select="DESCRIPTION"/></td><td><xsl:value-of select="CREATION_DATE"/></td><td><xsl:value-of select="IS_APPROVED"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

The sample output will look like:

3. Baseline Change Report

This is a more detailed report for a project that shows the differences (called DIFFS) across all artifacts between each baseline and the previous baselines:

select HC.CHANGESET_ID, HC.ARTIFACT_TYPE_NAME, HC.ARTIFACT_ID, HC.ARTIFACT_DESC, HC.CHANGE_DATE,
HC.CHANGETYPE_NAME, HC.USER_NAME, B.BASELINE_ID, B.NAME as BASELINE_NAME
from SpiraTestEntities.R_HistoryChangeSets as HC
left join SpiraTestEntities.R_Baselines as B on HC.CHANGESET_ID = B.CHANGESET_ID
where HC.PROJECT_ID = ${ProjectId}
and HC.CHANGESET_ID >= anyelement(select value B1.CHANGESET_ID from SpiraTestEntities.R_Baselines as B1 where B1.BASELINE_ID = 1)
and HC.CHANGESET_ID <= anyelement(select value B2.CHANGESET_ID from SpiraTestEntities.R_Baselines as B2 where B2.BASELINE_ID = 2)
order by CHANGESET_ID desc

where the query has two hard-coded values:

• B1.BASELINE_ID - the ID of the baseline you want to compare differences FROM
• B2.BASELINE_ID - the ID of the baseline you want to compare differences TO

If you use the option to Create the Default Template you will get:

<?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>CHANGE_DATE</th><th>CHANGETYPE_NAME</th><th>USER_NAME</th><th>BASELINE_ID</th><th>BASELINE_NAME</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="CHANGE_DATE"/></td><td><xsl:value-of select="CHANGETYPE_NAME"/></td><td><xsl:value-of select="USER_NAME"/></td><td><xsl:value-of select="BASELINE_ID"/></td><td><xsl:value-of select="BASELINE_NAME"/></td>
        </tr>
      </xsl:for-each>
        </table>
    </xsl:template>
</xsl:stylesheet>

The sample output will look like: