Customize PivotGridAggregateField Calculation

2 posts, 0 answers
  1. Lawrence
    Lawrence avatar
    10 posts
    Member since:
    Jun 2015

    Posted 07 Nov Link to this post

    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.

  2. Lawrence
    Lawrence avatar
    10 posts
    Member since:
    Jun 2015

    Posted 07 Nov in reply to Lawrence Link to this post

    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)
                        {
                        }
                    }
                }
            }

     

     

Back to Top