To Subreport or Just continue with Running Totals 1

[Jcfx]

CR10 on a Sql Database. Three Tables, Order,Leg, and Paydetails. Each order can have multiple legs that can have multiple paydetails. Legs are associated with Units<Tractor>, Paydetails with Driver. Order(numberID) is linked via inner join to Legs(Numberid), Legs linked to paydetails via left outer.

I've grouped by OrderNumberID, then sorted by Legs. I have to come up with the revenue for the Tractors and for the drivers. For the tractors, if the order line haul is 1000 for 500 miles, and the tractor ran all 500 miles, the tractor receives 100% of the Revenue, else they receive a percentage for the miles they ran. <total miles per order comes from adding up the legs of the order> I have a running total for calculating the legs for each tractor in each order <Sum of LGH_miles, evaluate for Lgh_Number, reset on ord_hdrnumber>

A driver can have multiple pay details per leg, per order. He could be paid a flat rate, as well as additional charges.
Ihave a running total set up for Drivers <Sum of Pyd_Amt, evaluate on pyd_number,reset on change of lgh_number>

This is all working so far... but now I need to group on the Tractors and Drivers to show their total Revenue and Pay. This is where I am getting lost. Should what I have so far be a subreport for a main report that does the final summations, or is their a way to continue using running totals? Below is what I have so far.

ord_hdrnu lgh_linelgh_num pyd_numlgh_mi  RTOrderM pyd_amou  PydDriv  lgh_tra  lgh_dri
   719927 $1,012.  179251  323342   562    562.00 $112.40   $254.80  252      OLIVTI


   719928 $1,012.  179252  324047   562    562.00 $223.10   $223.10  208      STALDA

   719928 $1,012.  179252  325881   562    562.00  $50.00   $273.10  208      STALDA


   719929 $1,012.  179253  327259   320    320.00 $112.40   $112.40  252      OLIVTI

   719929 $1,012.  179253  327260   320    320.00  $15.60   $128.00  252      OLIVTI

   719929 $1,012.  180458  324856   422    742.00  $30.00    $30.00  246      HEMGLU

   719929 $1,012.  180458  324857   422    742.00 $108.36   $138.36  246      HEMGLU

   719929 $1,012.  180458  326588   422    742.00  $25.00   $163.36  246      HEMGLU


   719930 $1,012.  179254  325960   281    281.00 $111.55   $111.55  208      STALDA

   719930 $1,012.  179254  325961   281    281.00  $25.00   $136.55  208      STALDA

   719930 $1,012.  180537  325958   440    721.00  $25.00    $25.00  253      HUBBRI

   719930 $1,012.  180537  325959   440    721.00 $111.55   $136.55  253      HUBBRI

Julie Cox
CR 9 and 10
CE 10
Sql Database

 

[Madawc]

If you need to group the same data in two different ways, you have a choice of running totals, a subreport or (in some cases) a crosstab. Running totals can accumulate for specific values for the entire report, but each has to be written individually.

A short-cut if you need to make many similar formulas or running totals is to use cut-and-paste to copy them to a blank report, change the names and copy them back. You then have to remember to change the function of each, easy to forget.

Subreports make a lot of work for computers, but save time for humans. You decide which is more important for your particular circumstances.

Where I work, we go to a lot of trouble to make our mainframe screens and reports run efficiently. We use Crystal mostly for investigations and management information, cases where it doesn't much matter if a job takes twice as long because the user can be doing something else in the meantime. For a big Crystal report that is run frequently, it would be worth going to some trouble to make it efficient. In other cases it makes sense to save programmer-time.

Madawc Williams (East Anglia, UK)