Report Criteria 1

[pandy1]

Hi,
I have my data like this in the report.

id Function

1 200
1 201
1 201
1 202
1 203

2 200
2 201
2 201
2 202
2 203

3 201
3 202
3 203

I would like to have the data like this:

1
2

Report footer: should have count of all id's


the criteria to pick up the data would be: id 1 has function 200 and 201
and also id 2 has the function 200 and 201 (Each id should have function 200 and 201)

Id 3 should not be displayed because it is not having function 200 though it has function 201

I am using Crystal Reports 8.5

 

[IdoMillet]

Create a formula:
IF {Function}=200 THEN 1 ELSE 0

Use Report, Edit Selection Formula, Group...
that checks for SUM (note: not count) of that formula being greater than 0.

hth,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[pandy1]

First of all, I need to display the id's which has both functions 200 and 201. how to write logic to pick only those id's.

after that i need to count all those id's which are displayed in the report.

thanks
pandy

 

[IdoMillet]

Same idea, just create TWO formulas and in the group selection criterion enter an expression that checks for the sum of both not being equal to zero.

Cheers,
- Ido

CUT, Visual CUT, and DataLink Viewer:
view, e-mail, export, burst, distribute, and schedule Crystal Reports.

http://www.MilletSoftware.com

 

[pandy1]

Thank you Ido, but I am not getting my results.

I created two formulas:

@Id200 :if ({ID}=200) then 1 else 0
@Id201 :if ({ID}=201) then 1 else 0

Then in the group selection: I wrote this:
({@Id200}+{@Id201})<>0

I am getting the records with either function ie., with 200 or 201. I want id's with both functions i.e., id should have 200 and 201.

I tried using @id200+@id201 >1 then i am not getting anything.

Thanks in advance.

pandy

 

[synapsevampire]

I assume that you mean that you might want ID 3 were it to have BOTH 200 & 201.

Use the Report->Edit selection formula->Record:

{table.function} in {200,201} to limit the rows to only those 2 types.

You might group by the ID, then use a formula in the details such as Ido suggested:
@200
IF {Function} = 200 THEN
1
ELSE
0

And another containing:

@201
IF {Function} = 201 THEN
1
ELSE
0

Now right click each of these formulas and select insert->summary->sum

Now you can use these formulas in the detail suppression (they will be in the list of report fields) in another formula such as:

(
sum(@200,{table.id} = 0
0r
sum(@201,{table.id} = 0
)

Should work.

-k

 

[pandy1]

Thanks a lot synapsevampire and Ido. I got the results i wanted.

Thank you very much for your help.

pandy