Create report that shows inactive users based on last logon date 1

[Roscoe307]

I have a report that I need to show only the inactive users (where {table.action} = 'I') and their userids. The problem is that some of the users have mulitple entries in the database with mulitple dates and differing {table.action} types.

For example, user BBear has one entry on January 1, 2006 11:59:04 pm where {table.action}='A'. But then Bbear has another entry on August 3, 2006 5:23:32 am where their {table.action} ='I'. Then on August 3, 2006 5:23:45 am BBear has another entry where {table.action}='A'. I need to be able to tell that as of the latest entry in the database the user BBear is considered Active regardless of the fact that BBear was inactive seconds prior.

I hope this makes sense.

I am using an oracle database and CR XI.

Thanks!

 

[lbass]

Insert a group on the userID and then go to report->selection formula->GROUP and enter:

{table.date} = maximum({table.date},{table.user})

This will display the most recent record. If you need to do calculations across users, you might need to use running totals, as any non-group selected records will be included in inserted summaries.

-LB

 

[Roscoe307]

What if a user (userid=BBear) has an entry in the database where on 07/04/2007 at 5:55:55pm they were set to inactive but then at the exact same time on 07/04/2007 at 5:55:55pm they have an entry of active in the database. How do I keep this user from showing up in my report even though they have an entry for inactive at the same exact time as an active entry. (The active entry show override the inactive if the active has a timestamp of a more recent date and time or if the active entry is the exact same date and time as an inactive entry.)

 

[lbass]

Instead of this approach, then create a command like:

select max(table.`date`) as maxdate, table.`user`
from table
group by table.`user`

Link this command by both user and date fields to the main table and in the linking options, choose "enforce both". Then your main report will only return the most recent record. Then go to the report->selection formula->GROUP and enter:

{table.action} = "I" and
distinctcount({table.action},{table.user}) = 1

-LB

 

[Roscoe307]

Thanks for the help lbass!