Solved

Crystal Reports Multi Part Formula and Parameters

Posted on 2009-07-01
3
453 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
There have always been a lot of questions related to when Crystal Reports evaluates report components (such as formulas, summaries, cross-tabs, charts, to name a few examples). Crystal Reports uses a two-pass reporting process to provide greater …
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

756 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