Formula to filter data

[austin22]

I am using Crystal 8.5 and the report is grouped by case number.

I have created a formula field named 'Action'and in the formula editor I have:

if {Action} in "ActionName" then
1
else
0

Then I have place the formula into the Group Selection Formula Editor as follows:

sum ({@Action},{Case Number)>0

Therefore, I receive all cases with specified ActionName. Now, what I want to do is go a little further, example:

ActionName is CETP, however, the case also shows an ActionName as TPP along with CETP. I want all cases to show up on my report with CETP but I do not want to see the data if it has both CETP and TPP on the same case.

How do I filter these cases out of my data?

 

[lbass]

Create a second formula {@TPP}:

if {Action} = "TPP" then
1

Then add a second clause to your group selection formula:

and
sum({@TPP},{table.casenumber}) = 0

-LB

 

[austin22]

Great! Thank you.

 

[austin22]

The formula seem to be not working, anymore. The data with both CETP and TPP is still showing up on the report.

I want to see cases with TPP, however, if a case has TPP and CETP together, I do not want to see that case.

Please advise.

 

[lbass]

So you want to see CETP or TPP only if they appear alone, not with each other?

-LB

 

[austin22]

That is correct.

 

[lbass]

(
sum ({@Action},{Case Number) > 0 and
sum({@TPP},{table.casenumber}) = 0
) or
(
sum ({@TPP},{Case Number) > 0 and
sum({@Action},{table.casenumber}) = 0
)

-LB

 

[austin22]

That did not work either...Here is my data set up

Case Number 1234
CETP
TPP

Case Number 5678
TPP

Case Number 8910
TPP

I want to suppress case number 1234 because in this case both the CETP and TPP is on this case.

 

[lbass]

Are you entering my formula in the correct place? You should be going to report->selection formula->GROUP and entering it there. I can see no reason why it wouldn't work. When a suggestion does not work, you should explain why--in this case, what results are you getting that are incorrect?

-LB

 

[austin22]

I have the following set up:

First formula
Formula Name TPP
if {Table.ActionCode} = "TPP" then
1
else
0

Second formula
Formula Name CETP
if {Table.ActionCode} = "CETP" then
1
else
0

Then I go to the report->selection formula->GROUP and enter the formula there. Now, when I use your formula as follows:

(
sum ({@TPP},{table.CaseNumber}) > 0 and
sum({@CETP},{table.CaseNumber}) = 0
) or
(
sum ({@CETP},{table.CaseNumber}) > 0 and
sum({@TPP},{table.CaseNumber}) = 0
)

I am still retrieving cases that have both the CETP and TPP on the same case. I have checked over and over of my set up and cannot find anything wrong.

 

[lbass]

Please identify your groups using fields names and group numbers.

-LB

 

[austin22]

The report is grouped by CaseNumber, Group #1

 

[lbass]

I'm sorry, I don't see how you could possibly be getting the results you say. Please show the ACTUAL group selection formula so we can troubleshoot it. Maybe you have some missing parens.

If these are the only two values that the Action Code can have, you could alternatively use:

distinctcount({table.actioncode},{table.casenumber}) = 1

-LB