Urgent help please on displaying row with the most recent date in cognos report

[abhilash502]

Hi,
I have a list report with a data items named case, child number and date.

The output which I am getting from the List report is:

Case                             Child Number        Date
00123                               1                       09/02/2013
00123                                1                     08/01/2013
00245                                2                      08/30/2013


I don't want the rows to be repeated for the case numbers..It should only show the row with the recent date like:

Case           child Number             Date
00123             1                           09/02/2013
00245              2                           08/30/2013

I tried all the possibilities like putting the expression in the date item like:     maximum(date) for case number.
This gave me the below output:
Case                             Child Number        Date
00123                               1                       09/02/2013
00123                                1                     09/02/2013
00245                                2                      08/30/2013

I dont want the rows to be repeated....Is there anything that I am doing wrong...or is there some other alternate solution to get this accomplished.

Please help me out.

Regards,
AB

[blom0344]

You need to define a data item  'x' like maximum([date] for [case])  and use this in an after auto aggregation filter  like   x = [date]

[abhilash502]

The problem here is..

In the report we have a display criteria box where the users can select what they want to view in the report.. So, there is every possibility that users might not opt to display case number in the report.
So in such cases, when I put maximum ([date] for [case number]).., the report is giving incorrect results like it is trying to find the maximum date for the entire report and displaying it in each and every row.

Example:
MPI no      date
1              06/01/2014
2              06/01/2014
3               06/01/2014
3              06/01/2014


Please Any suggestions?

[Lynn]

You could build on Blom's suggestion by adding to the filter so it is only applied when the case number is included in the selection criteria.

Something like:

?YourParameter? = 'Whatever'
and
maximum([date] for [case number]) ... etc.

[reinknecht]

I have the same need to only pull a record for the most recent date. When I try to validate the data item :
maximum ([PAY_DATE]for[CUSIP12]) I receive the following error: 'The server returned an unrecognizable validation response'. The report however ran and returned the results I wanted. Does anyone have any idea why the validation would have failed even thought the report works?
Thanks

[kc9400]

http://www-01.ibm.com/support/docview.wss?uid=swg21342598

rein, does that help you at all?