Active TopicsMemberlistSearchHelpRegisterLogin

Technical Questions
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Technical Questions
Message Icon Topic: IF THEN ELSE Formula using IN clause on Integers Post Reply Post New Topic
Author Message
robtyketto
Newbie
Newbie
Avatar

Joined: 13 Jul 2009
Online Status: Offline
Posts: 12
Quote robtyketto Replybullet Topic: IF THEN ELSE Formula using IN clause on Integers
     Posted: 16 Aug 2011 at 9:03am
Greetings,
 
I am currently receiving a syntax error in the select expert for my record formula.
 
The formula uses a static parameter of type 'Number'.
 
I have added a value of 0.00 to the parameter with a description of 'ALL' to indicate select all integer variables.
 
The formula I have written is below:
{ACCOUNTS.ACCOUNT_NAME} = {?Account} and
IF {?GM Priority}=0.00 THEN {PRIORITIES.PRIORITY_ID} IN (1.00,2.00,3.00,4.00)
ELSE {PRIORITIES.PRIORITY_ID} = {?GM Priority}
 
Is the formula I'm trying to write possible with an IN clause (as I believe I can't use a LIKE clause with wildcards)?
 
If the parameter with value of 0.00 is selected I wanted to retrieve all records where the PRIORITY_ID is any of the following: 1.00, 2.00, 3.00, 4.00
 
Any assistance would be appreciated?


Edited by robtyketto - 16 Aug 2011 at 9:04am
Thanks
Rob
IP IP Logged
hilfy
Admin Group
Admin Group
Avatar

Joined: 20 Nov 2006
Online Status: Offline
Posts: 3702
Quote hilfy Replybullet Posted: 16 Aug 2011 at 9:14am
You can't use an "If..Then" in the select expert.
 
I would rewrite it like this:
 
{ACCOUNTS.ACCOUNT_NAME} = {?Account} and
(({?GM Priority} = 0.00 and {PRIORITIES.PRIORITY_ID} in (1.00, 2.00, 3.00, 4.00)) OR
({PRIORITIES.PRIORITY_ID} = {?GM Priority}))
 
If  the only possible priority ID values are 1, 2, 3, and 4, then you can do the OR part of the formula like this:
 
({?GM Priority} = 0 or {PRIORITIES.PRIORITY_ID} = {?GM Priority})
 
Note where the parentheses are in both statements - they are important!  This won't work correctly without them.
 
-Dell
Proviti, Data & Analytics Practice
www.protiviti.com/US-en/data-management-advanced-analytics
IP IP Logged
robtyketto
Newbie
Newbie
Avatar

Joined: 13 Jul 2009
Online Status: Offline
Posts: 12
Quote robtyketto Replybullet Posted: 16 Aug 2011 at 1:23pm
Originally posted by hilfy

You can't use an "If..Then" in the select expert.
 
I would rewrite it like this:
 
{ACCOUNTS.ACCOUNT_NAME} = {?Account} and
(({?GM Priority} = 0.00 and {PRIORITIES.PRIORITY_ID} in (1.00, 2.00, 3.00, 4.00)) OR
({PRIORITIES.PRIORITY_ID} = {?GM Priority}))
 
If  the only possible priority ID values are 1, 2, 3, and 4, then you can do the OR part of the formula like this:
 
({?GM Priority} = 0 or {PRIORITIES.PRIORITY_ID} = {?GM Priority})
 
Note where the parentheses are in both statements - they are important!  This won't work correctly without them.
 
-Dell
 
Thanks for the help i understand and implemented the formula without any issues Smile
 
Thanks
Rob
IP IP Logged
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

HOME | FREE EBOOK | PROGRAMMING GUIDE | PURCHASE | REVIEWS | CONTACT ME | FAQ | LINKS | PRIVACY | CLICK PLAYS | KONACAD

© 2004-2026 Brian Bischof. All Rights Reserved.