How to display the maximum of multiple rows in Report Studio

[RickHenderson]

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.

[Lynn]

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.

Code Select Expand


[Term] = maximum ( [Term] for [Student] )


[RickHenderson]

Thanks Lynn, I'll give it a try. I'm not as familiar using the FOR clause as I should be perhaps.

[hespora]

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