## 07 February, 2012

### Custom multiply aggregation function in AS2008

In a recent project of mine i needed a custom aggregation function that needed to multiply som yield numbers of a specific measure in a given dimension.

My yield numbers are actually stored as a number between 0 and 100. And not between 0 and 1 which i guess are more common when expressed as percentage.
The reason for this is some limitations in our 3rd party reporting charts.
Any way this is how i made the custom aggregation function that will work with both variants:

Create a c# class library.
`C:\Program Files\Microsoft Analysis Services\AS OLEDB\10\msmgdsrv.dll`
Add a class named ASUtils with the following content:

```namespace ASUtils
{
using System;

public class Multiplication
{
[SafeToPrepare(true)]
public static Nullable<double> Multiply(Set inputSet, Expression inputExpression, double divider, bool excludeZeroValues)
{
Nullable<double> num = null;
bool first = true;
foreach (Tuple tuple in inputSet.Tuples)
{
MDXValue mdxValue = inputExpression.Calculate(tuple);
bool isEmpty = (mdxValue.ToChar() == 0x00);
if (!isEmpty)
{
double value = mdxValue.ToDouble();
if (value != 0 || (value == 0 && excludeZeroValues == false))
{
if (first == true)
{
num = value / divider;
first = false;
}
else
{
num *= value / divider;
}
}
}
}

return num * divider;
}
}
}
```
I did not find any built in functions that could check if a value was empty or not, but this seems to work:
`bool isEmpty = (mdxValue.ToChar() == 0x00);`
Any other solutions out there ?

Build the project, then in your AS2008 cube project, add a reference to the new class library.
In your cube, under calculations. Add a new calculated member named YieldM with this code:
`ASUtils.Multiply([Process].[ProcessNoGroup].Children,[Measures].[Yield], 100.0, false)`
[Process] = Dimension
[ProcessNoGroup] = Hierarchy
[Measures].[Yield] = Measure containg numbers between 0 and 100.

If the measure contains numbers beteen 0 and 1, then you can change the multiplyer to 1 like:
`ASUtils.Multiply([Process].[ProcessNoGroup].Children,[Measures].[Yield], 1.0, false)`
Empty values are always eliminated from the multiply.
You can also exclude 0 (zero) values by changing the last parameter to true.

So if we now look at the yield measure over time and process, we have this test data:
 Time Dimension Process Dimension Yield W01 Process1 94.88 W01 Process2 96.42 W01 Process3 95.03 W02 Process1 98.86 W02 Process2 96.42 W02 Process3 94.03

When we aggregate this by week and look at our calculated YieldM measure:
 Time Dimension YieldM Formula W01 86.93 (94.88*96.42*95.03) W01 89.63 (98.86*96.42*94.03)

And voila, our custom aggregation is working as it should.