Customize PivotGridAggregateField Calculation

[Lawrence]

Hi,

I have a pivot grid as per picture below. For Total Ratio row, need to display a value with custom calculation. Following is my calculation format.

A = SumOfQty Won
B = SumOfQty Pending
C = SumOfQty Quoted
D = SumOfQty Lost

Format Ratio (%) = [A / (A+B+C+D)] * 100 

How to do it using code behind C#? I put my sample aspx code here.

<telerik:RadPivotGrid ID="RadPivotGrid1" runat="server" DataSourceID="SqlDataSource1" OnCellDataBound="RadPivotGrid1_CellDataBound" OnItemNeedCalculation="RadPivotGrid1_ItemNeedCalculation"
                                AggregatesLevel="1" AggregatesPosition="Rows" EmptyValue="0" Skin="Bootstrap" OnPivotGridCellExporting="RadPivotGrid1_PivotGridCellExporting">
                                <Fields>
                                    <telerik:PivotGridRowField DataField="SummaryStatus" Caption="Status">
                                    </telerik:PivotGridRowField>
                                    <telerik:PivotGridColumnField DataField="APP_INIT_DATE" Caption="Month">
                                    </telerik:PivotGridColumnField>
                                    <telerik:PivotGridAggregateField DataField="Quantity" Aggregate="Sum" DataFormatString="{0}" Caption="Quantity">
                                        <TotalFormat Level="0" Axis="Columns"></TotalFormat>
                                    </telerik:PivotGridAggregateField>
                                    <telerik:PivotGridAggregateField DataField="Price" Aggregate="Sum" DataFormatString="{0:n2}" Caption="Price (RM)">
                                        <TotalFormat Level="1" Axis="Columns"></TotalFormat>
                                    </telerik:PivotGridAggregateField>
                                    <telerik:PivotGridAggregateField DataField="Ratio" CalculationDataFields="Quantity" CalculationExpression="{0}/100"
                                        DataFormatString="{0:n2}" CellStyle-Font-Italic="true">
                                    </telerik:PivotGridAggregateField>
                                </Fields>
                                <TotalsSettings ColumnGrandTotalsPosition="First" ColumnsSubTotalsPosition="First" RowGrandTotalsPosition="First"
                                    RowsSubTotalsPosition="First" GrandTotalsVisibility="RowsAndColumns"/>
                            </telerik:RadPivotGrid>

 

Thank you.

[Lawrence]

I found the way to present the data. I'm doing a counting from the database and calling it into the table. For the calculation was done within the code behind.

Aspx:

...
<telerik:PivotGridAggregateField DataField="WRatio" Aggregate="Sum" DataFormatString="{0}">
   <TotalFormat Level="3" Axis="Columns"></TotalFormat>
</telerik:PivotGridAggregateField>
<telerik:PivotGridAggregateField DataField="Average" CalculationDataFields="WRatio,TRatio">
</telerik:PivotGridAggregateField>
...

C#:

protected void RadPivotGrid1_ItemNeedCalculation(object sender, PivotGridCalculationEventArgs f)
        {
            if (f.DataField == "Average")
            {
                AggregateValue wratio = f.GetAggregateValue("WRatio");
                AggregateValue tratio = f.GetAggregateValue("TRatio");

                if (wratio != null && tratio != null)
                {
                    try
                    {
                        double wrVal = double.Parse(wratio.ToString());
                        double trVal = double.Parse(tratio.ToString());

                        if (trVal == 0)
                        {
                            if (wrVal == 0)
                            {
                                f.CalculatedValue = new DoubleAggregateValue(0);
                            }
                        }
                        else
                        {
                            double avg = (double)(wrVal / trVal * 100);
                            f.CalculatedValue = new DoubleAggregateValue(Math.Round(avg, 2));
                        }
                    }
                    catch (Exception ex)
                    {

                    }

                }
            }
        }