I want to know about the aggregations when you create the measure. When you create a measure and mention aggregation as average ,does it roll up with average of all values. When you mention aggregation as sum does it so the sum when it rolls up.
hartford27 
(BOB member since 2006-11-22)
Hi,
Have a look at these 2 old posts:
[list]- post 1
Marek Chladny 
(BOB member since 2003-11-27)
Thanks for the posts. I understood the basics of aggregation and projection. Now still Cant I use the average as the projection if the measure rolls up even if I didnot use the average in the aggregation.
How does the count in aggregation and sum in projection work. At the report level if this measure rolls up it is supposed to do the count but instead doesnot it does the sum.
hartford27 (BOB member since 2006-11-22)
You can do a projected average only if you can guarantee that the sample sizes are consistent. For example, if you have exactly the same number of orders every month you can project the average order dollars. If you have a different number of orders each month (most likely) then the math does not work, if you project from month to quarter (for example) then the average will be an average of the averages, and not a true and mathematically correct weighted average.
When you do a count at the database level you sum at the report level. It does not make sense to count a count. Suppose you counted orders by month and go this:
January 25
February 25
March 25Now at the quarter level, what number do you want to see? 1, 3, or 75? If you do a distinct count of 25, 25, 25 you get 1. If you do a full count of 25, 25, 25, you get 3. Neither of those are correct. Thus, you project a count with a sum in order to get a total count of 75 orders for the quarter.
This post by Steve Krandel shows the generally accepted mapping from SQL aggregate to report projection function.
Dave Rathbun (BOB member since 2002-06-06)