Sage 100

 View Only
Back to discussions
Expand all | Collapse all

Crystal Reports Suppression Formula

  • 1.  Crystal Reports Suppression Formula

    Posted 09-26-2020 09:59
    I have suppression formula that works but I am wonder why it isn't represented in the SQL query: Crystal Reports, Database, Show SQL Query.

    There is a text box that gets suppressed if the country code is not either US or USA.  {SO_InvoiceWrk.BillToCountryCode} <> "USA"
    AND {SO_InvoiceWrk.BillToCountryCode} <> "US".

    This question arose when I noticed the following formula didn't work so I wanted to see how Crustal interpreted it when it was converted to SQL. {SO_InvoiceWrk.BillToCountryCode} <> "USA" OR {SO_InvoiceWrk.BillToCountryCode} <> "US".

    I believe both the AND and the OR operators should work.  Using OR, if either of the conditions is met, the value of the query is true.  Using AND, both of the conditions must be met, and the value of the query is true.  In this example, the query should return TRUE for AND and OR.  Why would there be different results?

    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Assistant to the Traveling Secretary
    ------------------------------


  • 2.  RE: Crystal Reports Suppression Formula

    Posted 09-26-2020 12:25
    The SQL query is what data Crystal gets from the data source.  Suppression works on the display after all the data is gathered.
    {SO_InvoiceWrk.BillToCountryCode} <> "USA" OR {SO_InvoiceWrk.BillToCountryCode} <> "US":  if it is US, then it is not USA, so it doesn't get suppressed, and vice versa.
    It might be cleared to use:
    Not {SO_InvoiceWrk.BillToCountryCode} IN ["US","USA"])

    ------------------------------
    Phil McIntosh
    President
    Friendly Systems, Inc.
    Asheville NC
    678.273.4010 ext 5
    ------------------------------



  • 3.  RE: Crystal Reports Suppression Formula

    Posted 09-26-2020 21:02
    Phil - Using the example you provided, if the country code is AUS (Australia) then the text box should be suppressed, but it isn't.  That's what I don't understand.

    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Assistant to the Traveling Secretary
    ------------------------------

    Original Message


  • 4.  RE: Crystal Reports Suppression Formula

    Posted 09-27-2020 09:04
    After I fixed the missing open parenthesis it worked for me...

    ------------------------------
    Phil McIntosh
    President
    Friendly Systems, Inc.
    Asheville NC
    678.273.4010 ext 5
    ------------------------------

    Original Message


  • 5.  RE: Crystal Reports Suppression Formula

    Posted 09-27-2020 10:02
    Not ({SO_InvoiceWrk.BillToCountryCode} IN ["US","USA"]) may work, I didn't test it.  My point is, if {SO_InvoiceWrk.BillToCountryCode} <> "USA" OR {SO_InvoiceWrk.BillToCountryCode} <> "US" is equivalent to Not ({SO_InvoiceWrk.BillToCountryCode} IN ["US","USA"]), then why doesn't {SO_InvoiceWrk.BillToCountryCode} <> "USA" OR {SO_InvoiceWrk.BillToCountryCode} <> "US"  work?

    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Assistant to the Traveling Secretary
    ------------------------------

    Original Message


  • 6.  RE: Crystal Reports Suppression Formula

    Posted 09-27-2020 16:35
    It is not equivalent.  It is equivalent to Not {SO_InvoiceWrk.BillToCountryCode} <> "USA" AND {SO_InvoiceWrk.BillToCountryCode} <> "US")

    ------------------------------
    Phil McIntosh
    President
    Friendly Systems, Inc.
    Asheville NC
    678.273.4010 ext 5
    ------------------------------

    Original Message


  • 7.  RE: Crystal Reports Suppression Formula

    Posted 09-27-2020 16:55
    OK. Not {SO_InvoiceWrk.BillToCountryCode} <> "USA" AND {SO_InvoiceWrk.BillToCountryCode} <> "US"  Works fine.  

    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Assistant to the Traveling Secretary
    ------------------------------

    Original Message


  • 8.  RE: Crystal Reports Suppression Formula

    Posted 09-28-2020 08:46
    @Phil McIntosh
    Scenario 1: If {SO_InvoiceWrk.BillToCountryCode} = "CAN" and the formula below is used, should the text box be suppressed?
    {SO_InvoiceWrk.BillToCountryCode} <> "USA" OR {SO_InvoiceWrk.BillToCountryCode} <> "US" ​

    Scenario 2: If {SO_InvoiceWrk.BillToCountryCode} = "USA" and the formula below is used, should the text box be suppressed?
    {SO_InvoiceWrk.BillToCountryCode} <> "USA" OR {SO_InvoiceWrk.BillToCountryCode} <> "US" ​

    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Assistant to the Traveling Secretary
    ------------------------------

    Original Message


  • 9.  RE: Crystal Reports Suppression Formula

    Posted 09-28-2020 09:26
    Doug,

    If you use the OR, then any value of {SO_InvoiceWrk.BillToCountryCode} you plug in will evaluate to true, because "US"  <> "USA" and "USA"  <> "US", and with OR if either side is true then the whole expression evaluates to true.

    Phil

    ------------------------------
    Phil McIntosh
    President
    Friendly Systems, Inc.
    Asheville NC
    678.273.4010 ext 5
    ------------------------------

    Original Message


  • 10.  RE: Crystal Reports Suppression Formula

    Posted 09-28-2020 09:38
    I think I got it. 

    If I plug in "CAN" then the "OR" evaluation expression becomes "CAN" <> "USA" OR "CAN" <> "US" so both of these are true and the expression evaluates to true and the text box is suppressed. ​​If I plug in "USA" then the "OR" evaluation expression becomes "USA" <> "USA" OR "USA" <> "US" so since "USA" <> "US" is true and the expression evaluates to true and the text box is suppressed. ​

    So, that's why I am using AND.

    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Assistant to the Traveling Secretary
    ------------------------------

    Original Message


  • 11.  RE: Crystal Reports Suppression Formula

    Posted 09-27-2020 08:58
    @Phil McIntosh
    Using this formula. Not {SO_InvoiceWrk.BillToCountryCode} IN ["US","USA"]).
    ​Not {AUS} IN ["US","USA"]) = TRUE
    So text box should be suppressed.   Am I missing something?

    ------------------------------
    Doug Higgs
    Midwest Commerce Solutions, Inc
    (312) 315-0960
    Assistant to the Traveling Secretary
    ------------------------------

    Original Message


  • 12.  RE: Crystal Reports Suppression Formula

    Posted 09-30-2020 12:00
    You're missing the left paren.

    Not ({SO_InvoiceWrk.BillToCountryCode} IN ["US","USA"]).

    ------------------------------
    Chris St. Amand
    Arizona Accounting & Information Systems
    ------------------------------

    Original Message


Related Content