| Author |
Message |
FrnhtGLI
Senior Member
Joined: 22 May 2009
Online Status: Offline
Posts: 347
|
  
Topic: Data Sorting Awkwardly
Posted: 14 Apr 2010 at 4:28am |
Report grouped by Agent Group>Name>Date(each day)>Time(each second). If I prompt for a large group, one of the agent's data gets flopped around. If I just prompt for that one person, it works like it should.
Anyone have any idea why this might be happening?
|
|
|< /\ '][' ( )
|
 IP Logged |
|
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 14 Apr 2010 at 4:38am |
Can you clarify this a little more please?
What do you mean by flipped around and which part is being flipped?
|
| IP Logged |
|
FrnhtGLI
Senior Member
Joined: 22 May 2009
Online Status: Offline
Posts: 347
|
Posted: 14 Apr 2010 at 5:37am |
Detail lines are coming through that show time punches for various events a person does within a day. The first punch is the punch in and the next punch would be the punch out for the first event and the punch in for the next event. Depending upon a code entered upon a punch, the person goes into a state. I am calculating the time a person spends for each state. So on each detail line is the persons name, punch in, punch out, reason code, event type(state) and time in seconds.
Due to the nature of the data, I had to create separate manual running totals to calculate the seconds so I could then turn it into minutes. The seconds for each state/event are calculated in a running total depending on their reason code and event type.
It seems that for one particular person when I run it with a large group some of the reason codes/event types are swapping, skewing the data. There is no trickery involved when the reason codes display, I'm just displaying the database field. So I'm confused as to how they are swapping positions and why this is happening.
|
|
|< /\ '][' ( )
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 14 Apr 2010 at 6:03am |
Hmmmm...
Hard to tell without seeing row level data but here are some things to consider
When you group data, it automatically sorts the data by that group (alpha or numeric) which alters your original data set rows.
From your desctription it sounds like a new row is created for each punch so you need to compare previous or next rows to get a total time. Is that accurate?
Edited by DBlank - 14 Apr 2010 at 6:03am
|
| IP Logged |
|
FrnhtGLI
Senior Member
Joined: 22 May 2009
Online Status: Offline
Posts: 347
|
Posted: 14 Apr 2010 at 7:56am |
Correct. There is only one time field {table.datetime}. The punch in time for an event will be {table.datetime} and the punch out time will be next({table.datetime}). I'm not having a problem with the running totals themselves, just the way the detail line is acting according to a given circumstance. Like I said, if I set the parameter to include only that agent, the lines display correctly. If I bring in the whole lot of agents, the times for that agent display correctly, but the event types and reasons swap in certain places, skewing data the running totals collect.
I have some pictures to show but I can't load them.
|
|
|< /\ '][' ( )
|
| IP Logged |
|
FrnhtGLI
Senior Member
Joined: 22 May 2009
Online Status: Offline
Posts: 347
|
Posted: 14 Apr 2010 at 8:01am |
This is how it should look and does when it is the individual:
1, 2, 3, 4, 5, 6 relate to the event, 3 being ready, 4 being reserve.
This is how it looks when it is the group:
Notice the 3 and 4 are switched which adds time to the "Ready" running total (total to the far right).
|
|
|< /\ '][' ( )
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 14 Apr 2010 at 8:44am |
I'll have to look at this later tonight at home as security settings block the image.
That being said, when you refer to a Running Total are you using a Crystal Running Total or are you using a formula that you are calling a RT? Either way can you give an example of the design of one (including conditional statements) that is displaying incorrectly ('flopping').
Thanks Edited by DBlank - 14 Apr 2010 at 8:45am
|
| IP Logged |
|
FrnhtGLI
Senior Member
Joined: 22 May 2009
Online Status: Offline
Posts: 347
|
Posted: 14 Apr 2010 at 10:55am |
I'm using manual running totals that lockwelle likes to use, the three field ones. Those are not my problem though, but I can give you an example:
global numbervar nIBackupServices:= nIBackupServices + (if {ReasonCodes}='Backup Services'
and {EventCodes}<>'Ready'
then {TotalSeconds}
else 0);
Where {ReasonCodes} and {EventCodes} help to determine if the seconds should get calculated. As long as {ReasonCodes} is 'Backup Services' and {EventCodes} is not 'Ready' I need it to add the seconds to the running total for the particular total stated above.
{ReasonCodes} takes numbers that come from the database for a particular punch and turns it into a recognizable text, so:
if {table.reasoncodes}=2
then 'TSR Sub'
else
if {table.reasoncodes}=11
then 'Backup Services'
else...
{EventCodes} also takes numbers that come from the database for a particular punch and turns it to recognizable text.
There is always a Reason and Event associated with each punch.
|
|
|< /\ '][' ( )
|
| IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 14 Apr 2010 at 3:02pm |
I assume that the images you loaded are some sort of header or footer that is combing 2 rows of data. Is that correct?
My guess is that for this person the data is the same for the seconds and it is inverting on the 2 rows. Why in one instance and not another i do not know.
If you have another way to make sure you have the 2 mathcing rows that go togther I would insert a group on that before the seconds group.
|
| IP Logged |
|
FrnhtGLI
Senior Member
Joined: 22 May 2009
Online Status: Offline
Posts: 347
|
Posted: 15 Apr 2010 at 2:31am |
Incorrect. They are detail lines. The odd thing is if I put the fields in the Time(for each second) group header, my running total for that particular event/reason comes out correct. The downside is that other running totals get dramatically skewed.
I believe you are right about the seconds. I thought about that last night.
On another note, while I was writing this response I played around with grouping and grouped on Event after time in original order and that skewed some numbers, but when I removed the group my numbers look like they match pretty well. So not sure what's the deal with that. I guess I'm going to test some more and see if it's going to cooperate now. Hopefully it does.
Thanks for your responses.
|
|
|< /\ '][' ( )
|
| IP Logged |
|
|
|
