Technical Questions: Using multiple IF THEN statements in select expert

Print Page | Close Window

Using multiple IF THEN statements in select expert

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=23029
Printed Date: 27 Apr 2024 at 9:02pm

Topic: Using multiple IF THEN statements in select expert

Posted By: Tonyak74
Subject: Using multiple IF THEN statements in select expert
Date Posted: 04 Jan 2023 at 10:32am

I have a Crystal report question. Hopefully, it is a very easy solution that I am overlooking.
I created a Static parameter for Facility with 3 Facility choices to choose from. For 2 of the selections in the parameter my customer wants to have it actually report for 2 Facilities.

I then created a Formula to add to the report:
if {?Facility to Report} = "1720 S UNIVERSITY DR" then {Facility.FacilityDescr} like "*1720 S UNIVERSITY DR*"
Else if {?Facility to Report} = "5225 23RD AVE S" then {Facility.FacilityDescr} like "*5225 23RD AVE S*" or {Facility.FacilityDescr} like "*5270 22 AVE S*"
Else if {?Facility to Report} = " 801 N BDWY HOSPITAL" then {Facility.FacilityDescr} like"* 801 N BDWY HOSPITAL*" or {Facility.FacilityDescr} like "*510 MILLS AVE*"
The issue I am running into is if there is data for 1720 S UNIVERSITY DR, and all of the locations where selected, then the rest of the data does not show up on the report.
If I select them individually the data shows up fine.
Do you see a simple solution in my formula?
Any help would be appreciated.

Replies:

Posted By: hilfy
Date Posted: 01 Feb 2023 at 10:05am

Don't use IF then statements in the Select Expert! They will slow down your report because Crystal will pull all of the data into memory and filter it there instead of pushing the filter to the database. Instead, do something like this:


(

  ({?Facility to Report} = "1720 S UNIVERSITY DR" AND 

   {Facility.FacilityDescr} like "*1720 S UNIVERSITY DR*")

  OR

  ( {?Facility to Report} = "5225 23RD AVE S" AND 

    ({Facility.FacilityDescr} like "*5225 23RD AVE S*" or 

     {Facility.FacilityDescr} like "*5270 22 AVE S*")

  )

  OR

  ({?Facility to Report} = " 801 N BDWY HOSPITAL" AND 

    ({Facility.FacilityDescr} like"* 801 N BDWY HOSPITAL*" or 

     {Facility.FacilityDescr} like "*510 MILLS AVE*")

  )

)

Notice how I've used the parenthese here. Because of the order of processing when you have OR's, you need the parenthese to make sure everything works right.

-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