Comparing one detail line to different detail line

[kdm57]

Trying to compare different detail lines of a report to get time differences. For example, I've selected multiple job ID's for a range of dates. I'm trying to get a transaction ID = 1531 and supress transaction ID 1518, but if transaction ID 1531 does not exist for the job ID, then I want to select a transaction ID of 1518 for that job ID and not supress it. I've tried using a boolean and variables, but can't get it to look through all transaction IDs for each job and to select the 1531 if it exists but if it don't, then to select the 1518 (Have both pulling in on report)?! Also, once I get over that hurdle, I'm need to get a time from one tranaction ID and subtract it from a time for a different transaction ID for each Job ID. Can this be done within crystal?

 

[MColeman]

Maybe with two subreports that pass data back to the main report with shared variables.

One looking for transaction 1531 and one looking for transaction 1518.

 

[lbass]

I think this might be doable without subreports, but you need to tell us what transaction IDs are involved in the time difference you have to calculate. It looks like one of them is either 1531 or 1518, but what can the other transaction ID be? We need all of this information in order to help. Besides 1531 or 1518 is there only one other ID, or can there be multiple IDs? Which ones are involved in the time elapsed calculation?

-LB

 

[Madawc]

You could test for maximum transaction ID for the group. Assuming that you're not using anything higher than 1531, that should work. Suppress a 1518 when the maximum is 1531.

Madawc Williams (East Anglia, UK)

 

[kdm57]

Here's 2 of many of the job ID's that I'm pulling:

GH1 - JOB ID 2403053
GH2 - Trans ID Date StartTime Total
404 3/19/2004 16:23
588 3/22/2004 10:44
1531 4/1/2004 10:14
1518 4/19/2004 16:42 (This one I want to omit-since there is a 1531 on the record and supress it from the GH2)
1011 4/19/2004 9:44

For the above, I would want to find the total time difference between the 404's start time and the 588's start time, the total time difference between the 588 and the 1531 and the total time difference between the 1531 start date and the 1011.

In the other scenerio, here's the data:
GH1 - JOB ID 2403057
GH2 - Trans ID Date StartTime Total
404 3/9/04 16:30
588 3/12/04 11:38
1518 3/31/04 16:36
1011 4/9/04 11:06

This one doesn't have a 1531, so I want to get the time difference of the 404 and the 588, the 588 and the 1518, and the 1518 and the 1011.
These are the only transaction IDs involved in the report. Any help is greatly appreciated.

 

[Madawc]

Tricky. I think you need a running total for each transaction type - use a selection formula and 'maximum date', which will work even if there's only one instance. Or you could do it with date variables, but I find running totals more convenient.

In the group footer, use formula fields to check the running totals and use DateDiff to find the difference between the correct pair of dates. I think you'd need to use isnull to test for cases where there was no 1531 and the 1518 value had to be used.

Madawc Williams (East Anglia, UK)

 

[lbass]

I think MColeman was right about the need for subreports. I would approach it this way. Create a formula in your main report {@1531}:

if {table.transID} = "1531" then 1

Then create a subreport called "has1531" which contains {table.transID} and a formula that concatenates your date and time field into a datetime. In the record selection formula add:

{table.transID} <> "1518"

Also create a datediff formula and place it in the subreport detail section--something like:

if onfirstrecord or {table.grp#2field} <> previous({table.grp#2field}) then 0 else
datediff("s",previous({@datetime}),{@datetime})

Link the subreport to the main report on {table.grp#2field}, and place the subreport in a group footer #2a section.

Create a second subreport "no1531" with no record selection formula, exactly like the first otherwise, and again link it to the main report on {table.grp#2field}. Place this in the group footer #2b section.

Then go to the section expert->group footer #2a->suppress->x+2 and enter:

sum({@1531},{table.Grp#2field}) = 0

Then select group footer #2b->suppress->x+2 and enter:

sum({@1531},{table.Grp#2field}) <> 0

The first subreport will calculate the correct datediffs when there is a 1531 transactionID, while the second will calculate the correct values if there isn't.

Not sure what your group #2 field is. Also didn't know the datatype of your date and time fields, or whether you wanted to calculate seconds, or what, but this should get you started.

-LB