Sorting the same field twice on same report

[SueSp]

Hello.

System - CE10 Embedded, CR10 Adv Dev. MS SQL

I have a report that shows date/times of processes for a machine grouped by machine then date/time. The details are suppressed and I'm showing my fields in the group 1 footer. Group 1 is Machine Number and Group 2 is Date. In the Group 1 footer I need to show First Activity and Last Activity for that particular machine number. Then I show an elapsed time calculation between the two. Activity is one DB Field.

So, Machines 10, 11, and 12 will have activity througout the given date range. I need to show the first activity and then the last activity for a date range:
Mach# First Last
10 1-9-06 08:01:30 1-9-06 14:03:33
11 1-9-06 07:00:12 1-9-06 23:00:00
12 1-9-06 02:23:20 1-9-06 13:20:00

In this example Machine 10 (or any of them) could have had hundreds of entries between the times shown. I just need to show first and last.

I've been trying to use variables but can't seem to get the right results. I would think a formula of some sort should be able to show the earlies time and latest time for the Group, no?

Thanks for any input!

 

[synapsevampire]

Group by the machine.

To return just the first and last per machine, use Report->Selection Formulas->Group and place:

{table.date} = minimum({table.date},{table.machine})
or
{table.date} = maximum({table.date},{table.machine})

Now create 2 formulas for display:

minimum({table.date},{table.machine})

maximum({table.date},{table.machine})

You didn't state what this elapsed time calculation is suppposed to show, is this days:hours:minutes:second:milliseconds, or???

You did a job job describing it up until you needed to display something.

Here's my FAQ on displaying time differences:

faq767-3543

You'd use the min and max formulas aove in the datediff function.

-k

 

[SueSp]

Wow. I'm gonna have to check out what you've said to do. This may just be the ticket!

The time diff just shows elapsed hours like 1.5 hours for example. If I was smart enough I'd figure out how to show the difference as hh:mm:ss...

Thanks so much!

 

[wsmcjoe]

I'm curious. Are you first and last dates within the same record in a table. If not how did you get crystal to print side by side like this. 1-9-2006 1-9-2006

 

[lbass]

If you insert two summaries (minimum, maximum) on a detail field, you can place them wherever you want in the group footer (or header). That's all SueSP needed to do above.

-LB