Hi All,
I am new to cognos and I have to prepare a report where i should count the people who are at different ages
for ex: I need the count of the people who are age 5 and also whose ag is between 6-8
Age5, Age 6-8,
hi there,
you could group the column and then either set the aggregation to count or you can auto summarize it to get the totals in footers
This is typically solved by defining 'buckets' using case statements:
CASE WHEN
[AGE] = 5 THEN '5'
WHEN
[AGE] BETWEEN 6 AND 8 THEN '6-8'
WHEN
....
ELSE
....
END
in case the dataitem AGE stores an integer for age. When you need to calculate the age using a birthdate, then extend the logic.
Lining up the buckets takes some effort, cause Cognos does not allow for sorting on an individual basis. You may need to define an additional calculated dataitem that allows for a proper sort:
CASE WHEN
[AGE] = 5 THEN '01'
WHEN
[AGE] BETWEEN 6 AND 8 THEN '02'
WHEN
....
ELSE
....
END
We have some HR warehouses with all kinds of buckets. blom0344's approach is exactly how we handled this, and we created calculated attributes in our models to handle the grouping and sorting for reuse across reports. The approach works quite well for us.