Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: Using multiple IF THEN statements in select expert Post Reply Post New Topic
Author Message
Tonyak74
Newbie
Newbie
Avatar

Joined: 24 Apr 2013
Online Status: Offline
Posts: 28
Quote Tonyak74 Replybullet Topic: Using multiple IF THEN statements in select expert
    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.
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet 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

Edited by hilfy - 01 Feb 2023 at 10:05am
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump 		You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.030 seconds.
HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | CLICK PLAYS | COACH FORUMS | TROPICAL FISH

© 2004-2024 Brian Bischof. All Rights Reserved.