Cognos Analytics

Hi,

Imagine you have a report that brings in the following data set for Product Line, Year and Quantity.

This is a summarized data set - the underlying rows in the database correspond to individual product sales on specific dates, so the Quantity values you see displayed in the list are summarized and aggregated automatically by Cognos. This aggregation is driven by the Detail Aggregation property of the Quantity item. In this case, the property is set to Total, so you are seeing Quantity totals for each combination of Product Line and Year. At this point, the Summary Aggregation property has no bearing on what you see.

You now decide that within your list you want to see aggregated values for each Product Line as well as for each combination of Product Line and Year. To achieve this, you would first group the Product Line values by selecting the Product Line column, and using the Group button on the toolbar

And at this stage, you would have a grouped list with no summary aggregations 

Next you would select the Quantity column, and use the Summarize button on the toolbar to choose how to aggregate the Quantity values for each Product Line grouping (and also for the list as a whole):

Now you have a choice of which summary aggregation type to use. Doing things this way (via the Summarize button) will add your chosen summaries in footers at the end of each Product Line and at the end of the list. In this case, I chose Total.

It also creates a new summarized query item to display these summaries - you can see this if you look at the query.

At this point, the Summary Aggregation property of the Quantity item still has no bearing on what you see. So, when does it make a difference?

If you decide to add the original Quantity item (rather than the new Total(Quantity) item) into your footings, then this is where the Summary Aggregation property comes in. If I set the Summary Aggregation property to Average...

...then I unlock the structure of the report using the padlock button on the toolbar...

...then I drag the Quantity item into one of my Product Line footers...

...the Quantity values displayed are averages - driven by the Summary Aggregation property setting of the Quantity item.

(Not forgetting to re-lock the structure of my report) if I then change the Summary Aggregation property to a different choice, the Quantity values in the footers change to reflect that choice.

So, to summarize (pun definitely intended!), the Summary Aggregation property of an item controls how that item is summarized in group footers (if it is used directly in footers).

Cheers!
MF.

Thank you Mark,

I've tried this on my end and indeed the summary aggregation for list will take effect only if the original data item (in your case the quantity) is pulled into the summary section (footer). 

I will go on and test for other objects (i.e., crosstab) and see how it behaves.

Best Regards,

WB

Hi,

Imagine you have a report that brings in the following data set for Product Line, Year and Quantity.

This is a summarized data set - the underlying rows in the database correspond to individual product sales on specific dates, so the Quantity values you see displayed in the list are summarized and aggregated automatically by Cognos. This aggregation is driven by the Detail Aggregation property of the Quantity item. In this case, the property is set to Total, so you are seeing Quantity totals for each combination of Product Line and Year. At this point, the Summary Aggregation property has no bearing on what you see.

You now decide that within your list you want to see aggregated values for each Product Line as well as for each combination of Product Line and Year. To achieve this, you would first group the Product Line values by selecting the Product Line column, and using the Group button on the toolbar

And at this stage, you would have a grouped list with no summary aggregations 

Next you would select the Quantity column, and use the Summarize button on the toolbar to choose how to aggregate the Quantity values for each Product Line grouping (and also for the list as a whole):

Now you have a choice of which summary aggregation type to use. Doing things this way (via the Summarize button) will add your chosen summaries in footers at the end of each Product Line and at the end of the list. In this case, I chose Total.

It also creates a new summarized query item to display these summaries - you can see this if you look at the query.

At this point, the Summary Aggregation property of the Quantity item still has no bearing on what you see. So, when does it make a difference?

If you decide to add the original Quantity item (rather than the new Total(Quantity) item) into your footings, then this is where the Summary Aggregation property comes in. If I set the Summary Aggregation property to Average...

...then I unlock the structure of the report using the padlock button on the toolbar...

...then I drag the Quantity item into one of my Product Line footers...

...the Quantity values displayed are averages - driven by the Summary Aggregation property setting of the Quantity item.

(Not forgetting to re-lock the structure of my report) if I then change the Summary Aggregation property to a different choice, the Quantity values in the footers change to reflect that choice.

So, to summarize (pun definitely intended!), the Summary Aggregation property of an item controls how that item is summarized in group footers (if it is used directly in footers).

Cheers!
MF.

My firm PMsquare has a blog post on this very subject, much of which MF has already indicated in his reply.  We also have a short video on this coming soon on our PMsquare YouTube channel

... stay tuned!

Thank you Mark,

I've tried this on my end and indeed the summary aggregation for list will take effect only if the original data item (in your case the quantity) is pulled into the summary section (footer). 

