How to get the latest comments to display in the report

[rajua99]

Hello,

In my report  i want to apply filter where i want to get the latest comments only.
For example i have Company Number, Job Number, Cost Type, Sequence Number, Date and Comments.
When i run the report i want to bring the last comments with the latest date where sequence number=1.
How to apply filter to display the latest comment for each cost type. If there are multiple comments for Sequence number 1 with multiple dates then i want to display the max date one only in the report.

Thanks,
Raju

[CognosPaul]

How are the comments inserted? Ideally you should have a flag field indicating the last entered field. When inserting you would run an update that does something like:

update comments
set positionFlag = positionFlag + 1
where companyNumber=@companyNumber and jobNumber=@jobNumber etc...

then do the insert
insert into comments (positionFlag, companyNumber, etc...)
values (1, @companyNumber, etc...)


Now if you don't have the ability to modify the insert process, there is a solution here.

The detail filter should be something like:

[Sequence] = 1
and [Date] = maximum([Date] for [Company Number],[Job Number])

(and whatever else you need to uniquely identify the comment date)

In terms of performance this is not ideal. For something like comments you probably don't have a large volume of data, but if the table was large the query would be incredibly slow.

[Andrei I]

Try Rank:

Code Select Expand


[Sequence] = 1
and rank([Date]for [Company Number],[Job Number])=1


[rajua99]

[Sequence] = 1
and [Date] = maximum([Date] for [Company Number],[Job Number])

(and whatever else you need to uniquely identify the comment date)

In terms of performance this is not ideal. For something like comments you probably don't have a large volume of data, but if the table was large the query would be incredibly slow.

Thanks Paul. The above formula worked for me. Thank you again.