Solved

Crystal Reports Multi Part Formula and Parameters

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

Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
across tab - Distinct Count - Crystal Reports 2 86
Crystal Reports 12 41
How to modify the SQL query in Crystal 2016 1 28
Dates formatted Differently on another pc 2 16
Hot fix for .Net Crystal Reports 10.2.3600.0 to fix problems with sub reports running on 64 bit operating systems ISSUE: Reports which contain subreports fail with error "Missing Parameter Value" DEPLOYMENT SERVER OS: Windows 2008 with 64 bi…
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 …
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

837 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