NReco multiple measures at run time

Question

I am using the NReco library to build Pivot Tables. I'm following the examples and I was able to build a simple PvT from a DataSet. Now I want to build a PvT that receives a number of measures that are chosen at run time, but I'm having trouble with the dynamic nature of the process of using a list of measures each with it´s own formula of aggregation. The formula is known at run-time, and it's nothing more than a sum or an average, but is specific to the measure. I have the following code:

private string CreatePivotTable(DataTable dt, string[] lines, string[] columns, string[] dimensions, string measure)
    {

        var pivotData = new PivotData(dimensions, new SumAggregatorFactory(measure), new DataTableReader(dt));

        var pivotTable = new PivotTable(lines, columns, pivotData);
        var htmlResult = new StringWriter();
        var pvtHtmlWr = new PivotTableHtmlWriter(htmlResult);
        pvtHtmlWr.Write(pivotTable);

        return htmlResult.ToString();
    }

I wanted to do something like the following code to add the measures and the aggregator dynamically at run time:

 private string CreatePivotTable(DataTable dt, string[] lines, string[] columns, string[] dimensions, Measure[] measures)
    {


        var pivotData = new PivotData(dimensions, null, new DataTableReader(dt));

        foreach(var m in measures)
        {
            if (m.Formula.equals("sum"))
                pivotData.AggregatorFactory.Create(new SumAggregator(m.ColName));
            else if(m.Formula.equals("avg")){
             pivotData.AggregatorFactory.Create(new AvgAggregator(m.ColName));
           }
        }
     }

How can I achieve something like this? Is there a way to do it?

Answer 1

You can configure PivotData class to collect several measures in the following way:

private string CreatePivotTable(
    DataTable dt, string[] lines, string[] columns, string[] dimensions, Measure[] measures)
{
    var aggrFactories = new List<IAggregatorFactory>();
    foreach(var m in measures) {
        if (m.Formula.equals("sum"))
            aggrFactories.Add( new SumAggregatorFactory(m.ColName));
        else if(m.Formula.equals("avg")){
            aggrFactories.Add( new AverageAggregatorFactory(m.ColName));
        }
    }
    if (aggrFactories.Length==0) {
        // no measures provided. 
        // Throw an exception or configure "default" measure (say, CountAggregatorFactory)
        aggrFactories.Add( new CountAggregatorFactory() );
    }   
    var pivotData = new PivotData(dimensions, 
        aggrFactories.Length==1 ? aggrFactories[0] : new CompositeAggregatorFactory(aggrFactories.ToArray()),
        new DataTableReader(dt));
    // you code that renders HTML pivot table with PivotTableHtmlWriter
}

As alternative you can use PivotDataFactory component (NReco.PivotData.Extensions assembly) that can create PivotData instance by PivotDataConfiguration model:

private string CreatePivotTable(DataTable dt, string[] lines, string[] columns, string[] dimensions, Measure[] measures)
{
    var pvtDataFactory = new PivotDataFactory();
    var pivotData = pvtDataFactory.Create( new PivotDataConfiguration() {
        Dimensions = dimensions,
        Aggregators = measures
            .Select(m => new AggregatorFactoryConfiguration(m.Formula, new[] {m.ColName}) )
            .ToArray()
    });
    // you code that renders HTML pivot table with PivotTableHtmlWriter
}

PivotDataFactory knows about standard aggregator types ("Count", "Sum", "Average", "Min", "Max") and if you use custom implementations you can register them with PivotDataFactory.RegisterAggregator method (see Implement custom aggregator for more details).

BTW real user-defined formula (that can use aggregator values as arguments) is also possible: see "DynamicFormulaMeasure" example from PivotData SDK examples package.