Cross Tab w/ Formula for selection criteria not resulting correctly

[medpcjockey]

I've put together a cross-tab table that needs to show the top 5 productive analysts for a specific set of Analyst groups.
***************************
@Top5Analysts #ofTickets %ofTickets
"" 1225 96%
Analyst2 19 1%
Analyst3 4 0%
Analyst4 4 0%
Analyst5 4 0%
***************************
Couple of problems:
1. I don't have any empty fields so I should get a name for the missing Analyst1 field.
2. The #ofTickets count is wrong for Top 5 Results
3. The %ofTotal is only a total percentage of all tickets by the top5Analyst.

Here is the formula used in the cross-tab
***********************
@Top5Analysts
If {Command.Status} = "Closed" and ({Command.Case Type}= "Break/Fix" or {Command.Case Type}= "Quick Close")
and {Command.Assigned Group}in ["Group A", "Group C", "Group F", "Group G"]
Then {Command.Assigned Individual}
else {@Null}

*@Null is an empty formula
************************
Other Solutions already tried.
1. Because I have other tables and calculations in the report that require a much larger data set, I cannot use the selection criteria wizard to narrow down the results.
2. Subreport would not work becuase this is a problem I am having with different tables. To resolve the problem for all my tables using a subreport would be way too complex and fragile, and make running the report too time consuming.
3. The best alternative I have come up with is to use the following set of forumulas to manually list the top five assigned individuals. I have 5 iterations of the NthMostFrequent Command to list the top 5 Individuals within the group array.
**********************************
@5thMostFrequentAnalyst
NthMostFrequent (5, {@Top5Analysts})
***********************************
The sticking point is that I don't know how to get the ticket count for each iteration of the formula. When I try to count the number of tickets for @5thMostFrequentAnalyst I get a "this field cannot be summarized" error. Here is the formula I am trying to use to count the tickets for one of the top 5 analysts.
**********************************
@count5thMostFrequentAnalyst
count {@5thMostFrequentAnalyst}
***********************************

Anyone know how to overcome this problem?

 

[scottostanek]

As for why you Analyst1 position isn't being returned I would suspect the second section of your formula:

@Top5Analysts
If {Command.Status} = "Closed" and ({Command.Case Type}= "Break/Fix" or {Command.Case Type}= "Quick Close")
[highlight]and {Command.Assigned Group}in ["Group A", "Group C", "Group F", "Group G"][/highlight]
Then {Command.Assigned Individual}
else {@Null}

Is analyst1 really in one of those groups? Try cutting this out and then also show the Command.Assigned Group for each of the top5.

For your count problem you might try a running total on #Tickets where it evaluates on
{Command.Assigned Individual} = @5thMostFrequentAnalyst
for 5th, rinse and repeat for 4th, 3rd etc.

Too be honest, unless I am completely sure I am locking the groups in a particular way for the life of a report, I shy away from summaries completely and build Running Totals to do the work. I particularly am fond of the "Bucket" approach, just like above, where a condition defines what goes into the bucket --whether it is a count, distinct count, sum or whatever floats your boat.

Scotto the Unwise

 

[medpcjockey]

Scott,
Analyst1 is dynamic. The result is the analyst that shows up most frequently in the field. Analyst2 is the 2nd most frequent field entry, etc. So in each month Analyst1 could be a different person.
I am not sure how to go about using running totals to do the work. The CIO wants the information in a specific format and I haven't been able to get him to budge on the information he wants.

Not sure what you mean by rinse and repeat...