Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Sum for a field based on logic formula Post Reply Post New Topic
Author Message
ncedward
Newbie
Newbie
Avatar

Joined: 18 Feb 2013
Location: United States
Online Status: Offline
Posts: 4
Quote ncedward Replybullet Topic: Sum for a field based on logic formula
    Posted: 21 Feb 2013 at 8:25am

Anyone, everyone, please help!!!!

I've been banging my head against everything in sight trying to figure this one out.  I am trying to calculate the total and average daily log in times for users.  The log in data is stored in an audit event table and the audit event timestamp contains all of the date/time info.  Thus, the login and logout times are captured in the same column.  I used the following formula to calculate the login time.
 
if Previous ({AUDIT_EVENT.EVENT_TYPE_ID})=100 and {AUDIT_EVENT.EVENT_TYPE_ID}=105
Then (DateDiff ('n',PREVIOUS({AUDIT_EVENT.EVENT_TIMESTAMP}),{AUDIT_EVENT.EVENT_TIMESTAMP})/60)
else if Previous ({AUDIT_EVENT.EVENT_TYPE_ID})=100 and {AUDIT_EVENT.EVENT_TYPE_ID}=103
Then (DateDiff ('n',PREVIOUS({AUDIT_EVENT.EVENT_TIMESTAMP}),{AUDIT_EVENT.EVENT_TIMESTAMP})/60)
 
The formula above works and displays the time in hours (e.g. 1.4).
 
I then try to create a running total field for this field and it will not allow me to do so.  I then tried to use the "sum" formula and I get the "this field cannot be summarized" error message.
 
I've also tried exporting to a pivot table, but this too does not allow me to see the data in the format/level I need.  It ends up entering an average for each login session which I don't need (need daily). 
 
I'm thinking that I'm unable to sum this field because of the time when my formula is evaluating.
 
Any advice/help would be greatly appreciated!!!
 
Thanks in advance!!!
 
Nick
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Feb 2013 at 9:02am
look into shared variable formulas
IP IP Logged
ncedward
Newbie
Newbie
Avatar

Joined: 18 Feb 2013
Location: United States
Online Status: Offline
Posts: 4
Quote ncedward Replybullet Posted: 21 Feb 2013 at 10:04am
I'm not sure if that would help.  I think that part of the problem is how the data is displaying.  See below:
 
As I stated, the stop and start dates are on separate rows.  I've tried creating a variable for the login date/time to get it on the same row with no success.  Perhaps, I even tried changing the evaluation times with no luck. 
 
Any suggestions as to how/where to use the shared variables?
 
 
user name user role location 2/12/2013 18:43.45 Wednesday 103.00 Logout 0.07
user name user role location 2/12/2013 18:45.33 Wednesday 100.00 Successfull log in 0.00
user name user role location 2/12/2013 18:54.28 Wednesday 103.00 Logout 0.15
user name user role location 2/12/2013 18:54.55 Wednesday 100.00 Successfull log in 0.00
user name user role location 2/12/2013 19:00.40 Wednesday 103.00 Logout 0.10
user name user role location 2/12/2013 19:01.56 Wednesday 100.00 Successfull log in 0.00
user name user role location 2/12/2013 19:02.17 Wednesday 103.00 Logout 0.02
user name user role location 2/12/2013 19:07.25 Wednesday 100.00 Successfull log in 0.00
user name user role location 2/12/2013 19:11.00 Wednesday 103.00 Logout 0.07
user name user role location 2/12/2013 19:16.30 Wednesday 100.00 Successfull log in 0.00
user name user role location 2/12/2013 19:22.26 Wednesday 103.00 Logout 0.10
user name user role location 2/12/2013 19:24.35 Wednesday 100.00 Successfull log in 0.00
user name user role location 2/12/2013 19:27.49 Wednesday 103.00 Logout 0.05
user name user role location 2/12/2013 19:30.40 Wednesday 100.00 Successfull log in 0.00
user name user role location 2/12/2013 19:37.05 Wednesday 103.00 Logout 0.12
user name user role location 2/12/2013 19:37.36 Wednesday 100.00 Successfull log in 0.00
user name user role location 2/12/2013 19:42.24 Wednesday 103.00 Logout 0.08
user name user role location 2/12/2013 19:47.14 Wednesday 100.00 Successfull log in 0.00
user name user role location 2/12/2013 19:50.56 Wednesday 103.00 Logout 0.05
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Feb 2013 at 10:41am
formual 1 - set it to 0 in the header
shared numbevar x;
x := 0
 
 
sum the minutes per row
Shared Numbervar x;
x := if Previous ({AUDIT_EVENT.EVENT_TYPE_ID})=100 and {AUDIT_EVENT.EVENT_TYPE_ID}=105
Then (DateDiff ('n',PREVIOUS({AUDIT_EVENT.EVENT_TIMESTAMP}),{AUDIT_EVENT.EVENT_TIMESTAMP}))
else if Previous ({AUDIT_EVENT.EVENT_TYPE_ID})=100 and {AUDIT_EVENT.EVENT_TYPE_ID}=103
Then (DateDiff ('n',PREVIOUS({AUDIT_EVENT.EVENT_TIMESTAMP}),{AUDIT_EVENT.EVENT_TIMESTAMP}))
 
display in footer
Shared Numbervar x;
x
IP IP Logged
ncedward
Newbie
Newbie
Avatar

Joined: 18 Feb 2013
Location: United States
Online Status: Offline
Posts: 4
Quote ncedward Replybullet Posted: 21 Feb 2013 at 10:59am
I did this and it displays the last value for x in the group but not the sum.  For example, if a user logs on at 3 times per day, this formula shows the last login session.  I think this is only hard because I must use the "previous()" formula to display my info correctly.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Feb 2013 at 11:18am
sorry forgot part of it...
 
Shared Numbervar x;
x := x + (if Previous ({AUDIT_EVENT.EVENT_TYPE_ID})=100 and {AUDIT_EVENT.EVENT_TYPE_ID}=105
Then (DateDiff ('n',PREVIOUS({AUDIT_EVENT.EVENT_TIMESTAMP}),{AUDIT_EVENT.EVENT_TIMESTAMP}))
else if Previous ({AUDIT_EVENT.EVENT_TYPE_ID})=100 and {AUDIT_EVENT.EVENT_TYPE_ID}=103
Then (DateDiff ('n',PREVIOUS({AUDIT_EVENT.EVENT_TIMESTAMP}),{AUDIT_EVENT.EVENT_TIMESTAMP})))
IP IP Logged
ncedward
Newbie
Newbie
Avatar

Joined: 18 Feb 2013
Location: United States
Online Status: Offline
Posts: 4
Quote ncedward Replybullet Posted: 21 Feb 2013 at 11:26am
Awesome!!!
 
It Worked!!!
 
Progress!!!
 
Your magic is most definitely greater than that which I possess.
 
I'm going to rename my first born DBlank in your honor!
 
Thank you for sticking in there with me!!!
IP IP Logged
Post Reply Post New Topic
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



This page was generated in 0.031 seconds.