Dynamic Grouping based on a formula- stumped

[Jcfx]

CR 11 on a sql database.

This report is a logistics report that is supposed to replace a spreadsheet. The idea behind the report is to track a load from the point it is dispatched until the load is dropped off in the customers drop lot. The unit moves through three 'groupings' the first being the original dispatch to pick up the load, the second where the load is dropped in our drop lot and moved to a different trailer. The third is where the unit is in the customers lot waiting for the customer to unload. The first two groupings are only to display the SLI# and our Order number. The third grouping is supposed to show an aging <when was the load originally picked up> and what trailer is it on. When the customer unloads the trailer, the trailer is supposed to drop off the report.

Below is what I have come up with so far, but I am wondering if I've made this too complicated. I want to group by my finalGrouping formula but am unable too. I've included sample data and my current layout as well as my formulaes so far.


PH SLI# Our# Trailer# Pick Up Date
GH1 RF28357 780054
<Field> LEGID SEQ CITY Trailer PickupDate Carrier Event Status

D 905537 1 SHREVEPORT,LA/ 3798 1/0/1900 12:00:00 AM UNKNOWN BMT DNE
D 905290 2 SHREVEPORT,LA/ 3798 1/18/2007 7:38:00 AM UNKNOWN LLD DNE
D 905538 3 ROMULUS,MI/ 3798 1/0/1900 12:00:00 AM UNKNOWN DLT DNE
D 905539 4 ROMULUS,MI/ 564 1/0/1900 12:00:00 AM les HCT DNE
D 909416 5 SHARON,ON/ 564 1/0/1900 12:00:00 AM les DLT DNE
D 909417 6 SHARON,ON/ 564 1/0/1900 12:00:00 AM les HLT OPN
D 905291 7 MISSISSAUGA,ON/ 564 1/0/1900 12:00:00 AM les LUL OPN
GF1
GH1 RF32304 780057
D 905572 1 HARTSVILLE,SC/ 0087 1/0/1900 12:00:00 AM UNKNOWN BMT DNE
D 905296 2 CROSSVILLE,TN/ 0087 1/18/2007 9:25:00 AM UNKNOWN LLD DNE
D 905574 3 ROMULUS,MI/ 0087 1/0/1900 12:00:00 AM UNKNOWN DLT DNE
D 905575 4 ROMULUS,MI/ 652 1/0/1900 12:00:00 AM les HCT DNE
D 909962 5 SHARON,ON/ 652 1/0/1900 12:00:00 AM les DLT DNE
D 909963 6 SHARON,ON/ 652 1/0/1900 12:00:00 AM les HLT OPN
D 905297 7 MISSISSAUGA,ON/ 652 1/0/1900 12:00:00 AM les LUL OPN
GF1
GH1 RF33028 780064
D 906815 1 INDIANAPOLIS,IN/0013 1/0/1900 12:00:00 AM UNKNOWN BMT DNE
D 905348 2 RUSHVILLE,IN/ 0013 1/19/2007 10:19:00 AM UNKNOWN LLD DNE
D 906817 3 ROMULUS,MI/ 0013 1/0/1900 12:00:00 AM UNKNOWN DLT DNE
D 906818 4 ROMULUS,MI/ UNKNOWN 1/0/1900 12:00:00 AM les HCT OPN
D 905349 5 MISSISSAUGA,ON/ UNKNOWN 1/0/1900 12:00:00 AM les LUL OPN
GF1

GH1 RH09135 781384
D 912243 1 LONGMONT,CO/ 1/0/1900 12:00:00 AM UNKNOWN LLD OPN
D 912244 2 MISSISSAUGA,ON/ 1/0/1900 12:00:00 AM UNKNOWN LUL OPN
GF1

GH1 RH09136 781386
D 912645 1 Tulsa,OK/ 0078 1/0/1900 12:00:00 AM UNKNOWN BMT DNE
D 912245 2 LONGMONT,CO/ 0078 1/0/1900 12:00:00 AM UNKNOWN LLD OPN
D 912649 3 ROMULUS,MI/ 0078 1/0/1900 12:00:00 AM UNKNOWN DLT OPN
D 912650 4 ROMULUS,MI/ UNKNOWN 1/0/1900 12:00:00 AM UNKNOWN HCT OPN
D 912246 5 MISSISSAUGA,ON/ UNKNOWN 1/0/1900 12:00:00 AM UNKNOWN LUL OPN
GF1

//@Categorize - placed in the detail
if {stops.stp_event} = "LLD" and {stops.stp_status} = "OPN" then "On Way"
Else
if {stops.stp_event} = "LLD" and {stops.stp_status} = "DNE" then "Romulus"
Else 
if {stops.stp_event} = "HCT" and {stops.stp_status} = "DNE" then "Woodstock"

//@Sort - placed in the detail
if {@Categorize} = "Romulus" then 2
else
if
{@Categorize} = "Woodstock" then 3
else
if 
{@Categorize} = "On Way" then 1
else
0

//@FinalGrouping - placed in GH1
if Sum ({@Sort}, {orderheader.ord_number}) = 5 then "3. Woodstock Yard"
else
if Sum ({@Sort}, {orderheader.ord_number}) = 2 then "2. Romulus Yard"
else
if Sum ({@Sort}, {orderheader.ord_number}) = 1 then "1. Will be Coming to Romulus"
else
"Issues"

//@PickupDate
if {stops.stp_event} = "LLD" and {stops.stp_status} = "DNE" then {stops.stp_arrivaldate}

//@TrailerID
if ({stops.stp_event} = "HCT" or {stops.stp_event} = "HLT") then {stops.trl_id} else " "

