Excluding Running Totals from Grand Total

[arodri]

Hello,

I have a report with a "Total" column that is a running total based on a formula, and the "Grand Total" is also a running total based on a formula. Basically, the report lists employees from a certain location, and the total number of a specific item that was sold by them. I have three groups in the report, Region(#1), Sitename (#2), and Employee name (#3). Here's a very simplified example of the part of the report that I'm having trouble with:

Sitename#1

Employee#1 57
Employee#2 120
Employee#3 3

Grand Total 180


What I need to do is exclude any records from the report that have a total of less than 20. So Employee#3 wouldn't show up, and the Grand Total would be 177. The problem is I can't put it in a record selection formula because it's a running total. I was able to exclude Employee#3, but then it doesn't exclude it from the Grand Total.

Is there any way to make this happen by just making changes in the report? Or do I have to make the changes in the stored procedure?


Thanks!

 

[lbass]

Add a formula like this to the employee group footer:

whileprintingrecords;
numbervar gt;
if {#emplrt} >= 20 then
gt := gt + {#emplrt};//where {#emplrt} is the running total for the employee group.

In the report footer, add this display formula:

whileprintingrecords;
numbervar gt;

-LB

 

[arodri]

Hi lbass,

Thanks so much for that -it was a huge help. What you gave me worked perfectly for the Grand Total for the entire report, but I have many different sitenames, and a Total for each sitename that I also need to do the same thing to. In order for those Site Totals to reflect the correct totals (excluding items <=20) should I just create another set of the formulas you gave me, but just add a reset formula in the Group#2(sitename) header?
I have a rough idea of what the reset formula would look like:

whileprintingrecords;
numbervar gt;
gt := 0;


Does this sound like the correct approach?

Thanks!

 

[lbass]

You should use a different variable name for three formulas, so use "site" or something and then add a reset formula to the group header, add the accumulation formula to the employee group footer and then add the display formula to the site group footer, using the new variable name in each. If you have "repeat group header on each page" set, then change the reset formula to:

whileprintingrecords;
numbervar site;
if not inrepeatedgroupheader then
site := 0;

-LB

 

[arodri]

Okay perfect - that makes sense. Thank you so much for your help!