Search code examples
sqlfiltercrystal-reportsrangesuppress

Suppress ENTIRE customer if they contain 1 shipment within a range

Hopefully this makes sense. First post here.

I am trying to suppress all instances of a parent while my parameters affect only the child. I run into a problem where I would like to see all customers that had a shipment outside of a date range and ONLY the customers with shipments outside of that range.

With my current formula below, I will return customers with shipments outside of that date range, but it is possible they have a shipment inside it as well.

Ex.

{customer.arcustname} ={?Customer}
AND
{jobshipment.ccdate} <> {?DateRange}

DateRange = 7/1/2014 to 7/31/2014

CUST1's last shipment was on 6/25/2014, this record returns true

CUST2 has a shipment on 7/25/2014, this record returns false

CUST2 also a shipment on 6/25/2014, this record returns true and is displayed on my report. -not wanted

I would like my report to display only CUST1, since CUST2 had a shipment inside of that range, but my report is showing CUST1 and CUST2 because it is only filtering the {jobshipment.ccdate} and not the entire {customer.arcustname}.

Any help would be appreciated. Thanks!

EDIT: In my details all I have is {customer.arcustname}. All I want my report to display is a list of customers (by salesperson prompt) who have not ordered in a designated time period. All answers I've seen are only showing the shipments not in the range, where I just want to see the customers who have not had a shipment in the date range. If they had a shipment, I want the {customer.arcustname} omitted from my report.

HERE is the raw SQL:
SELECT customer."armasterid",customer."arcustname", salesperson."arsalesname",jobshipment."ccdate" FROM ( ( "public".customer customer LEFT OUTER JOIN "public".salesperson salesperson ON customer.arsalesid=salesperson.arsalesid ) LEFT OUTER JOIN "public".job job ON customer.armasterid=job.armasterid )
LEFT OUTER JOIN "public".jobshipment jobshipment ON job.ccmasterid=jobshipment.ccmasterid WHERE ((#NUMBER CONSTANT# = customer."arsalesid")) AND ((NOT ( (customer."araccountstatus" = 'I') ))) ORDER BY customer."arcustname"

Solution 
  • Below solution works assuming you have a customer group and placed dates in detail as you haven't mentioned report structure.

    1. Create a formula @count to count the occurences of dates that are not in range.

      if {jobshipment.ccdate} <> {?DateRange}
then 0
else 1

    2.Now create a summary field (named {@countSummary}) where we take the sum of @count. Place that in the group footer

    3.Write the below supress condition in supress part of group header (Customer group), details and group footer (Customer group).

        EvaluateAfter({@count});


 if ({@countSummary}) <> 0
then true
else false