count of records from location A to location B

[rrmcguire]

Hello everyone,

How would I go about writing a formula to do for example a count of records from State A to State B.

ex. show the number of shipments running from Alabama(origin) to Oregon(destination)

thanks for any help provided

origin = SSTATE, destination = CSTATE

 

[Madawc]

You could do a running total, a count with a formula that checks for those two values.

Or you could create a new formula field combining the two and then add a cross-tab for it. E.g.

"From " & {SSTATE} & " to " & {CSTATE}

This would list all of the combinations in a table that could go in the report header or report footer.

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

 

[lbass]

if {table.sstate} = "Alabama" and
{table.cstate} = "Oregon" then 1

Place this in the detail section and insert a sum on it (not a count).

You could also set up parameters {?Origin} and {?Destination} and use these in the formula instead of the state names. Better yet, limit the entire report using a selection formula like this:

{table.sstate} = {?Origin} and
{table.cstate} = {?Destination}

-LB

 

[rrmcguire]

I don't think that the cross tab will work..basically what Im doing is trying to do is I have a large table with the states along the x and y axis and then for the number of field RECNO running from for example CA, as origin and CO as destination show that total, but do this for every origin destination.

This is for a trucking company, where we're doing a listing of the number of shipments running from state to state based on the RECNO total.

 

[lbass]

A crosstab IS the way to go. Add Sstate as the row field, Cstate as the column field, and count of recno as the summary field. Place the crosstab in the report footer and suppress other report sections.

-LB

 

[rrmcguire]

thanks, the cross tab worked perfect