Custom Aggregate Functions in Reports.

Question

I have daily records of production per category, i.e. category A: 80 tons. Each report row will look like this:

             Daily                  Week to Date          Month to Date
             Actual  Plan   Var     Actual  Plan   Var    Actual   Plan   Var
Category A   100     110    -10     230      300   -70    900      1200  -300
Category B   etc. etc.

Category A: On day 2 of the week, 10 skips of ore are mined. At 10 tons/skip, that equals the 100 tons you see above, under Daily Actual. So, for Category A, I need a formula defined that says "input * 10", but it won't always be a simple multiplication factor. I need to store any formula for the input -> Daily figure. On Day 1, I have a 132 ton Actual, but a positive error of 2 tons/day is known, so the Week to Date Actual is not just a simple sum and needs to be adjusted down. So I need a special formula for Week to Date actuals, like (d1+...+dn) - 2n.

Category B: On day 2, an assay result gives x kilograms of nickel per ton of ore. For Category B, I need a daily Actual formula of nickel/ore * 100, but again, I must be able to assign any formula to Daily Actuals for Category B. For the Week to Date Actuals, do I use mean, median, or mode, and is there an adjustment?

In my Categories DB table, I have e.g.

Category A: UnitOfMeasure=Tons, DailyFormula="input*10",WeeklyFormula="(d1+...+dn) - 2n"

My code needs to apply the unique formulae for Category A when calculating values for a Category A line in the report. I want to try and work this into a LINQ query against the raw data.

EDIT: I need a way to let the user define these formulae, and to dynamically parse and evaluate them at run time, based on the formula stored in a DB column.

Answer 1

I hope I understood correctly. The idea I have for custom grouping is used by map from category to grouping method.

I have simplified it a little to make the answer clear:

Let's say the rows of the report input are of this class:

public class Production
{
    public string Category { get; private set; }
    public int DayIndex { get; private set; }
    public double Input { get; private set; }
}

In this class we have category, the index of the day and the input that can represent for example the amount of ore mined.

Production[] results = // The inputs to the report

// The map from category to the grouping method
var groupingMethods =
    new Dictionary<string, Func<IGrouping<string, Production>, double>>
      {
          {
              "ore", // Category name
              grouping =>                                        // The method
              grouping.Sum(production => production.Input) * 10  // grouping ore 
          },                                                     // rows
          {
              "nickel",
              grouping =>
              grouping.Sum(production => production.Input) - 2 * grouping.Count()
          }
      };

// The query create groups based on the category
var report = from result in results
             group result by result.Category
             into categoryResults
             select new
                  {
                     // Created anonymous object with Category
                     Category = categoryResults.Key,
                     // Find the grouping method of the category, invokes it
                     // and store the result in Week column
                     Week = groupingMethods[categoryResults.Key](categoryResults)
                  };

The main idea here is the "switching" methods depends on the category. It can be easily changed to group by other criteria and of course add and change the groupings. In addition, for additional columns it can easily adjust by adding another dictionary for the new column.

For example:

var weekMethods =
    new Dictionary<string, Func<IGrouping<string, Production>, double>>() ...

var monthMethods =
    new Dictionary<string, Func<IGrouping<string, Production>, int>>() ...


from result in results
group result by result.Category
into categoryResults
select new
       {
           Category = categoryResults.Key,
           Week = weekMethods[categoryResults.Key](categoryResults),
           Month = monthMethods[categoryResults.Key](categoryResults)
       };