Conditional group total (sum or count)

[sasa888]

Hi all, I would like to know if there is a way not to count records that have been suppressed. In other words, I want to ONLY count the non-suppressed records. Please let me know if there is a way.

 

[kwalters110]

Are you supressing a detail section based on a condition? If so then you can create a that uses the same condition to output a 1 or a 0. Then you can summarize that with Sum which will give the the number of records suppressed.

Formula {@Suppress 1_0}:
If <Condition> = true Then 1 Else 0

This is assuming that true means the section is suppressed. Change that to false if you wish to count the records that are actually being displayed.

Also if you want to sum a particular qty if it is showing, then you can replace the 1 with the field for that qty value.

Is that what you wanted?

 

[sasa888]

Hi Kwalters110, thanks for your reply. The condition is in the group section unfortuntely. Please help.

 

[kwalters110]

Could you please give a description or example of what you would like to accomplish? That would help alot.

 

[sasa888]

Hi, here is what I need to do:

Total Count
(Group Level 2)Employee1 10
Employee2 9
Employee3 1
Employee4 2
(Group Level 1)Grand Total 22

Notice that the grand total is 22, but what I really want is 21 (excluding any employee with only 1 total count). Please help.

 

[Madawc]

Are you saying thay you suppress some groups, and only want the groups that are not suppressed?

You could achieve this by variables, a formula field in each non-suppressed detail line that says

// Accumulate selectively
whileprintingrecords;
if {@XS_Reply} then
NumberVar MyCount:=MyCount+1

Followed in the report footer by a display:

//Display
whileprintingrecords;
NumberVar MyCount

That should work, but how are you suppressing the unwanted groups? Is there a way you could get rid of the records that go into the unwanted groups?

Madawc Williams (East Anglia, UK)

 

[sasa888]

I tried that before and it doesn't work Any other suggestions?

 

[lbass]

To get the grand total, you could insert a running total in the detail section where you select count of {table.field}, evaluate based on a formula:

count({table.field},{table.employee}) <> 1

Reset never.

-LB

 

[sasa888]

Ok, finally I got the grand total in place, how about if I have to sum at group level as well? I know I need to reset, but at which level?

 

[lbass]

For the employee groups, you just need to right click on {table.field} and insert a summary (count). If you are suppressing counts = 1, then you would take care of the that separately, by going to the section expert->group header/details/group footer->suppress->x+2 and entering:

count({table.field},{table.employee}) = 1

Or are you saying you have an outer group--a higher level group than employee? Then use the running total and just change the reset to "on change of group" and choose that group.

-LB