Scenario

The SpiraTestEntities.R_Users entity has the user information that can be used to create this custom report.

Creating the Custom Report

1. Go to Admin Panel
2. Edit Reports
3. Click on Add Report 
4.  Name the report ("User Activity Report") so that it can be identified clearly when running the report
5. Update the header and footer as you see fit (Optional)
6. Select the Format for the Report. In this case the, Excel (data) and HTML are a good candidates.
7. Click on the "Add New Custom Section"
8. 
9. Give a name to the section (Required)
10. Add additional details in the header, footer, and description (Optional)
11. Write the following Entity SQL Query 
12. Preview the results
13. Click on "Create Default Template"
14. 
15. Save the Query Section
16. Save the Report
17. Go to Reporting Portal 
18. Run this report

SQL Query

select 
    R.USER_ID, 
    R.USER_NAME, 
    R.FIRST_NAME, 
    R.MIDDLE_INITIAL, 
    R.LAST_NAME,
    CASE 
      when R.IS_ACTIVE = False then "Inactive" 
      else "Active" 
    end as ACTIVE_STATUS, 
    TruncateTime(R.LAST_LOGIN_DATE) as LAST_LOGIN, 
    DiffDays(R.LAST_LOGIN_DATE, CurrentDateTime()) as DAYS_UNUSED
from 
 SpiraTestEntities.R_Users as R 

XSLT Template

<?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:output method="html" indent="yes"/>
  
  <xsl:template match="/RESULTS">
    <table class="DataGrid" style="width:100%; border-collapse: collapse; font-family: sans-serif;">
      <thead>
        <tr style="background-color: #f2f2f2; border-bottom: 2px solid #ddd; text-align: left;">
          <th style="padding: 8px;">User ID</th>
          <th style="padding: 8px;">Username</th>
          <th style="padding: 8px;">Full Name</th>
          <th style="padding: 8px;">Email Address</th>
          <th style="padding: 8px;">Last Login</th>
          <th style="padding: 8px; text-align: center;">Days Unused</th>
        </tr>
      </thead>
      <tbody>
        <xsl:for-each select="ROW">
          <tr style="border-bottom: 1px solid #ddd;">
            <td style="padding: 8px;"><xsl:value-of select="USER_ID"/></td>
            <td style="padding: 8px; font-weight: bold;"><xsl:value-of select="USER_NAME"/></td>
            <td style="padding: 8px;">
              <xsl:value-of select="FIRST_NAME"/>
              <xsl:text> </xsl:text>
              <xsl:value-of select="LAST_NAME"/>
            </td>
            <td style="padding: 8px;"><xsl:value-of select="EMAIL_ADDRESS"/></td>
            <td style="padding: 8px; color: #333;">
              <xsl:choose>
                <xsl:when test="LAST_LOGIN_RAW = '' or not(LAST_LOGIN_RAW)">
                  <span style="color: #999; font-style: italic;">Never Logged In</span>
                </xsl:when>
                <xsl:otherwise>
                  <xsl:value-of select="concat(substring(LAST_LOGIN_RAW, 1, 10), ' ', substring(LAST_LOGIN_RAW, 12, 5))"/>
                </xsl:otherwise>
              </xsl:choose>
            </td>
            <td style="padding: 8px; text-align: center;">
              <xsl:choose>
                <xsl:when test="DAYS_UNUSED = '0'">
                  <span style="background-color: #E6F4EA; color: #137333; padding: 2px 8px; border-radius: 4px; font-weight: bold;">Active Today</span>
                </xsl:when>
                <xsl:otherwise>
                  <xsl:value-of select="DAYS_UNUSED"/>
                </xsl:otherwise>
              </xsl:choose>
            </td>
          </tr>
        </xsl:for-each>
      </tbody>
    </table>
  </xsl:template>
</xsl:stylesheet>

Query Explanation

1. LAST_LOGIN_RAW (Timezone Handling): We select this field directly without any numeric offset modifications (like AddHours(..., -4)). Because Spira is inherently user-context aware, the platform automatically injects the active user's profile timezone settings into this field when the query executes. The raw column already represents the correct local time for whoever is viewing the report screen.

2. DAYS_UNUSED via DiffDays: To compute the number of days a user has not logged in since their last activity, the DiffDays function is used to find the absolute difference between the current date and the last login date. While the display timestamp is localized, this duration math uses CurrentUtcDateTime() to ensure the day count remains perfectly accurate and unaffected by the viewer's regional timezone boundaries.

3. Bypassing TruncateTime for Time Tracking: In standard Spira queries, TruncateTime is frequently used to strip away the hour/minute component from R.LAST_LOGIN_DATE. If you want to retain and display the exact execution time, remove the TruncateTime function entirely as shown in this query, and let the XSLT presentation layer clean up the resulting database string instead.

4. The IS_ACTIVE Flag: The R.IS_ACTIVE property is a native Boolean flag that returns either True or False. In this query's WHERE clause, it filters out inactive users directly. If you ever need to project this field in your SELECT statement as user-friendly text, you can apply a conditional statement: CASE WHEN R.IS_ACTIVE = True THEN 'Active' ELSE 'Inactive' END.

5. XSLT String Manipulation via concat and substring: Spira passes raw database timestamps in standard ISO 8601 formatting (e.g., YYYY-MM-DDTHH:MM:SS). To make this human-readable without complex backend code:

   • substring(LAST_LOGIN_RAW, 1, 10) isolates the date component (YYYY-MM-DD).

   • substring(LAST_LOGIN_RAW, 12, 5) isolates the time component (HH:MM).

   • concat() seamlessly stitches them back together with a single space character in the middle.

Output

Given below is an example of the output.