NReco PivotData: Custom AggregatorFactoryConfiguration Expression

Question

I'm using NReco PivotData Toolkit with a connection to my Elasticsearch index.

var pvtDataCfg = new PivotDataConfiguration()
{            
    Dimensions = GlobalVariable.dimensions.ToArray(),
    Aggregators = new[] {
        new AggregatorFactoryConfiguration("Sum", new String[] {"nameOfField"}),
    },
};

This is the configuration I use for the query. I would like to do something as "nameOfField_1" + "nameOfField_2". Is there a way to define custom formulas/expressions?

I read somewhere that the AggregatorFactoryConfiguration just accept defaults formulas ("Sum", "Max", "Count", ...). If I try to use "FormulaAggregatorFactory" as Aggregator it gives me an error because of class conversion.

Answer 1

In the case of calculations there are 2 possible variants:

• formula that is evaluated over already aggregated resultset on .NET application level. For this kind of calculations you may take a look at "ToolkitPivotBuilderMvc" example that contains FormulaCubeWrapper implementation (Services\FormulaCubeWrapper.cs file). This is only one of the possible approaches, in fact your calculations may be encapsulated in your code that loads/prepares IPivotData object for pivot tables rendering (see also https://www.nrecosite.com/pivotdata/query-cube.aspx "Define derived dimension" and "Define derived measure" sections).
• an expression that is evaluated on the database level. Definition of such dimensions/measures is database dependent, and your connector should handle this convention accordingly. In terms of "PivotBuilderMvc" example, this should be a code ElasticSearchCube implementation that can handle correctly either specific AggregatorFactoryConfiguration instances like new AggregatorFactoryConfiguration("Sum", new String[] {"expression" "nameOfField_1+nameOfField_2"}) -- in that case, this set of parameters should be somehow handled as you need. Alternatively, this can be your own implementation of IAggregatorFactory -- this approach is needed if you need to use DB-specific aggregate function (more about custom aggregators: https://www.nrecosite.com/pivotdata/implement-custom-aggregator.aspx).

For reference purposes, I may recommend to check how ElasticSearch connector works in PivotData microservice - this is a reporting engine based on NReco PivotData Toolkit. It already has built-in support for formulas that can be evaluated either on app's side or as an database-specific expression in the query. If you need something like that please let me know, I may try to enhance PivotData Toolkit examples with similar capabilities.