Hi everyone,
I'm writing reports in Cognos Report Studio to pull relational information from our institution's Banner database. When dealing with student records, there are multiple records for a student with different term values (year and month of when a term starts).
So far my report is showing all the students I'm looking for, but there are multiple rows for each student. I only want the row for a particular student that has the maximum term value.
How can I accomplish this? Using the Maximum summarize function on the report page won't give me what I want, as it just puts the Maximum value on the summary row at the bottom of the table.
Any help appreciated,
Rick.
Quote from: RickHenderson on 07 Mar 2018 09:28:13 AM
Hi everyone,
I'm writing reports in Cognos Report Studio to pull relational information from our institution's Banner database. When dealing with student records, there are multiple records for a student with different term values (year and month of when a term starts).
So far my report is showing all the students I'm looking for, but there are multiple rows for each student. I only want the row for a particular student that has the maximum term value.
How can I accomplish this? Using the Maximum summarize function on the report page won't give me what I want, as it just puts the Maximum value on the summary row at the bottom of the table.
Any help appreciated,
Rick.
It sounds like you want to add a filter to your query so that you only get one row back per student corresponding to the maximum term for each student.
Try a filter with syntax similar to below and set it to after auto aggregation.
[Term] = maximum ( [Term] for [Student] )
Thanks Lynn, I'll give it a try. I'm not as familiar using the FOR clause as I should be perhaps.
Quote from: RickHenderson on 07 Mar 2018 12:05:13 PM
Thanks Lynn, I'll give it a try. I'm not as familiar using the FOR clause as I should be perhaps.
I found this an excellent write-up: http://public.dhe.ibm.com/software/dw/dm/cognos/reporting/advanced_report_design/using_the_at_and_for_options_with_relational_summary_functions.pdf