Count records based on criteria within Groups 1

[ramblinrose]

I am using CR XI. I have 2 groups. I would like to have summeries based on criteria within the 2nd group.

Group I = WorkerID
Group II = ApplicationID
Details = AppPending AppDenied AppApproved

Data would look like this:

MM21
12345
Y N N
N Y N
N Y N
N N Y

What I want is a count based on the following. If there is a Y in any of the records for the AppApproved within Group II then 1 for AppsPending. If there is a Y in any of the records for AppDenied and NOT a Y for AppApproved within Group II then 1 else 0. If there is a Y in any of the records for AppPending and NOT a Y for AppDenied or AppApproved within Group II then 1 else 0. These counts are based on the records within the Application Group and not each record. So in this instance the result would be: 0 0 1

I then to sum the group totals of 2 for group 1.

How can I accomplish this?

 

[lbass]

Create three formulas like the following for the detail section:

//{@approved}:
if {table.appapproved} = "Y" then 1

//{@denied}:
if {table.appdenied} = "Y" then 1

//{@pending}:
if {table.apppending} = "Y" then 1

Then place the following formula in GH2 and suppress it:
whileprintingrecords;
numbervar appr := 0;
numbervar den := 0;
numbervar pend := 0;
numbervar sumappr;
numbervar sumden;
numbervar sumpend;

if sum({@approved},{table.applicationID}) > 0 then
appr := 1;
if sum(({@denied},{table.applicationID}) > 0 and
sum({@approved},{table.applicationID}) = 0 then
den := 1;
if sum(({@denied},{table.applicationID}) = 0 and
sum({@approved},{table.applicationID}) = 0 and
sum(({@pending},{table.applicationID}) > 0 then
pend := 1;
sumappr := sumappr + appr;
sumden := sumden + den;
sumpend := sumpend + pend;

Then in GF2, place separate display formulas for each:

whileprintingrecords;
numbervar appr;

Repeat for pend and den.

In GH1 place this reset formula:
whileprintingrecords;
numbervar sumappr := 0;
numbervar sumden := 0;
numbervar sumpend := 0;

Then in the GF1, add a display formula for each:

whileprintingrecords;
numbervar sumappr;

Repeat for sumpend and sumden.

-LB

 

[ramblinrose]

Thank you so much. This worked. I also discovered that I had to remove "repeat group header" so the summaries would not reset on each page when the groups spanned over more than 1 page. You are great!

 

[lbass]

You don't need to remove the repeat group header. Instead, change the reset formula to:

whileprintingrecords;
numbervar sumappr;
numbervar sumden;
numbervar sumpend;
if not inrepeatedgroupheader then(
sumappr := 0;
sumden := 0;
sumpend := 0
);

-LB