I need to insert a group on Final Grouping
Using the data above Order 780054 and 780057 would appear under 3. Woodstock Yard, Order 780064 would appear under 2. Romulus yard and
781384,781386 would be 1. will be coming to romulus. I know how to grab my pickup date and trailer information. It's this final group that has me a bit stumped. If I try to insert a group on @Sort, it just pulls out the associated detail, not the whole order.

My final Result needs to look like this: <Note that the trailer id and shipping date only appear in one group>

GH2 3. WoodStock Yard
GH1 RF28357     780054  564 1/18/2007
GH1 RF32304     780057  652 1/18/2007

    2. Romulus Yard
    RF33028     780064

    1. Will be coming to Romulus
    RH09135     781384
    RH09136     781386

I hope this information helps, any assistance in finishing this report off, or even a different approach with the data that may be more efficient would be apprecieated.


Julie
CRXI CE10 / RS2005 Sql DB

 

[synapsevampire]

I feel badly for mentioning this, as you've obviously done a lotof work on trying to convey requirements, but it's quite confusing, and you misuse terms.

Example data does not mean an example of how you currently output data.

It means what does the data look like at the detail (row) level. I'm guessing that you are showing this, although already grouped for some reason...

Then you show output where a Group Header 2 is displayed PRIOR to a group header 1. That isn't how grouping works.

Perhaps that helps to show why you aren't getting many responses, and I agree, you're probably over complicating this, I just don't know what this is.

Again, example data is not defined as being grouped, that's a presentation method used by Crystal.

So show data, just the field namesand the data, and then show the output, based on ALL of the data in the example. You don'tneed so much data either, keep it succinct.

-k

 

[Jcfx]

Thanks SV I'll try using one load as an sample data and walk through each proccess.


Each detail line is a movement <leg>. When a order is first dispatched it contains a minimum of two detail lines, three if the driver started off empty <BMT>

So for this particular report when the order looks like this:

LEGID SEQ CITY Trailer shippingDate Carrier Event Status SL# Order Number
905537 1 SHREVEPORT,LA/ 53798 1/18/2007 12:00:00 AM UNKNOWN BMT DNE RF28357 780054
905290 2 SHREVEPORT,LA/ 53798 1/18/2007 07:38:00 AM UNKNOWN LLD OPN RF28357 780054
905291 3 MISSISSAUGA,ON/ Unknown les LUL OPN RF28357 780054

I need it to show up under group 1. Will be coming to Romulus. the event of LLD and the Status of OPN triggers that.


When the order looks like this I need it to fall under 2. Romulus, the event of DLT and Status of DNE triggers that.

LEGID SEQ CITY Trailer shippingDate Carrier Event Status SL# Order Number
905537 1 SHREVEPORT,LA/ 3798 1/18/2007 12:00:00 AM UNKNOWN BMT DNE RF28357 780054
905290 2 SHREVEPORT,LA/ 3798 1/18/2007 07:38:00 AM UNKNOWN LLD DNE RF28357 780054
905538 3 ROMULUS,MI/ 3798 1/18/2007 12:00:00 pM UNKNOWN DLT DNE RF28357 780054
905539 4 ROMULUS,MI/ 564 1/18/2007 12:05:00 pM les HCT OPN RF28357 780054
905291 5 MISSISSAUGA,ON/ 564 les LUL OPN RF28357 780054

At this stage of dispatch it needs to show on the report under 3. Woodstock. This is triggered by the event of HCT and status of DNE

LEGID SEQ CITY Trailer shippingDate Carrier Event Status SL# Order Number
905537 1 SHREVEPORT,LA/ 3798 1/18/2007 12:00:00 AM UNKNOWN BMT DNE RF28357 780054
905290 2 SHREVEPORT,LA/ 3798 1/18/2007 07:38:00 AM UNKNOWN LLD DNE RF28357 780054
905538 3 ROMULUS,MI/ 3798 1/18/2007 12:00:00 pM UNKNOWN DLT DNE RF28357 780054
905539 4 ROMULUS,MI/ 564 1/18/2007 12:05:00 pM les HCT DNE RF28357 780054
909416 5 SHARON,ON/ 564 1/18/2007 15:00:00 pM les DLT DNE RF28357 780054
909417 6 SHARON,ON/ 564 1/18/2007 15:05:00 pM les HLT OPN RF28357 780054
905291 7 MISSISSAUGA,ON/ 564 1/18/2007 23:00:00 pM les LUL OPN RF28357 780054

The order naturally falls off the report when the final event LUL has a status of DNE

Lets pretend that there is only one load today and disregard the fact that the driver seems to be moving sub sonic )

When the report is run at 8 am it should look like this:

3.Woodstock
SL# ORDER# Trailer PickupDate

2.Romulus
SL# ORDER# Trailer PickupDate

3.Will Be Coming to Romulus
SL# ORDER# Trailer PickupDate
RF28357 780054


When the report is run at 1pm:

3.Woodstock
SL# ORDER# Trailer PickupDate

2.Romulus
SL# ORDER# Trailer PickupDate
RF28357 780054

3.Will Be Coming to Romulus
SL# ORDER# Trailer PickupDate


When the report is run at 4 pm:

3.Woodstock
SL# ORDER# Trailer PickupDate
RF28357 780054 564 01/18/2007

2.Romulus
SL# ORDER# Trailer PickupDate

3.Will Be Coming to Romulus
SL# ORDER# Trailer PickupDate


The data I supplied was several loads in various stages and the amount of detail for each load I have to work with

Thanks



Julie
CRXI CE10 / RS2005 Sql DB

 

[Jcfx]

Anyone? I am not sure how else I can explain this report.

I almost have this working. If I could group on my formula @finalGrouping I would havve it. I've moved the formula from the group header into the detail area but it is not available in my insert group dialog. I am assuming that is because of when @Sort is evaluated?

Thank you

Julie
CRXI CE10 / RS2005 Sql DB