Difference between Aggregate and Rollup Aggregate

[nayitian]

Hi,

I have been searching some similar posts but cannot find clear answers.
In RS, what are the differences between using Aggregate funcations and Rollup Aggregate functions?
Each has Automatic, Summarized and Calculated, I did not see any difference when I use either of the functions.

Really appreciate if you can help explain. Thank you very much.

[blom0344]

aggregate influences the type of aggregate function in the SQL prepared. The rollup influences the way data is handled when displayed at a higher level than brought by the query definition.  If you bring in a dataset aggregated to month level with year present, then displaying data within a report at year level, means rolling it up from month to year

[nayitian]

Hi, thank you very much for explaining. But I am not fully caugth up,
if I have a data set
---------------------------------------------------------------------------------------------------
ProvinceCode      CityCode     Measure(Aggreation:total/Rollup aggregation: average)     
     P1                      C1                      100
     P1                      C2                      200
---------------------------------------------------------------------------------------------------
I was expecting my report looking like this
---------------------------------------------------------------------------------------------------
ProviceCode       Measure
    P1                    150
---------------------------------------------------------------------------------------------------

But the measure still shows 300 even I explicitly set Rollup function to average.

[Lynn]

The rollup aggregate will kick in on the summary line of a report, such as a list footer or a group footer. It will perform the rollup aggregation based on the displayed detail rows so if the report shows Province and city code then the summary line would show 150 as the summarized measure value ... 300 / 2 rows displayed = 150.

Removing the city code from the report layout will not cause roll up aggregation. It will use the regular aggregate which is total in your case. You get one row with a regular rollup of 300 for P1 and then it would show the summary row also as 300 because the summarize happens after the regular aggregation ... 300 / 1 row displayed = 300.

If you create a summary row and then compare the report SQL for native vs. Cognos you can see that the averaging is happening against the total at the level of detail in the report.

Clear as mud?