Simplifying Grid Summaries Calculation with the Help of Expressions

• Download source - 23.8 KB

Introduction

Usually, when you want to calculate a complex summary for tabular data, let's say displayed in an ASP.NET GridView, you need to write code for three logical parts:

• init your summary calculation state variables - counters, sums, etc.
• handle row changed event to increment calculation state
• get result based on state variables

And of course, you need these state variables declaration. Sometimes even additional structures to keep your page code clean. You can significantly simplify/replace this with the help of a simple library for evaluating aggregate expressions.

See project site on CodePlex.

Background

Main project brief description:

ExpressionEngine - contains all the classes responsible for expression evaluation.

• ExpressionEveluator - core of this library. This class converts infix expressions to postfix form and evaluates them. It supports only "plain" expressions (without aggregate functions). Also this class is independent from the other classes in the library, and can be used separately if you need just to evaluate some expression, like (X+Y)*Z. Exposes the following members:
• Constructor - ExpressionEveluator(string) - accepts an expression string as an argument.
• Evaluate(IValueProvider) - evaluates an expression and returns the result. The argument IValueProvider allows ExpressionEveluator to get the actual variable values if some appears in your expression. But if the expression does not contain any variables, for example (1+1)*2, this argument can be null.
• AggregateExpressionEvaluator - derived from ExpressionEveluator, and this one supports aggregate functions. For now, the supported functions are: SUM, AVG, MAX, MIN, COUNT. To add your own one, it's enough to create a class derived from BaseAggregateFunction and a state class derived from BaseAggregateState. Then register this newly created aggregate function class in _aggregatedFunction - see the private field of AggregateExpressionEvaluator.

Exposes the following members:

• Constructor AggregateExpressionEvaluator(string) - with expression as argument. This can contain aggregate functions, for example - SUM(Column1+Column2)+AVG(Column3).
• Reset() - you should call this function if you want to use a single instance of AggregateExpressionEvaluator to evaluate expressions against deferment data sets.
• RowChanged(IValueProvider valueProvider) - call this function for every row of your data. IValueProvider allows AggregateExpressionEvaluator to get field values for the current row.
• GetResult() - after all rows were passed through the RowChanged method, you can call this one to get the result.

Using the Code

Let's see how to replace the standard approach for GridView summary with expressions. To make it simpler, let's start from a grid with one column, and AVG as summary:

<asp:GridView ShowFooter="true" AutoGenerateColumns="false"
runat="server" onrowdatabound="OnRowDataBound">
    <Columns>
        <asp:BoundField DataField="Column1" HeaderText="Column1"/>
    </Columns>
</asp:GridView>

In the code-behind, we need to define the OnRowDataBound function to process every single row:

protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        _count++;
        _sum += ((MyDataItemClass)e.Row.DataItem).Column1;
    }

    if (e.Row.RowType == DataControlRowType.Footer)
    {
        if (_count != 0)
            e.Row.Cells[0].Text = string.Format("AVG(Column1) = {0}", _sum / _count);
    }
}

And the state variables:

private int _count = 0;
private double _sum = 0;

And the same with expressions summary:

Instead of a state variable, we are defining an AggregateExpressionEvaluator instance with summary formula:

AggregateExpressionEvaluator summary1 = new AggregateExpressionEvaluator("AVG(Column1)");

Updated OnRowDataBound function:

protected void OnRowDataBound(object sender, GridViewRowEventArgs e)
{
    if (e.Row.RowType == DataControlRowType.DataRow)
    {
        ReflectionValueProvider provider = new ReflectionValueProvider(e.Row.DataItem);
        summary1.RowChanged(provider);
    }

    if (e.Row.RowType == DataControlRowType.Footer)
    {
        e.Row.Cells[0].Text = string.Format("AVG(Column1) = {0}", summary1.GetResult());
    }
}

That's it.

If you have several complex summaries in your grid, it can significantly simplify your code. Also, with expressions, it's much easier to change the calculation algorithm, or even load it dynamically.

See example in AspNetGridViewExpressionSummary\Default.aspx.cs.

Actually you can calculate summaries even without a grid control. See the below example of calculation of AVG salary for an array of Employee objects:

public class Employee
{
    public int Salary { get; set; }
}

Employee[] employees;
//.....

AggregateExpressionEvaluator evaluator = new AggregateExpressionEvaluator("AVG(Salary)");
foreach (object obj in employees)
{
    ReflectionValueProvider provider = new ReflectionValueProvider(obj);
    evaluator.RowChanged(provider);
}

double avg = evaluator.GetResult();

See example in TestExpressionParser\AggregateTestDataItem.cs.

History

• Version 1 - 16^th March 2013 - First version.