total numbe of miles for carrier

[rmcguire80]

I have a table Shipments with many records and for a shipment record can run my report which includes the field SHIPMENTS.TOTALMILES. There is also a field SHIPMENTS.CARRIERID on each record which represents the carrier who moved the shipment.

What Im trying to create is a formula to put on my report which would sum the field SHIPMENTS.TOTALMILES for the SHIPMENTS.CARRIERID for all records with that CARRIERID up to a certain date which is field, SHIPMENTS.ENTRYDATE on the record which Im running my report.

Any help would be appreciated. Is there a conditional summary which would accomplish this? I didn't think just a flat out summary would suffice


thanks

 

[Madawc]

Group by SHIPMENTS.CARRIERID.

Create a formula field that is zero or the miles, depending. Something like

if SHIPMENTS.ENTRYDATE <= {your.date}
then SHIPMENTS.TOTALMILES
else 0

Get a total for @MilesThatCount. (If you're not already familiar with Crystal's automated totals, see FAQ767-6524.)

If you can't group by SHIPMENTS.CARRIERID, then you should be able to use it in a Crosstab.

PS. It helps to give your Crystal version - 8, 8.5, 9, 10, 11, 2008 or whatever. Methods sometimes change between versions, and higher versions have extra options.


Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[rmcguire80]

also I should've mentioned this, the report is ran on an individual record, So I'll open for example shipment 501234 and run the report.

How can I use the formula for entrydate <= youredate Which would be the entry date on th record I'm on

 

[rmcguire80]

Im using Crystal 8.5

 

[Madawc]

If it's just one Shipment, you do just as I specified, but total for the whole report.

Entrydate is one of your fields, as far as I know.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP

 

[rmcguire80]

ok, I can get the sum of miles for all carriers to come up when I run it as its own individual report, but when I put that in as a subreport of my main report it only prints the miles for the shipment record Im running my main report on


Basically I have a report called Rate Confirmation and there is a carrier record tied to that confirmation, but those reports are sent out per record(shipment)

Im trying to add in the total number of miles for all shipments that the carrier assigned to that rate confirmation has been on.

 

[Madawc]

If it is extra data, you'd need to pass shipping details and work from that.

Madawc Williams (East Anglia, UK). Using Crystal 2008 with SQL and Windows XP