Print Page | Close Window

Need AND & OR formula for report

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=23028
Printed Date: 02 May 2024 at 4:57am


Topic: Need AND & OR formula for report
Posted By: DrewEC
Subject: Need AND & OR formula for report
Date Posted: 21 Dec 2022 at 9:14am
Hello there,

Thank you for your assisting. I'm trying to create a AND & OR formula that filters for unitcodes that starts with ("10" or "TH" or "CO") AND starts with ("WE").

The logic statement is, show me all incidents that have a unitcode dispatched (all of this is filtered for already) with a unitcode that starts with either "10" or "TH" or "CO" ALONG with any unitcode that starts with "WE".

I currently have this formula which is not working:

({incilog.unitcode} startswith ["10"] or {incilog.unitcode} startswith ["TH"] or {incilog.unitcode} startswith ["CO"]) and
({incilog.unitcode} startswith ["WE"])


Any help is appreciated.


Replies:
Posted By: DrewEC
Date Posted: 21 Dec 2022 at 10:50am
I'm also attempting this from a suppression standpoint as well which is something like:

If
{incilog.unitcode} like ["10*", "CO*", "TH*" and "WE*"]
then false
else if {incilog.unitcode} like ["WE*"]
then true
else if {incilog.unitcode} like ["10*", "CO*", "TH*"]
then true
else true

The logic for the above is, if the {incolog.unitcode} field contains BOTH anything that starts with 10*, etc etc, AND anything that starts with WE, don't suppress , otherwise suppress everything else (including anything that only has a unitcode that starts with 10*, etc. or WE* by itself.

Posted By: hilfy
Date Posted: 03 Jan 2023 at 8:35am
You can't have a single unitcode that starts with (10 or CO or TH) and also starts with WE. So really what you need is starts with 10 or CO or TH or WE. This will get you records with unit codes that start with any of the four options.

Try this for a formula if you just need to identify the records: 

Left({incilog.unitcode}, 2) in ["10", "CO", "TH", "WE"]


If you're using this in the Select Expert to filter the records coming into the report, you don't want to do it that way, though, because Crystal can't pass its own formulas to the database for processing and will pull ALL of the data into memory and filter it there. Instead, you'll do this in the Select Expert formula: 

(

  {incilog.unitcode} StartsWith "10" OR

  {incilog.unitcode} StartsWith "CO" OR

  {incilog.unitcode} StartsWith "TH" OR

  {incilog.unitcode} StartsWith "WE"

)

Note the use of parentheses - if you have any other selection criteria, you MUST have them in order for the filter to work correctly.

-Dell

-------------
Proviti, Data & Analytics Practice
http://www.protiviti.com/US-en/data-management-advanced-analytics - www.protiviti.com/US-en/data-management-advanced-analytics


Print Page | Close Window