on 04-13-2011 6:41 PM
I have a series of data in a report that looks like the following:
827,489
Start Date/Time 8/18/2010 3:23:57 PM 0.00 0.00 1
Stop Date/Time 8/18/2010 4:35:19 PM 72.00 72.00 2
*Stop Date/Time 8/18/2010 6:03:06 PM 160.00 232.00 3
829,819
Start Date/Time 8/25/2010 4:14:22 PM 0.00 0.00 1
Stop Date/Time 8/25/2010 4:26:22 PM 12.00 12.00 2
827,519
*Start Date/Time 8/18/2010 4:05:53 PM 0.00 0.00 1
830,662
Start Date/Time 8/27/2010 3:52:17 PM 0.00 0.00 1
Stop Date/Time 8/27/2010 5:04:43 PM 72.00 72.00 2
830,721
Start Date/Time 8/27/2010 5:15:03 PM 0.00 0.00 1
Stop Date/Time 8/27/2010 5:59:21 PM 44.00 44.00 2
*Stop Date/Time 8/27/2010 8:31:39 PM 196.00 240.00 3
831,180
Start Date/Time 8/30/2010 3:22:09 PM 0.00 0.00 1
Stop Date/Time 8/30/2010 4:16:48 PM 54.00 54.00 2
You'll see that I essentially have start and stop times that are manually entered into a database through an interface. Unfortunately, users don't always remember to enter their start or stop times. Each record should be a pair.
Now, for the most part I've been able to flag the records using a variety of different formulas, but always with the same results. If the last record in one group (the 6 digit number) is a Start (ie. only one record with no matching stop) and the next groups record begins properly with a 'start', it thinks the record in error is the second one that is starting properly. Not the one with the missing 'stop' record.
So, essentially I need to flag each record (indicated by the * above) that does not have it's mate.
OK, this looks like it works for me. I actully just did this as a test and then instead of colors created a manual running total that set the value to zero if it fell within the value
if {@counttest2}=1
and {@StartORStop} = "Start Date/Time"
then White
else
if {@counttest2}=1
and {@StartORStop} = "Stop Date/Time"
then green
else
if ( {@StartORStop} = "Start Date/Time"
and next({@StartORStop}) <> "Stop Date/Time" )
or
( {@StartORStop} = "Stop Date/Time"
and previous({@StartORStop}) <> "Start Date/Time" )
then green
else white
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Yeah, I messed with that to some effect and this is what I got. It works in most all cases but misses some if there is only a single start in a record (no stop) and the previous record ended with a stop. That's fine to an extent as a start will always equal zero so it's not impacting totals. My code is just sloppy though and it seems to me there should be a better way to write it.
If next({Incident.Incident #})<>previous({Incident.Incident #})
and {@StartORStop} = "Start Date/Time"
Then white
else
if {@StartORStop} = "Start Date/Time" and previous({@StartORStop}) <> "Stop Date/Time"
or
{@StartORStop} = "Stop Date/Time" and previous({@StartORStop}) <> "Start Date/Time"
then
green
else
white
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
Anytime you do something with next or previous, it gets sloppy.
if you put a running total in the detail that resets on the group header you have a counter that you can use in the group
to count records.
put this formula in the group header-do not repeat group header on each page. *
It must print-do not hide it or the header it is in.
You can set the font to white so you won't see it if you like:
//{@RT.record number.zero) <formula name
whileprintingrecords;
numbervar recordnumber := 0
put this formula on the detail line. It too must print-do not hide it.
//{@rt.record number} <formula name
whileprintingrecords;
numbervar recordnumber;
recordnumber:= recordnumber + 1;
now you can test for the first record in each group. Don't forget your parenthesis around the AND statements seperated by the OR.
if {@rt.record number}=1
then
if {@StartORStop} = "Start Date/Time" then white else green
// this assumes the first record should always be a start
else
if ( {@StartORStop} = "Start Date/Time"
and previous({@StartORStop}) not equal "Stop Date/Time" )
or
( {@StartORStop} = "Stop Date/Time"
and previous({@StartORStop}) not equal "Start Date/Time" )
then green else white
there is a way to do this if you must repeat group headers. Put it in group header a and set group header a to underlay the next group header which contains the information you want to print on each page. Then conditionally format group header a to suppress with
inrepeadedgroupheader
and it will only print once and therefore will only reset once per group
This is what I have but for the one record where there is only a start (no stop) it flags the start in the next record:
if {@StartORStop} = "Start Date/Time" and previous({@StartORStop}) <> "Stop Date/Time"
or
{@StartORStop} = "Stop Date/Time" and previous({@StartORStop}) <> "Start Date/Time"
then
green
else
white
Switching to a next vs previous flags the detail record before the one that is incorrect.
Thinking I need to reset at the top of each group.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
84 | |
23 | |
11 | |
9 | |
8 | |
5 | |
5 | |
5 | |
5 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.