AGGREGATESUM (Dimension, Member)

Description:

This function calculates an aggregate value for the specified member in the specified dimension. The hierarchy for the specified dimension defines the aggregation and the weights are appropriately applied. This function forces the aggregation of the Hierarchy by a formula that forces the hierarchy aggregation engine to operate accurately.

Syntax: AGGREGATESUM (dimension, member)

• Dimension : Enter the name of the dimension from the current database from where you want to access the members to be aggregated.
• Member : Enter the name of the member for which you want to force the aggregation via the AggregateSum formula.

Remarks

• The name of the dimension and the member must be a valid name and should exist in the database.
• The member for which this function has to be written should be an aggregate member for the function to be appropriately applied.
• Both arguments are compulsory  (i.e not Optional).

Example

=AGGREGATESUM ("Region","Asia")	- from the dimension "Region", and for the member "Asia" this function example, adds up the values based on the hierarchy for Asia.
=AGGREGATESUM ("Version","TotalSales")	- from the dimension "Version" and for the member "TotalSales" this function example, adds up the values based on the hierarchy for TotalSales.

 

 

Let us take the example from the Avon Trading Company database and in it, the cube called Margin. In this database the AggregateSum function can be used to force the aggregations where the totals don't quite add up correctly. The database is designed in a way that the aggregations don't make up correctly without the AggregateSum function. By using this function, the hierarchies are being forced in one direction to aggregate the values correctly. In this database, without the AggregateSum function, the totals don't add-up right until the AggregateSum forces the hierarchy of the dimension to function properly. The AggregateSum function is written for the aggregate member for which the hierarchy has to be forced.

 



The screen above shows a formula for the AGGREGATESUM function. The formula forces the aggregation for the aggregate member Variance which belongs to the dimension "Version". The function takes these two parameters, which are the name of the dimension whose members are accessed, and the name of the member, which is an aggregate member and for which member the formula has to force the aggregation based upon the hierarchy for that dimension and for that member. From the Margin cube above, the function accesses the dimension and the member referenced in the formula, which are Version and Variance respectively. After this the AGGREGATESUM function reads the hierarchy of the dimension and calculates the aggregations based on the hierarchy designed. Therefore, in instances when there is anything interfering with the hierarchies, that make the aggregate compute otherwise than how it is supposed to, in this case the AGGREGATESUM function controls that aggregate with a formula, and forces the hierarchy to work and calculate the sum as designed. The AGGREGATESUM function doesn't return any value, it is the hierarchy for that dimension that returns the value.