Help with Group

[goduke]

I have a report where I want to Group based on a Count. For example, I have the report Grouped on leader and in the Details Section it shows the people under that leader. Then I get a count of people for each leader and put it in the Leader Group Header. What I would like to do is have my first Group be a formula that groups the report on whether each leader has 2 or more people under him. For example, I have this:

Grp John Smith - Count(3)
Bill Jones
Dawn England
Fred Davis
Grp Clint Mears - Count (1)
Heather Cummings
Grp Mandy Johnson - Count (2)
Dante Jackson
Jennifer Riddle
Grp Jason Johnson (1)
Ashley Watson


And would like to see this:

2 or More
John Smith - Count(3)
Bill Jones
Dawn England
Fred Davis
Mandy Johnson - Count (2)
Dante Jackson
Jennifer Riddle

1 or Less
Clint Mears - Count (1)
Heather Cummings
Jason Johnson (1)
Ashley Watson

Please let me know if this is possible.

 

[lbass]

As I've mentioned in at least one or your previous threads, you should always specify your CR version, as it affects what solutions you can use. Hunted again in order to see that you have CR 10 or higher by now.

Create a command to use as your datasource, something like:

select table.`leader`, table.`employee`,
(select count(A.`employee`)
from table A
where A.`leader` = table.`leader`) as emplcnt
from table

The punctuation/syntax depends upon your datasource/connectivity, so adjust as necessary. Then you can create a formula like this:

if {command.emplcnt} >= 2 then
"2 or more" else
"1 or less"

Insert a group on this formula.

-LB