Trouble eliminating multiple records

[powell99]

Hi,

I'm relatively new to Cognos Report Studio so I may be overthinking this but I am struggling with eliminating additional records in a report.
Here is the scenario: I work for a university and I am creating a report to pull a group of people based on their status with the University. I have a report with 5 prompts, none are required.

Prompt1   Prompt2   Prompt3   Prompt4   Prompt5
Report output is a simple list.
One of the prompt selections is Degree College and should return anyone who has a degree from the college chosen.  The problem comes into play when a person has multiple degrees.  If any prompt other than the Degree College is selected, In this case we only want the first degree to display on the report.  I created a data item which numbers the degrees in order by year received.  I then tried to do a filter based on the year using minimum and I still get a record for each degree which can appear to be duplicates to the end users.
What I'm trying to accomplish is to say when the Degree College prompt is null (i.e. nothing is selected) then run the report but only include the lowest (first degree) the person received.  Here's is the syntax I'm using:
Case when
([Degree College] not in (?p_DegreeCollege?))
Then
([Degree Year] = minimum[Degree Year] for GraduateID]))
End

The statement validates if I don't include the for statement but I get a parsing error when I include the for statement. In either case I still have duplicate names when I run the report. I understand that they are not truly duplicates because there is a record for each degree but how can I get only one record to display?  Any help is appreciated.

[MFGF]

Hi,

I think instead of trying to do a minimum(), simply add a filter which returns the first degree (ie [data item which numbers the degrees] = 1). You would need to set this as an "after aggregation" filter.

Cheers!

MF.

[powell99]

Thank you for your response and I have done that and it works but the problem is we only want the minimum degree when no degree is chosen.

For example if The College of Engineering is selected from the degree prompt and the person has a Bachelors, Masters, and PHd from the college of engineering then all three records should show in the report output, if the person has one degree from engineering then 1 record would be on the report, same with 2 degrees.

However, if no prompt selections are made we only want to see the person first degree regardless of where the degree is from.  Hopefully that clears it up a bit. You are correct in that it needs to be a filter but I'm not sure of the syntax to accomplish this task.

[MFGF]

How about

([data item which numbers the degrees] = 1 and ?your prompt parameter? is null) or (?your prompt parameter? is not null)

Does that give you what you need?

MF.