Grouping and calculations 2

[Jcfx]

Cr10, sql data base, two tables lgh and ord linked by ord_hdrnum.

I need to take the following data and calculate
Total revenue per unit (lgh_tra)
I know somewhat how I need to do it, but translating into crystal has proven difficult.

First I need to come up with the total miles for each order <ord_hdrnum>
I get that by adding up each leg <lgh_num,lgh_mil> for each order.
Then I need to add up the lgh_mil per truck per order, and divide that by the total miles per order to find out the percentage of miles per order the unit ran. Then I multiply that percentage by the order charge to find out the revenue for that unit for that order. And finally sum all the revenue and miles per unit.

Looks easy on paper, but I just cant get a handle on this in Crystal.

Sample data

lgh_tra ord_hdrnum lgh_numb lgh_mil ord_cha
226        721508   181427    108  $389.00
250        721508   182880    378  $389.00
185        721533   181478    448  $1,490.
200        721533   181466    159  $1,490.
200        721534   181467    159  $1,490.
200        721534   182183     34  $1,490.
223        721534   181484    414  $1,490.
185        721535   181485    448  $1,490.
200        721535   181468    159  $1,490.
200        721535   182348     34  $1,490.
185        721536   181488    414  $1,490.
200        721536   181469    125  $1,490.
200        721536   182892     34  $1,490.

Final Output:

lgh_tra	Total Rev	Tot Miles
200	$1,726.14	704
226	$86.44	108
250	$302.56	378
223	$1,016.24	414
185	$3,217.62	1310

Any assistance appreciated.


Julie
CR 9,10 CE10 Sql DB

 

[lbass]

Can't translate truck, unit, leg, etc. to your fields. How about referring to them consistently? It would be easier if you identified the fields by the common name and then explained a little. Are there multiple trucks per order?

-LB

 

[Madawc]

Group by order. Do summary totals or running totals on the various fields. Create formula fields for the group footer that do the calculations you need.

From your description, everything should be doable using using summary totals and formula fields that refer to them. But it's best to show all of the work-field while developing the reports, and try to make it look neat until you are certain everything works.

There are several ways to find totals: running totals, summary totals and variables. Right-click on a field and choose Insert to get a choice of Running Total or Summary. Or else use the Field Explorer, the icon that is a grid-like box, to add running totals.

Running totals allow you to do clever things with grouping and formulas. They also accumulate for each line, hence the name. The disadvantage is that they are working out at the same time as the Crystal report formats the line. You cannot test for their values until after the details have been printed. You can show them in the group footer but not the group header, where they will be zero if you are resetting them for each group.

Summary totals are cruder, but are based directly on the data. This means that they can be shown in the header. They can also be used to sort groups, or to suppress them. Suppress a group if it has less than three members, say. They default to 'Grand Total', but also can be for a group.

Variables are user-defined fields. One useful variant are shared variables to pass data from a subreport back to the main report. You can also use variables to show page totals. For normal counting I find running totals or summary totals much easier.

Madawc Williams (East Anglia, UK)

 

[Jcfx]

Oops, sorry

Truck/Unit = lgh_tra field
Order = ord_hdrnumber
Leg = lgh_num
Miles = lgh_mil
Order Charge = ord_cha

So the first line of data:

Truck    Order     Leg     Miles    OrderCharge
226      721508    181427   108      389.00

There can be multiple trucks per order.

For one order there can be multiple legs. Each leg is linked to a truck, and contains information specific to that truck, mainly miles traveled, and date/time data.
When a truck travels from x to y that is a leg.

The order itself contains only a ordernumber <order> and a start/end date and final charge <OrderCharge>

For the first two lines of data Order 721508 has two legs
<181427,182880> Truck 226 went 108 miles and truck 250 went
378 miles. The two trucks split the revenue of 389 based on their percentage of the total miles of the order.

Hope this helps.







Julie
CR 9,10 CE10 Sql DB

 

[Jcfx]

This is a pared down version of the report I was trying to do here: http://www.tek-tips.com/viewthread.cfm?qid=1117455&page=1

