Print Page | Close Window

WHERE clause not recognized in Formula Field.

Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Technical Questions
Forum Discription: Formulas, charting data, Crystal syntax, etc.
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=14491
Printed Date: 28 Apr 2024 at 6:42pm


Topic: WHERE clause not recognized in Formula Field.
Posted By: Gnibmilc
Subject: WHERE clause not recognized in Formula Field.
Date Posted: 26 Sep 2011 at 7:08am
Please help. I am trying to use a WHERE clause in a formula field to narrow down my results and Crystal does not seem to recognize it. I am using Crystal 2008.

Here is an example but the "WHERE" does not turn blue. It gives me an error stating the WHERE clause does not seem to be part of the formula.

IF {result.entry} = ["H7", "H8"]
THEN {result.entry} WHERE {result.name} = "Ct"

Thanks


Replies:
Posted By: DBlank
Date Posted: 26 Sep 2011 at 8:04am
not fiollowing what you are doing here...are you using this in the select expert?
this is more likely an and statement...?
{result.entry} in ["H7", "H8"] and {result.name} = "Ct"

Posted By: Gnibmilc
Date Posted: 26 Sep 2011 at 9:36am
This is in the Formula fields.

We have several entries for {result.name} and each one of those has an entry for {result.entry}. The output I need is the {result.entry} when the {result.name} = "Ct" but I only want this when the {result.entry} is "H7 or H8" when the corresponding {result.name} = "Well ID".

I hope this makes mores sense.

Posted By: DBlank
Date Posted: 26 Sep 2011 at 9:47am
you are referencing only one table here, correct?
1. FOmrula fields do not have any impact on what rows are selected for inclusion in the report. I oftne see people try to write if-then formulas that look like they are trying to convert rows of data using a where clause. Crystal uses the select expert to deterrmine what rows need to be included (TRUE) or excluded (FALSE) from the report. (You can also do group conditionas but that is a little different and just mentioning it here as an FYI).
you want to include a row of data in your report if:
{result.name} = "Ct" and {result.entry} is "H7 or H8" and {result.name} = "Well ID"...
The first and last conditions are not making sense since they reference the same field?
Can you clarify that, please.
 
 

Posted By: Gnibmilc
Date Posted: 26 Sep 2011 at 10:03am
To give a very simple example. Imagine we have a kernel of corn and we want to record two results for the same sample (kernel), size and color. There is a field {result.name} that can be either "color" or "size". The corresponding {result.entry} fields would be "1.3" and "Yellow" respectively. I want to display the size of a kernel but only when the color is yellow. I hope this helps clarify.

Posted By: DBlank
Date Posted: 26 Sep 2011 at 10:05am
so you have 2 rows of data for each 'kernal' and you need to do select statment evlauating both rows for a sepcific condition, correct?

Posted By: Gnibmilc
Date Posted: 26 Sep 2011 at 10:17am
correct

Posted By: DBlank
Date Posted: 26 Sep 2011 at 10:25am

Are you allowed to write a command for the report?

Posted By: Gnibmilc
Date Posted: 26 Sep 2011 at 10:27am
I suppose I can. I am not to sure how.

Posted By: DBlank
Date Posted: 26 Sep 2011 at 10:32am
Here is a non-command way.
group on the Primary Key (the sample identifier)
create a flag to check if the grouping has the value you want to show.
//Flag example
if result.name = 'color' and result.entry='yellow then 1
Sum this flag at the grou level
SUM(@flag,result.pkfield)
if your SUM>0 then youwant to show this group.
go into the select epert
show the formula
change the fomrula to 'group selection'
add your criteria here
SUM(@flag,result.pkfield)>0

Posted By: Gnibmilc
Date Posted: 26 Sep 2011 at 10:40am
Thank you so much for your help I will definitely try this and see if it will work. However, in other situations I would like to be able to use my WHERE statement, do you have any idea why Crystal 2008 is not recognizing it?

Posted By: DBlank
Date Posted: 26 Sep 2011 at 10:55am

the 'where clause' for selecting records is defined by what you put in the select statement.

If you go to Database > Show SQL Query, you will the select, join and where parts of the SQL that defines the data in the report.

Posted By: Gnibmilc
Date Posted: 26 Sep 2011 at 11:16am
THANK YOU SO MUCH!!! After 2 weeks of research I have finally gotten my report working!!!


Print Page | Close Window