generating group value from records

[Honyo]

Hi

I am using 8.5, SQL 7 and need some help with groups.

I have created a group called BTN. There are various records under each BTN group. The one record is a value of 0, 1 or 2 (string value).

BTN address PFI

e.g. BTN1 abcdef 1
rtyuer 1
iooiuh 2
pppooo 0

BTN 2 nmmmmm 2
uuuuuu 0

BTN3 qqqqqq 1

Part 1
I need to insert a group total (subtotal) that has a look at the PFI value by group. If a 1 is present then the group result is a "Y" if no 1's present then it's a "N".
BTN1 and BTN3 would be a "Y". BTN2 would be a "N".

Part 2
I then need to total up all the "Y"'s and "N"'s at the end of the report.

Can anyone help with this?

 

[lbass]

Create a formula {@PFI1} for the detail section:

If {table.PFI} = "1" then 1 else 0

Create a second formula for the group header level:

if sum({@PFI1},{table.BTN}) > 0 then "Y" else "N"

For your report totals, use the running total editor and for {#Y} select {table.BTN}, distinct count, evaluate based on a formula:

sum({@PFI1},{table.BTN}) > 0

Reset never. For {#N} just change the sign in the evaluation formula to "=".

-LB