Maximum function based on another object

[annadanna]

Hi,

I have the following objects:
Title
Employee ID
Effective Date (of changes to title)

I'd like to show the Employee ID and the Title however I'd like the title to be the maximum (IE latest) title. I don't want to set a detail filter since there are other items in the query which use a different effective date so I'd like to write this into the data item expression. If it was a filter I would just put in effective date = maximum (effective data for employee id) but like I said this won't work here.

Thanks for any help you can offer!!

[Lynn]

Could you create a query that gives you each employee and his/her latest title and then join that query to another query that provides the rest of the information? That will involve local processing which isn't always a good idea in terms of performance, so be aware of that caveat.

[annadanna]

I'm actually doing that now but its causing issues with other items which need to be in the report. It would solve a lot of problems if I could do everything in one query.

[Lynn]

Ok, how about this....create a data item with maximum ([EffDate] for [EmpID]) and call is MaxEffDate. Create another data item that compares this date with the effective date on the row and returns either the title or null. You'd need to set the aggregate function on that to maximum I think.

You won't get the latest title on all the old rows, but you'd at least have the latest title derived somewhere in the query.

case
when [EffDate] = [MaxEffDate] then [Title]
else null
end

[annadanna]

Unfortunately that won't work either. I would need it to show up in all rows. Sorry I should have been more specific.

[Lynn]

If you set the aggregate function on that column to maximum then it should come out in your layout on all rows even if it isn't on every row in the detail assuming the auto group and summarize for the query is set to yes. If you have a layout with just employee ID and title I think you'd see that. Then use a master-detail relationship to another container that brings in the additional details.

[annadanna]

This worked - thank you!

[Lynn]

Great! Glad it worked out.

Your screen name makes me think of Roseanne Roseannadanna  ;D