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
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.
Try Rank:
[Sequence] = 1
and rank([Date]for [Company Number],[Job Number])=1
Quote from: CognosPaul on 05 Nov 2019 10:41:57 PM
[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.