'Ranking' records on a report

R

Robert_L_Ross

Ok, here goes.

I want to be able to highlight specific records (let's say the top 3) on a
grouped report.

For example, I have a report listing the Unit, Staff Member Name,
Transaction and # of incidents. The report is grouped by Unit first, then
Staff Member Name. The detail contains the Staff Member Name (it doesn't
show the duplicates, only the 1st), Transaction and the # of incidents. I
want the top 3 incidents (by number, not by list order) to highlight (bold,
italic, whatever).

I can get the highest and lowest to highlight already - I placed a field in
the footer of teh Staff Member group named MaxIncidents as follows:
=max([Incidents])
I then use conditional formatting on both the Transaction and the # of
Incidents fields to see if the Incidents field is = to the MaxIncidents
field. If true, it highlights in bold.

I have the same for minimum by using a MinIncidents field.

Now, the end users want to display the top 3 incident values and ditch the
min. How can I do something like the Rank command in Excel to find the top 3
incident values? Keep in mind I can sort decending on the incidents field,
as the sorting needs to be by transaction.

Have I confused you? If so, sorry, but I don't know how else to describe it.

THX for taking the time to read (and hopefully reply!).
 
J

jl5000

In a similar way you are changing the formatting for the highest and lowest,
but in this case create a counter in your report that resets every time you
start a group for a Staff member, in the onprint event change the formatting
until your counter reaches 3, then change the format for the rest, and start
over in the next group,
 
R

Robert_L_Ross

That sounds easy enough, but I have no idea how to have a counter reset when
the group changes.

jl5000 said:
In a similar way you are changing the formatting for the highest and lowest,
but in this case create a counter in your report that resets every time you
start a group for a Staff member, in the onprint event change the formatting
until your counter reaches 3, then change the format for the rest, and start
over in the next group,

--
jl5000
<a href="http://www.joshdev.com"></a>


Robert_L_Ross said:
Ok, here goes.

I want to be able to highlight specific records (let's say the top 3) on a
grouped report.

For example, I have a report listing the Unit, Staff Member Name,
Transaction and # of incidents. The report is grouped by Unit first, then
Staff Member Name. The detail contains the Staff Member Name (it doesn't
show the duplicates, only the 1st), Transaction and the # of incidents. I
want the top 3 incidents (by number, not by list order) to highlight (bold,
italic, whatever).

I can get the highest and lowest to highlight already - I placed a field in
the footer of teh Staff Member group named MaxIncidents as follows:
=max([Incidents])
I then use conditional formatting on both the Transaction and the # of
Incidents fields to see if the Incidents field is = to the MaxIncidents
field. If true, it highlights in bold.

I have the same for minimum by using a MinIncidents field.

Now, the end users want to display the top 3 incident values and ditch the
min. How can I do something like the Rank command in Excel to find the top 3
incident values? Keep in mind I can sort decending on the incidents field,
as the sorting needs to be by transaction.

Have I confused you? If so, sorry, but I don't know how else to describe it.

THX for taking the time to read (and hopefully reply!).
Click to expand...
Click to expand...
 
Top