suppress group if count < 50

[rmcguire80]

I have a report which displays a list of shipments(records) grouped by SHIPMENTS.SHIPPERSTATE, then a group SHIPMENTS.CONSIGNEESTATE where it shows the list by going out of the SHIPPERSTATE to each of the CONSIGNEESTATE's, then a count of those shipments for each SHIPPERSTATE to CONSIGNNEESTATE

the only shipper states are CA,IL,TX.FL,UT,NV the consignee states are every state

but I want to suppress the group of the consignee state if the count of shipments from the shipper to the consignee is < 50

Any help would be appreciated

thanks

 

[IanWaterman]

In main menu select Report->Selection formula-> Group
enter this formula with appropriate field for shipment Id
distinctcount(shipmentID, {SHIPMENTS.CONSIGNEESTATE} )>=50

This will suppress any consignee sates where count is less than 50

Ian

 

[rmcguire80]

what do you mean by the shipment ID? I think I didn't explain very well.

I have a set of 6 shipper states CA,IL,TX.FL,UT,NV and of the shipments(loads) shipping out of these states I need to get a count of the shipments going to(consignee state) each of the 50 states

ex. CA to AL,AZ,CA,CO, etc.

but I want to suppress the routes which have less than 50 shipments

i.e. CA to AK has 5 shipments, so suppress that group, whereas CA to CA has 260 so show that one


My list is complete and working correctly for my counts I just need to not show the groups which have less that 50 shipments.

thanks

 

[IanWaterman]

I did not know what field you were counting I assumed each shipment had an ID. Just replace that with whatever field you are counting.

Ian

 

[rmcguire80]

each shipment has its own number ex. 500000,500001,500002

 

[IanWaterman]

So I assume you are counting that field

In your example CA to AK has 5 shipments, so suppress that group

This formula distinctcount(shipmentID, {SHIPMENTS.CONSIGNEESTATE} will return 5 for Consignee sate AK, as I said replace ShipmentID with wahtever field contains your shipment numbers 500000,500001,500002 etc.

Ian

 

[rmcguire80]

when I put in that formula as

distinctcount({SHIPMENTS_TTX.RECNO},{SHIPMENTS_TTX.CSTATE})>=50

in my select expert its saying

"This function cannot be used because it must be evaluated later"



The report is laid out as:

GH1(Shipper State)
GH2(Consignee State)
details-shipment information
GF2 is suppressed
GF1 is suppressed

Im trying to suppress Group 2 if the count of those shipments is less than 50

 

[IanWaterman]

You can not use a formula in record selection it must be used in group selection as per my original reponse

In main menu select Report->Selection formula-> Group

Ian

 

[rmcguire80]

that worked Ian, thanks for your help