Counting submissions on a Top N report

[cgilmore]

I use Windows XP, CR 9.0

I have created a report which shows our top 10 agents by submissions. I have the report grouped by Agent Name. In the report footer I have a running total which counted the # of submissions for Bound or reinstated policies. My total is correct and it includes the top 10 agents but also includes the subs in Others. My boss wants another total which shows how many subs for only the top 10 agents. He wants to exclude "Others" in the total but he still wants to see Others on the report a long with the current total of all subs. I created a formula in the details section which is @top 10 and says if GroupName ({CR_AG.cagntname}) <> "Others" then 1 else 0. This puts a 1 beside each record which is not in Others. When I try to count where @top 10 =1 I get an error message. How can I exclude counting "Others"?

 

[lbass]

Place the following formula in the topN group section:

whileprintingrecords;
numbervar grpcnt := grpcnt + 1;
numbervar cntpolicies;

if grpcnt <= 10 then cntpolicies := cntpolicies + {table.numbersubmitted};

Then create a display formula for the report footer:

whileprintingrecords;
numbervar cntpolicies;

-LB

 

[cgilmore]

Thank you. That worked, but I don't understand how it worked. I have used variables before.

It looks like grpcnt := grpcnt +1 is assigning a number to each group, correct?

Then you are saying if grpcnt <=10 then cntpolicies := cntpolicies +
count ({CR_SU.csubmissionid}, {CR_AG.cagntname})

How would you count submissions ({CR_SU.csubmissionid}, {CR_AG.cagntname}) where cr_su.cstatus is only in ["B", "M", "P"] and is only the the <= 10 group?

 

[lbass]

I'm not exactly sure what you mean. If for each group you only want to count submissions with these values, then create a formula {@BMP}:

if {cr_su.cstatus} in ["B", "M", "P"] then 1 else 0

Then amend the accumulation formula to:

whileprintingrecords;
numbervar grpcnt := grpcnt + 1;
numbervar cntpolicies;

if grpcnt <= 10 then cntpolicies := cntpolicies + sum({@BMP},{CR_AG.cagntname});

If on the other hand, there is a value {cr_su.cstatus} at the group level and you only want to total groups with that value, then use:

whileprintingrecords;
numbervar grpcnt := grpcnt + 1;
numbervar cntpolicies;

if grpcnt <= 10 and
{cr_su.cstatus} in ["B","M","P"] then cntpolicies := cntpolicies + count({CR_SU.csubmissionid},{CR_AG.cagntname});

-LB

 

[cgilmore]

That worked. Thanks for your help!