Showing most recent record in a group header 1

[dianemarie]

Hello, my report is grouped by list id, and I show the list name in the group header. The data is from one ordhist table. We have a list lookup table but I can't use it because we have some data from old systems where the list information is in the ordhist table only. The list name changes over time, although the list id stays the same. Example:

OrderDate ListID ListName
4/1/10 123 CompanyCo Inc.
4/1/09 123 Company ABC, Inc.
4/1/08 123 Company ABC, Inc.

I have tried Min, Max, First, and sorting descending by order date on the group details to get the most recent list name to show in the header. Nothing worked consistently. I'm trying to do a sub query to create a new field called ListGroupHeaderName (or whatever) to use for the group header. Having trouble.... I want the results to look like this:

OrderDate ListId ListName ListGroupHeaderName
4/1/10 123 CompanyCo Inc. CompanyCo Inc.
4/1/09 123 Company ABC, Inc. CompanyCo Inc.
4/1/08 123 Company ABC, Inc. CompanyCo Inc.

I'm grouping by list ID in the report by the way, not the SQL query. The query just pulls the detail records by a parameter called @CustomerID. Any help would be appreciated. Thank you.

 

[Crowley16]

In this case, I would normally modify the query so that it would bring back just the most recent list name as it would be better for performance, probably.

However in the report itself, using the first and order by should get you the correct result.

What are the problems you are getting when trying this?

--------------------
Procrastinate Now!

 

[dianemarie]

OK, your confirmation that FIRST should have worked when sorted descending by most recent date made me try it again. I had tried that previously but it wasn't consistently pulling the most recent list name. After mulling it over a bit I decided to put another hidden detail row in the report with the list name. I had the list name on the group header only as it is unnecessary to repeat in the detail. That seems to now be working. I appreciate you pointing me back in the right direction.

 

[Crowley16]

glad to be of help.

without testing I can't be sure why it didn't work the first time, however, I suspect that since you didn't have the list name in the details section for each group previously, the system would have brought the first list in the whole dataset instead of the first list within that group.

but that's just me guessing idly...

--------------------
Procrastinate Now!