Active TopicsMemberlistSearchHelpRegisterLogin

Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Counting Occurences at set time intervals Post Reply Post New Topic
Author Message
JohnMcC
Newbie
Newbie


Joined: 23 Mar 2011
Location: United Kingdom
Online Status: Offline
Posts: 2
Quote JohnMcC Replybullet Topic: Counting Occurences at set time intervals
     Posted: 23 Mar 2011 at 1:43am
Hi,

I need to create a report that will count the number of users logged into the system at set inerval times.  Eg every 5 minutes.  There is a table that records the login time and the logout time for each session that a user initiates.  The date format is dd/mm/yyyy hh:mm:ss
My aim is to monitor system usage throughout the day.

TIA
John
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 24 Mar 2011 at 3:29am
I was hoping that there was simple function, but if you convert each date / time into a number, you can then group on the result...so say we are going to convert the date to the number of minutes, I would try something like:
 
create a formula like:
 
local datetimevar dt :={table.field};
local numbervar final := year(dt) * 365 * 24 * 60;
local numbervar monthDay := month(dt);
if monthDay = 2 then monthDay := 28;
if monthDay in [1,3,5,7,8,10,12] then monthDay := 31;
if monthDay < 13 then monthDay := 30;
 
final:=final + monthDay * 24 * 60;
final:=final + day(dt) * 24* 60;
final := final + hour(dt) * 60;
final := final + minute(dt);
 
cint(final / 5)
 
I am sure that it can be improved upon, but it is listed for simplicity of understanding.
 
HTH
use this formula in the grouping criteria.
 
for the formula to work
IP IP Logged
JohnMcC
Newbie
Newbie


Joined: 23 Mar 2011
Location: United Kingdom
Online Status: Offline
Posts: 2
Quote JohnMcC Replybullet Posted: 24 Mar 2011 at 4:27am

Hi Lockwelle, thanks for the reply.

I don’t think I have explained what I’m after clearly, sorry.  I’m a newbie and this is my first post.  We are limited to a set number of concurrent users of the system, I would like to report on activity throughout the day to see how close we get to the max users.  The only data recorded by the system about login activity is a record is created each time a user logs in and is updated when they logout.  What I would like is every 5 minutes count how many users are logged in

 

{S_LOGIN_SUCCESS.LOGINTIME} < CDateTime (2011, 03, 22, 09, 05, 00)

and

({S_LOGIN_SUCCESS.LOGOUTTIME} > CDateTime (2011, 03, 22, 09, 05, 00)

or

{S_LOGIN_SUCCESS.LOGOUTTIME} = CDateTime (1753, 01, 01, 00, 00, 00))

 

The above the indicates the user was logged in at 09:05 on the 22nd the 1753 date indicates the logout date has not been populated therefore is currently active.  Would it be possible to check this every 5 minutes throughout the working day 08:00 – 17:00 for all records.  I could limit the data to a single day and run the report daily as required.  Below is a sample from the table

 

SYSACCOUNTNAME       LOGINTIME        LOGOUTTIME

McCoyJ                19/03/2011  15:32:01       19/03/2011  17:31:04

Administrator    19/03/2011  17:36:21       19/03/2011  17:36:27

BoardmM            19/03/2011  17:31:20       19/03/2011  17:44:54

McCoyJ                19/03/2011  17:31:21       19/03/2011  17:44:54

McCoyJ                19/03/2011  17:32:26       19/03/2011  17:57:49

RogersK               19/03/2011  17:46:04       19/03/2011  18:09:40

TaylorB 19/03/2011  17:46:38       19/03/2011  18:09:40

TaylorB 19/03/2011  17:46:46       19/03/2011  18:09:40

McCoyJ                19/03/2011  17:58:08       19/03/2011  18:09:40

McCoyJ                19/03/2011  18:00:04       19/03/2011  18:09:40

BoardmM            19/03/2011  18:00:34       19/03/2011  18:09:40

TaylorB 19/03/2011  17:50:21       19/03/2011  18:10:41

RogersK               19/03/2011  17:46:10       19/03/2011  18:11:33

 

Thanks

John
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4374
Quote lockwelle Replybullet Posted: 25 Mar 2011 at 3:49am
hmmm....
 
ok, I see what you want...a count of the number of users logged in every 5 minutes(or some unit of time).
 
Ok, it took a moment, you can create a Command object with something like this in it:

select DATEADD(MINUTE ,5*n.n,'3/23/11 7:40:00') from Numbers as n where n.N < 121

you would want to make the date more generic...you might be able to use a parameter.  Then you would want to create a formula that gets each persons start and stop times...you probably just need to convert the still logged on value to a future date, and compares them to the Command object values.

 
So hopefully something like:
{command.field} in {table.login} to {table.logout}
 
hopefully this will group the data as desired.  The hard part is coming up with the numbers table.  This can an Excel spreadsheet or table in your database (we have one in our database).  I chose 120 as 10 hours * 5 minutes (or 12 entries an hour)
 
personally, I would do it in a stored proc, as there is more flexibility there, but this should work.
HTH
IP IP Logged
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.