Print Page | Close Window

Using AND

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=20521
Printed Date: 06 May 2024 at 2:31am


Topic: Using AND
Posted By: drummondj
Subject: Using AND
Date Posted: 28 Feb 2014 at 7:07am
Hi

I have a table of test results.

I need to select records where TestA=10 and TestB=20

I have set formula for each test, eg:
FormA
If TestA = 10
then 1;

FormB
If TestB = 20
then 1;

Each formula returns results individually.

But when I try and combine them I get no records returned.

What am I doing wrong.






Replies:
Posted By: lockwelle
Date Posted: 03 Mar 2014 at 5:26am
so if testa= 10 and testb=20 then 1

returns nothing?

simple question, are you sure that there are records that satisfy the condition?

do you have the formulas displaying next to the values so that you can trouble shoot? That would be the first thing that I would do, would be to verify if the report is showing 1 for both formulas on the same line to verify the data and the formulas. If there are no rows with 2 1's then I and you think there should be, I would verify that there is data that matches

Posted By: drummondj
Date Posted: 04 Mar 2014 at 7:29am
Thanks for the response it is appreciated.

Yes there are records that should meet the criteria - there are 3 that overlap.

In the formula TESTA I have:
If Test=TestA and Result = 10 then 1

In the formula TESTB I have:
If Test=TestB and Result = 20 then 1

And then I try and combine themin TESTAB
If TESTA= 1 and TESTb = 1 then 1

then I select if TESTAB=1

Thanks

Posted By: lockwelle
Date Posted: 04 Mar 2014 at 8:35am
but for the row, can the test be both A & B...so it is probably always false...I would think.

Again, 1 field cannot hold 2 different values at the same item...or am I missing something?

Posted By: drummondj
Date Posted: 04 Mar 2014 at 11:07am
Hi

No they are not in the same row. There are two columns Test and Result.

Thanks

Posted By: DBlank
Date Posted: 05 Mar 2014 at 4:25am
I think you are looking for a group select criteria here.
You need 2 formula, one for A and one for B
 
you have to group on the "test number" (whatever identifies a set of rows as one test) and set seperate group level criteria using your results from the two formula together
SUM(FormA,testnumber)>0 and sum(FormB,testnumber)>0

Posted By: drummondj
Date Posted: 06 Mar 2014 at 2:59am
Thanks DB, but I'm sorry you've lost me there, maybe I should explain a bit more.

This is a table of results of pathology blood tests and results e.g. glucose and cholesterol.

In the table a patient has many rows, one for each test performed.

I need to select the patients where the test is glucose and it has a result of > 8, and where the same patient has a cholesterol test with a results of > 5. Ideally I don't need to list the results - just the patients details.

Posted By: DBlank
Date Posted: 06 Mar 2014 at 3:53am
easiest to do in SQL with a store proc but you can do it with a command too.
If you want a solution using your existing process
group on client
create two formulas to flag each of your conditions
//glucose
if test=glucose and result>8 then 1 else 0
 
//cholesterol
if test=cholesterol and result>5 then 1 else 0
 
sum each of these at the client group level
now any client with both sums >0 are your clients you want
use that in the group select
 
 
SUM(@glucose,clientid)>0 and sum(@cholesterol,clientid)>0
 
Likely you have date issues to include but this should give you an idea how this can work

Posted By: drummondj
Date Posted: 08 Mar 2014 at 6:30am
It works DB - you are a star.

I do have a clever young friend that had done it in SQL - but I just couldn't give up on this. It is a very common question my workplace. Thanks for all your help.


Print Page | Close Window