I will go on and test for other objects (i.e., crosstab) and see how it behaves.

Best Regards,

WB

Hi,

Imagine you have a report that brings in the following data set for Product Line, Year and Quantity.

This is a summarized data set - the underlying rows in the database correspond to individual product sales on specific dates, so the Quantity values you see displayed in the list are summarized and aggregated automatically by Cognos. This aggregation is driven by the Detail Aggregation property of the Quantity item. In this case, the property is set to Total, so you are seeing Quantity totals for each combination of Product Line and Year. At this point, the Summary Aggregation property has no bearing on what you see.

You now decide that within your list you want to see aggregated values for each Product Line as well as for each combination of Product Line and Year. To achieve this, you would first group the Product Line values by selecting the Product Line column, and using the Group button on the toolbar

And at this stage, you would have a grouped list with no summary aggregations 

Next you would select the Quantity column, and use the Summarize button on the toolbar to choose how to aggregate the Quantity values for each Product Line grouping (and also for the list as a whole):

Now you have a choice of which summary aggregation type to use. Doing things this way (via the Summarize button) will add your chosen summaries in footers at the end of each Product Line and at the end of the list. In this case, I chose Total.

It also creates a new summarized query item to display these summaries - you can see this if you look at the query.

At this point, the Summary Aggregation property of the Quantity item still has no bearing on what you see. So, when does it make a difference?

If you decide to add the original Quantity item (rather than the new Total(Quantity) item) into your footings, then this is where the Summary Aggregation property comes in. If I set the Summary Aggregation property to Average...

...then I unlock the structure of the report using the padlock button on the toolbar...

...then I drag the Quantity item into one of my Product Line footers...

...the Quantity values displayed are averages - driven by the Summary Aggregation property setting of the Quantity item.

(Not forgetting to re-lock the structure of my report) if I then change the Summary Aggregation property to a different choice, the Quantity values in the footers change to reflect that choice.

So, to summarize (pun definitely intended!), the Summary Aggregation property of an item controls how that item is summarized in group footers (if it is used directly in footers).

Cheers!
MF.

Thank you MK. 

I will have a read on this now. 

looking forward to the youtube video soon :)

regards,

Wei bin

My firm PMsquare has a blog post on this very subject, much of which MF has already indicated in his reply.  We also have a short video on this coming soon on our PMsquare YouTube channel

... stay tuned!

Thank you Mark,

I've tried this on my end and indeed the summary aggregation for list will take effect only if the original data item (in your case the quantity) is pulled into the summary section (footer). 

I will go on and test for other objects (i.e., crosstab) and see how it behaves.

Best Regards,

WB

Hi,

Imagine you have a report that brings in the following data set for Product Line, Year and Quantity.

This is a summarized data set - the underlying rows in the database correspond to individual product sales on specific dates, so the Quantity values you see displayed in the list are summarized and aggregated automatically by Cognos. This aggregation is driven by the Detail Aggregation property of the Quantity item. In this case, the property is set to Total, so you are seeing Quantity totals for each combination of Product Line and Year. At this point, the Summary Aggregation property has no bearing on what you see.

You now decide that within your list you want to see aggregated values for each Product Line as well as for each combination of Product Line and Year. To achieve this, you would first group the Product Line values by selecting the Product Line column, and using the Group button on the toolbar

And at this stage, you would have a grouped list with no summary aggregations 

Next you would select the Quantity column, and use the Summarize button on the toolbar to choose how to aggregate the Quantity values for each Product Line grouping (and also for the list as a whole):

Now you have a choice of which summary aggregation type to use. Doing things this way (via the Summarize button) will add your chosen summaries in footers at the end of each Product Line and at the end of the list. In this case, I chose Total.

It also creates a new summarized query item to display these summaries - you can see this if you look at the query.

At this point, the Summary Aggregation property of the Quantity item still has no bearing on what you see. So, when does it make a difference?

If you decide to add the original Quantity item (rather than the new Total(Quantity) item) into your footings, then this is where the Summary Aggregation property comes in. If I set the Summary Aggregation property to Average...

...then I unlock the structure of the report using the padlock button on the toolbar...

...then I drag the Quantity item into one of my Product Line footers...

...the Quantity values displayed are averages - driven by the Summary Aggregation property setting of the Quantity item.

(Not forgetting to re-lock the structure of my report) if I then change the Summary Aggregation property to a different choice, the Quantity values in the footers change to reflect that choice.

So, to summarize (pun definitely intended!), the Summary Aggregation property of an item controls how that item is summarized in group footers (if it is used directly in footers).

Cheers!
MF.