Hello Everyone,
Cognos 10.1.1 with relational data (Informix)
Currently I have a query of student athletes that returns the following results. I'm filtering on Activity Type = Athlete
ID Name Activity Activity Type
1 John Jones Baseball Athlete
1 John Jones Football Athlete
1 John Jones Track Athlete
2 Mary Smith Lacrosse Athlete
2 Mary Smith Softball Athlete
What I'm trying to do is only return one record in the query for each athlete, regardless of how many sports they play. This is in a query that will be joined to another query for the final list report.
I'd like the data to look like this:
ID Name Activity Type
1 John Smith Athlete
2 Mary Smith Athlete
Any help is appreciated!
Hi,
Just remove the Activity item from your query. The result should then group exactly as you hope?
MF.
Thanks MFGF. That worked! I wish I knew why it worked :)
Quote from: wwconslt on 22 Apr 2014 02:39:00 PM
Thanks MFGF. That worked! I wish I knew why it worked :)
When a relational query has the "auto group and summarize" property set to Yes, which is the default, then the query will be generated as a select distinct or a select with a group by for measures based on elements present in the layout.
You ask to see the activity per athlete, so you will, and if an athlete has more than one activity, the result would repeat that athlete. That's the way relational dbs work...