'Ranking' records on a report

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!).
 
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,
 
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...
 
Back
Top