Finding maximum value and displaying related field 2

[ramc2000]

Hi all, I have a report on our employee table grouped by department_id where I'm showing employee_id, first, last name and productivity. I need to display at every group footer the highest productivity and the first and last name of the corresponding employee. Displaying the highest productivity is just a matter of using a max summary field, but I'm not sure about the names... does anyone know how to do this?? Thanks.

 

[lbass]

Try sorting on the productivity field in ascending order, and then drag the desired fields into the group footer and suppress the details section.

-LB

 

[ramc2000]

Unfortunately the report users don't want to see the records sorted on the productivity field... and I can't suppress the details section either.... perhaps there's a different approach???

 

[lyanch]

You could use a formula to set a variable.

something along the lines of

@display
stringvar display;

if id = max({table.field},{table.field} then
display := {table.field2}


You would need to initalize the variale in the report header.. and create another formula to display it.

Lisa

 

[lbass]

You could use the running total editor and select {@name} as your field (where @name = {firstname}+" "+{lastname}), choose "Nth Most Frequent" and "1" as the summary, evaluate based on a formula:

productivity = maximum({productivity},{yourgroupfield})

and reset on change of Group {yourgroupfield}.

Place the running total in your group footer next to your summary of productivity (insert maximum on {productivity}). Or you could concatenate the running total and the productivity value:

{#nameformax}+" "+totext(maximum({productivity},{yourgroupfield}))

-LB

 

[ramc2000]

These set of formulas seem to be right on the spot.... I still have a problem tough, I'm getting a 'the summary field could not be created' message, I think the reason might be my productivity field is a formula field using a SUM function to calculate the value.... I tried it replacing it temporarily with a numeric field in my table (just to check if that's the reason for the error message and I confirmed it). So now the problem comes down to the use of summary functions.... the thread is moving off the original issue but thanks anyway if you have an answer for this.

 

[lbass]

Maybe the easiest solution would be to create a subreport where you can do a sort on {productivity} using TopN, and then just drag the fields into the group footer and suppress the details. Then display the subreport in your main report group footer. I'm not a pro at subreports, but this might work.

-LB