Crystal Reports Multi Part Formula and Parameters

Hi,

I have a report I am developing and I have a question about a formula.  I think the error might be in my coding.  In Crystal Reports I have a total of three parameters setup, a total of four paremeters in all, that prompts the user when you run this report.  My report queries and filters our SQL database based on Room Location (Inbetween Statement), Based on the assigned person to that room, and based on whether an Item was Found or Not Found in those rooms.  The goal of the report is for a user to freely query a report to view all Found or Not Found items with the option of both for a section of rooms under a specific user/custodian.  Here are my Parameters:

   Dynamic Parameter 1 = Room1 *THIS IS FOR AN INBETWEEN STATEMENT WITH PARAMETER 2*
   Dynamic Parameter 2 = Room2 *THIS IS FOR AN INBETWEEN STATEMENT WITH PARAMETER 1*
   Static Parameter 3 = Custodian  (Added "ALL" Value)
   Static Parameter 4 = F or NF (Added "BOTH" Value)

For each of the Static Parameters, I created a 'ALL' or 'BOTH' to select all the data. THE PROBLEM ONLY HAPPENS WHEN ALL is selected for the Custodian. I am unable to successfully filter between Found(F) or Not Found(NF) items when ALL is selected.  When a user selects a specific custodian and does not select ALL, it pulls the correct data based on the selection where F, NF, or BOTH. Thats when it works.  But if ALL is selected, it pulls all the F and NF information, regardless if you selected to query only 'F' or 'NF".  It ignores that section of the query.  Here is the formula I am using:

{TABLE.LOCATION} in {?Room 1} to {?Room 2} and
If {?Custodian}='ALL'
Then {TABLE.USER} like '*'
Else {TABLE.USER} = {?Custodian} and
If {?F or NF}='BOTH'
Then {TABLE.F_NF} like '*'
Else {TABLE.F_NF} = {?F or NF}


Any ideas or suggestions?
digitalhitman00Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

vetaldjCommented:
For beginning you can check SQL Query that Crystal Reports generates with {?Custodian} = 'ALL'. It can help understand you what's wrong in your selection formula.
0
peter57rCommented:
{TABLE.LOCATION} in {?Room 1} to {?Room 2}
and
({?Custodian}='ALL' or {TABLE.USER} = {?Custodian})
 and
({?F or NF}='BOTH' or {TABLE.F_NF} = {?F or NF})

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
digitalhitman00Author Commented:
Actually, silly me.  I put parenthesis in and it now works correctly.

{TABLE.LOCATION} in {?Room 1} to {?Room 2} and
(
If {?Custodian}='ALL'
Then {TABLE.USER} like '*'
Else {TABLE.USER} = {?Custodian}
)
and
(
If {?F or NF}='BOTH'
Then {TABLE.F_NF} like '*'
Else {TABLE.F_NF} = {?F or NF}
)

That wound up working!  Thanks all!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.