COGNOiSe.com - The IBM Cognos Community

Hello, I am really new to Cognos, and could really use some help. I need to create a report where by I calculate  the average duration of a task, then use that average to count the number of tasks in groups such as 0 - 10% over, 11-25% over, 25-50% over, etc.

I am limited to a strictly relational model. I have tried all sorts of query calculations, and while I can successfully calculate the average for all tasks, I can not then use that average to count the number of tasks in the above buckets.

So, for example, if I determine that the average for my group of tasks is 7.69 days, how would I then create a report that would put the following into the appropriate buckets?

Task     Duration
A          3.5
B          8
C          4.5
D         11
E          2

and so on.

Any help would be most appreciated.

Thanks - B

Check out the use of the 'FOR' context. This enables you to perform aggregates on grouped sets of data

Blom,

Thanks for the tip. I have looked at the "Tips" section for "For" in Report studio and find myself still confused. I guess these are for experienced sql folks.

aggregate_function ( expression FOR expression { , expression } )
Use with summary expressions to define the scope of the aggregation in the query.

This is a bit greek to me. If I am understanding this right, the aggregate_function would be average, but then, what are the summary expressions it asks for?

Again, working with duration: average(duration FOR ????)

Thanks for your help AND patience!

B

here's an example of a Count expression :

count (distinct [ACCOUNTNMBR] for [REASONDEFSCRIPTION])

Quote from: balfond on 08 Apr 2009 11:37:41 AM
Blom,

Thanks for the tip. I have looked at the "Tips" section for "For" in Report studio and find myself still confused. I guess these are for experienced sql folks.

aggregate_function ( expression FOR expression { , expression } )
Use with summary expressions to define the scope of the aggregation in the query.

This is a bit greek to me. If I am understanding this right, the aggregate_function would be average, but then, what are the summary expressions it asks for?

Again, working with duration: average(duration FOR ????)

Thanks for your help AND patience!

B

The expression that the tips point at is the dataitem that holds the grouped set.
Unfortunately the boys at Cognos simply nowhere provide examples in their help/documentation, which makes the tips sometimes hard to follow   :-\

You both saved my bacon. Thank you!