Solved

Crystal Reports Multi Part Formula and Parameters

Posted on 2009-07-01
3
450 Views
Last Modified: 2012-08-14
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?
0
Comment
Question by:digitalhitman00
3 Comments
 
LVL 2

Expert Comment

by:vetaldj
ID: 24758520
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
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 24758586
{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
 

Author Comment

by:digitalhitman00
ID: 24766370
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

Featured Post

Courses: Start Training Online With Pros, Today

Brush up on the basics or master the advanced techniques required to earn essential industry certifications, with Courses. Enroll in a course and start learning today. Training topics range from Android App Dev to the Xen Virtualization Platform.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
Hello everyone, Hope you find this as helpful as we did. We have on the company I work for an application built in Delphi V with Crystal Reports 8. We all know that Crystal & Delphi can be temperamental sometimes and the worst thing is, nearly…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

816 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now