TOP 10 With a Twist

[khdavis]

Hi All! I'm using CR10 and have a Top 10 question that has me totally stumped!

I'm doing a Top 10 report based on a group summary count.

That part's easy enough. However the client has now asked to only include groups where the sum is greater than 1 AND still give a Top 10 count.

I'm able to remove the groups where the count equals 1 by using Report > Selection Formulas > Group but I can't figure out how to get the report to again display top 10 results.

Example:

Name #
Server 1 10
Server 2 15
Server 3 1
Server 4 40
Server 5 1
Server 6 12
Server 7 15
Server 8 1
Server 9 17
Server 10 4
Server 11 6
Server 12 8
(additional servers and counts)

Based on this example, I need to remove Servers 3, 5 and 8 and then have the report display the subsequent top 10 of all the remaining servers.

Is this possible? If anyone can help I'd greatly appreciate it! Thanks...

 

[lbass]

The top10 will automatically remove the sums = 1, unless you have a top10 with less than 10 with sums > 1. Then there wouldn't be a top10, but instead a top9, e.g. To do this I think you could do a top10 sort and then also have a suppression formula for the group. You reference both sums and counts, so adjust the following section suppression formula as necessary:

count({table.ID},{table.group}) = 1

-LB

 

[khdavis]

lbass, thanks for responding. You're right for the part based just on the counts as I discribed in the original post.

In my haste (and I apolize) I forget the second part of the equation...


Name # Avg Repair Time
Server 1 10 72.12
Server 2 15 63.99
Server 3 1 169.19
Server 4 40 48.41
Server 5 1 68.58
Server 6 12 62.96
Server 7 15 45.66
Server 8 1 45.72
Server 9 17 63.99
Server 10 4 52.49
Server 11 6 12.56
Server 12 8 154.22 etc...

I need to do a top 10 based on the Avg Repair Time (not count of Server) and exclude servers where the count is 1.

 

[lbass]

You could potentially add a command that returns the server count, like:

SELECT count(`table`.`serverID`) as cnt, `table.`serverID`
FROM `table` `table`
Group by `table`.`serverID`

Use the syntax appropriate for your datasource. Link the command to your table on the server ID field. Then in the main report use a record selection formula like:

{command.cnt} > 1

Then you should be able to do the top10 group sort without a problem.

-LB