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 

Query Explanation

1. The IS_ACTIVE flag is a Boolean flag giving either True or False. If you want to say "Active" or "Inactive," then you need to apply the CASE WHEN statement as shown here to do the conversion. If you don't need this conversion, you can simply say R.IS_ACTIVE here.
2. The TruncateTime is used to get rid of the time component associated with R.LAST_LOGIN_DATE. If you want the time component, you can get rid of the TruncateTime function.
3. To compute the number of days the user has not logged since the last login attempt, the DiffDays function is used to find the difference between current date and the last login date. 

Output

Given below is an example of the output.