Setting Up The Custom Properties in Spira

The first thing we need to do is configure the custom properties in Spira to store the customer billing account that we want to report the time against. In the example below, I am creating a new custom list called Accounts that will contain the list of billing accounts. In our example we call them Account #1 and Account #2.

Once we have created the custom list (and values), we now need to create a Task custom property  called Account. Make it  a single-select list that uses the previously created custom list:

Next, we need to create the exact same  Incident custom property  called Account. Make it  a single-select list that uses the previously created custom list:

Ideally you should use the same Field # for both Tasks and Incidents.

Using the New Custom Properties

Now that we have the custom properties created, you need to make sure that all Tasks and Incidents that will be reported have a value set for the Account field. For example, consider one of the sample tasks in Spira:

Similarly, for incidents:

You can use the Incident and Task workflows to enforce that these fields are populated by users.

Entering Time Against the Tasks/Incidents

Now that the tasks and incidents have been mapped to the Accounts field, the users can now enter time against them:

Once the time has been entered, we can now create the new Custom Report that will be used to report the aggregate time by billing Account:

Creating The Custom Report

Using the tutorial for creating a new custom section based report, create a new custom report with custom section.

For the Query section of the report, include the following:

select GRP.ACCOUNT_NAME, GRP.PROJECT_ID, GRP.PROJECT_NAME, GRP.EFFORT_DATE, sum(GRP.EFFORT_MINUTES) as EFFORT_MINUTES
from
(
	(select CLV.NAME as ACCOUNT_NAME, TE.PROJECT_ID, TE.PROJECT_NAME, TE.EFFORT_DATE, sum(TE.EFFORT_MINUTES) as EFFORT_MINUTES
	from SpiraTestEntities.R_TimesheetEntries as TE
		inner join SpiraTestEntities.R_Tasks as TK on TE.ARTIFACT_ID = TK.TASK_ID
		inner join SpiraTestEntities.R_CustomListValues AS CLV on cast(TK.CUST_01 as Int32) = CLV.CUSTOM_PROPERTY_VALUE_ID and CLV.CUSTOM_PROPERTY_LIST_NAME = 'Accounts'
			and CLV.PROJECT_ID = TK.PROJECT_ID
	where TE.EFFORT_DATE >= AddDays(CurrentDateTime(),-7) and TE.EFFORT_DATE <= AddDays(CurrentDateTime(),7)
		and TE.ARTIFACT_TYPE_NAME = 'Task'
	group by TE.PROJECT_ID, TE.PROJECT_NAME, TE.EFFORT_DATE, CLV.NAME
	)
	union
	(select CLV.NAME as ACCOUNT_NAME, TE.PROJECT_ID, TE.PROJECT_NAME, TE.EFFORT_DATE, sum(TE.EFFORT_MINUTES) as EFFORT_MINUTES
	from SpiraTestEntities.R_TimesheetEntries as TE
		inner join SpiraTestEntities.R_Incidents as INC on TE.ARTIFACT_ID = INC.INCIDENT_ID
		inner join SpiraTestEntities.R_CustomListValues AS CLV on cast(INC.CUST_01 as Int32) = CLV.CUSTOM_PROPERTY_VALUE_ID and CLV.CUSTOM_PROPERTY_LIST_NAME = 'Accounts'
			and CLV.PROJECT_ID = INC.PROJECT_ID
	where TE.EFFORT_DATE >= AddDays(CurrentDateTime(),-7) and TE.EFFORT_DATE <= AddDays(CurrentDateTime(),7)
		and TE.ARTIFACT_TYPE_NAME = 'Incident'
	group by TE.PROJECT_ID, TE.PROJECT_NAME, TE.EFFORT_DATE, CLV.NAME
	)
) as GRP
group by GRP.PROJECT_ID, GRP.PROJECT_NAME, GRP.EFFORT_DATE, GRP.ACCOUNT_NAME
order by GRP.ACCOUNT_NAME asc, GRP.PROJECT_NAME asc, GRP.EFFORT_DATE asc

For the Template section of the report, include the following:

<?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>Account Name</th><th>Project ID</th><th>Project Name</th><th>Date</th><th>Time</th></tr>
      <xsl:for-each select="ROW">
        <tr>
			<td><xsl:value-of select="ACCOUNT_NAME"/></td>
			<td>PR:<xsl:value-of select="PROJECT_ID"/></td>
			<td><xsl:value-of select="PROJECT_NAME"/></td>
			<td>
			  <xsl:call-template name="format-date">
				<xsl:with-param name="datetime" select="EFFORT_DATE" />
			  </xsl:call-template>
			</td>
			<td><xsl:value-of select="format-number(EFFORT_MINUTES div 60, '0')"/>:<xsl:value-of select="format-number(EFFORT_MINUTES mod 60, '00')"/></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>

Notes

  • We have hard-coded the date range of timesheets to be considered to be a 7 days before and after the current date. You can change that range easily in the ESQL.
  • We have assumed CUST_01 is the custom property field being used for the Accounts in both Tasks and Incidents, you can change the name of the field being used.

Running the Report

Once the report has been created, you can execute it from the Spira reports center (either under Products or Programs).

Running the report in HTML format will look like the following:

Running the report in Excel format will look like the following:

Future Considerations

  • In the future, we could modify the layout to make the days columns vs. additional rows
  • We could group the data by Account and then Project vs. showing them repeated