Aggreate function in RS

[yogeswari]

Hi,

could anyone suggest how does aggregate function works??

ProductLine,ProductType,ProductName are direct columns.
Qty is not measure (have changed this to dimension)
Count==>count([Quantity] for [Product name])
RunningTotal==>running-total  ([Quantity] for [Product name])
Aggregate==>aggregate ([Quantity] for [Product name])

ProductLine             || ProductType  || ProductName || Qty || Count || RunningTotal || Aggregate
CampingEquipment    CookingGear    Utensils            10      4            10                   ??
CampingEquipment    CookingGear    Utensils            15      4            25                   ??
CampingEquipment    CookingGear    Utensils            20      4            45                   ??
CampingEquipment    CookingGear    Utensils            35      4            80                   ??

please provide your suggestions/answers since i am fresher i need to know this.

thanks in advance

thanks,
yogeswari.

[Lynn]

Why would you change quantity to a dimension? :o

Aggregate will use the default aggregation method for the measure based on the model aggregation property. It makes no sense to use this function with a dimension.

[yogeswari]

Hi Lynn,

First of all thanks for your response.
In order to understand detail level of data and to understand the function better , i have changed Quantity to dimension. 
Please find the attachment for my questions for better understanding. Here quantity is kept as Measure only.

Please look into this and suggest.  I would consider your help as great to my learning career.

thanks,
yogeswari.

[Lynn]

The aggregate function with the "for" scope definition is telling Cognos to aggregate the quantity measure using the default aggregation method defined in the Framework Manager model across the scope you explicitly define. You'd get the exact same result if you used the "total" function since quantity is a measure that gets summed.

If you had used a measure with a different model-defined aggregation, such as a unit price which typically gets averaged because it makes no sense to add up unit prices, then the aggregate function would do an average rather than a total.

Because you specified the scope as product line using the "for" option, you are getting the total quantity across all products within each product line. If you had specified "for report" you'd get the grand total quantity across all data returned.

As for the count data item, it looks like you want a count of products within each product line. This expression is the way to do that: count ( [Product] for [Product line] ). Not sure it really makes sense to count the quantity measure, so you are probably getting a record count or something else that isn't useful. It seems the "Not Applicable" setting is pushing it to count after aggregation, but from a clarity standpoint I'd say don't count measures.

Hope this helps. Good luck!

[yogeswari]

Hi Lynn,

Thank you very much for your great explainations.
My understandings is, Quantity is a measure which is defined as Total {which is one of the aggregate function} in FM model by default.  So aggregate(quantity)=total(quantity). Similarly Unit Cost is a measure which is defined as Average(which is one of the aggregate function) in FM model by default.  So aggregate(quantity)=average(quantity). Am i correct?

I have some concerns here for which i am attaching the document with better explaination for your clear understandings.  Please give your suggestions as i am would be thankful for this.

Thanks again for your support.

Thanks,
yogeswari.

[Lynn]

Yes, the aggregate function is going to roll up measures based on the default method defined by the model. You can get the same result by using the appropriate function for the measure. Typically you can just set the aggregate property for the measure in Report Studio and it will roll up the measure just fine on its own so you don't need to use aggregate functions in expressions unless you have a specific reason to do so, such as defining a particular scope of aggregation.

As for your attachment, it would help you to understand the Auto Group & Summarize property as well as how to look at the generated SQL. Changing the aggregate function for the measure to None is *not* the way to see detailed data. The appropriate way to see the detailed records would be to set the auto group and summarize property to no. If you look at the generated SQL with the measure aggregate property set to Total you would see it uses a sum with a group by. Change the aggregate function to None and then generate the SQL and you will see that instead of sum with group by it is doing a select distinct. This is the normal behavior for auto group and summarize. If there are no measures it will select distinct. Turn off auto group and summarize off and then you will see a select without distinct and without a sum/group by regardless of what you do with the measure aggregate properties. Your approach is dropping duplicate rows of fact data so a nonsensical number is returned.

Of course Cognos is a BI tool, so generally you want to summarize things rather than dump a big long list of detail data.

Regarding question 2, I'm not sure it is correct to say you've converted a measure to a dimension by changing the aggregate function. A dimension is a specific thing and a measure isn't ever going to be one. Be that as it may, it isn't clear why you want a count of records. If it is an important metric then typically the modeler would create a measure that would allow you to easily sum to get a count of whatever. If you have auto group and summarize turned on, then counting things will be a count of the distinct values returned. To return ALL detail records just to get a count seems a bit odd.