I really appreciated your reply Madawc,and decided to start from scratch.

I've tried grouping by order and using a summary field to get get my miles per order, then using a running total to try to get my miles per order per truck, but then I hit a wall trying to use that running total in a revenue calculation. There is something I am just missing. I've been working on this report for several weeks now, and becoming extremely frustrated. This really should be a snap and I am just not getting it.




Julie
CR 9,10 CE10 Sql DB

 

[lbass]

Are we talking about five trucks, or could there be many? There are a couple of solutions, depending.

-LB

 

[Jcfx]

lbass,

There are over 200 trucks.

Thank you for your help.



Julie
CR 9,10 CE10 Sql DB

 

[Jcfx]

I've grouped by Order, then by Tractor.
Inserted 2 summaries on Miles. One summary is on Order, the other is on Tractor. Then I am using this formula:

(Sum ({legheader.lgh_miles}, {legheader.lgh_tractor})/Sum ({legheader.lgh_miles}, {orderheader.ord_hdrnumber}))*{orderheader.ord_charge}

This gets me the revenue per tractor per order.

I was messing around with adding another group by Tractor, i.e.:

G1 Tractor
G2 Order
G3 Tractor

and trying to summarize my formula field, which of course you cannot do.


Julie
CR 9,10 CE10 Sql DB

 

[lbass]

I'm working on this, but it's a little complex. Probably can't get back to it until tomorrow.

-LB

 

[lbass]

Okay, the issue is that you need to group by order in order to determine the share that belongs to each truck. I would do this part in a subreport where you insert a group on order (Group #1) and then a group on truck (Group#2) and then use the following formula in Group Footer#2:

//{@trksh}:
whileprintingrecords;
shared numbervar array truckshare;
shared stringvar array truck;
shared numbervar i;
shared numbervar j := distinctcount({legheader.lgh_tractor},{orderheader.ord_hdrnumber);
redim preserve truckshare[j];
redim preserve truck[j];

i := i + 1;
if i <= j then
(redim preserve truckshare;
redim preserve truck;
truck := {legheader.lgh_tractor};
truckshare := (Sum ({legheader.lgh_miles}, {legheader.lgh_tractor})/Sum ({legheader.lgh_miles}, {orderheader.ord_hdrnumber}))*{orderheader.ord_charge});

Then create another formula in the subreport:
//{@subfooter} to be placed in the subreport report footer:
whileprintingrecords;
shared numbervar array truckshare;
shared stringvar array truck;
1

Now you can suppress all sections within the subreport.

In the main report, insert a group#1 on {legheader.lgh_tractor} and then a group#2 on {orderheader.ord_hdrnumber}. Then place the subreport in GH#2_b, and link it to the main report on {orderheader.ord_hdrnumber}(and NOT on the truck field). Place the following formula in GH#2_a:

//{@resettrksh}:
whileprintingrecords;
shared numbervar array truckshare := 0;
shared numbervar i := 0;

//{@resetaddtrsh} to be placed in GH#1:
whileprintingrecords;
numbervar addtrksh := 0;

Then add the following formula to GH#2_c:
whileprintingrecords;
shared numbervar array truckshare;
shared stringvar array truck;
shared numbervar i;
shared numbervar j;
numbervar addtrksh;
numbervar array thistruck;

for i := 1 to j do(
if truck = {legheader.lgh_tractor} then
(redim preserve thistruck[j];
thistruck[j] := truckshare));
addtrksh := addtrksh + thistruck[j];
thistruck[j]//this will display the current truckshare while
//accumulating the truckshare for the total in the group footer #1

//{@display} to be placed in GF#1:
whileprintingrecords;
numbervar addtrksh;

To get the total miles, you should be able to place the miles field in the detail section of the main report and insert a summary on it at the truck level.

-LB

 

[Jcfx]

Thank you very Much Lbass,

Trying this out now, I really appreciate your assistance.

Julie
CR 9,10 CE10 Sql DB