Hi gurus,
i have a requirement, giving an example below, please any one help me in that.
RNum status Date
100 onhold 07/07/2012
100 onhold 07/13/2012
101 delivered 07/11/2012
101 delivered 07/12/2012
when am running report the above all are appearing in report.
what i want is ,for the same Rnum n Status i have show only the latest one in the report.
for the above data my output should be like this.
100 onhold 07/13/2012
101 delivered 07/12/2012
..
thanks in adv.
Try the following in the data item of the Date
maximum([Date] for [Rnum],[Status])
As a filter
maximum([Date] for [Rnum],[Status]) = [Date]
Would you mind using descriptive titles for your posts? Thanx.. ;)
No as a data item.
Since you have the Auto group & summariza option to yes (default)
then Cognos Group your data.
If you put a summary function on date then it will return the maximum date for the rnum and status.
Quote from: pricter on 13 Jul 2012 03:30:13 AM
No as a data item.
Since you have the Auto group & summariza option to yes (default)
then Cognos Group your data.
If you put a summary function on date then it will return the maximum date for the rnum and status.
???
The OP wants to have 1 row for each combination of Rnum and status, so a filter is required. I do not see what you are after.
maximum([Date] for [Rnum],[Status]) = [Date]
as a filter will do just that..
Hi blom, am I missing something.
The raw data are the following:
RNum status Date
100 onhold 07/07/2012
100 onhold 07/13/2012
101 delivered 07/11/2012
101 delivered 07/12/2012
If you group this data by RNUM and status and use summary function maximum for date would you get the following or not?
RNUM Status Date
100 onhold 07/13/2012
101 delivered 07/12/2012
My solution is a cross between pricter and blom0344.
I would create a new data item called 'Maximum Date' whereas the expression would be
maximum([Date] for [Rnum],[Status])
Then Add a filter Maximum Date = Date
pricter, you are right concerning the example.. However I imagined it was just an example and the real thing would have other descriptive fields and the filter solution will work regardless of adding other dataitems to the report
Hi,
thanks for giving quick replies. maximum([Date] for [Rnum],[Status]) it helped me out to get that requirement.
once again thanks