Solved

Crystal Reports Multi Part Formula and Parameters

Posted on 2009-07-01
3
458 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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

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. …
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 …
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…

751 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