How to use customSelectExpression for detail AND userFormula for summaries

[jay.l.fisher]

SmartClient Version: v9.0p_2013-11-14/PowerEdition Deployment (built 2013-11-14)

I have a datasource with two fields, "cost" and "retail", and I need to calculate a "margin" using those two fields as 1-(cost/retail).

Originally I defined a user formula which works perfectly, except for filtering. The users want to apply a filter to select records where margin is > x. So instead of a user formula I created a customSelectExpression to calculate the value. This provides the filtering they were looking for.

But the datasource is attached to a ListGrid that shows grid and group summaries. The problem is that the margin column is being summed for grid and group summaries when instead it needs to be recalculated using the sum of cost and retail for the grid or group.

How can I combine the two techniques so the detail row values are calculated by the SQL query (to allow for filtering), but the summaries are calculated by the user formula?

[Isomorphic]

For a normal formula field in a ListGrid, there is a property ListGridField.applyAfterSummary which controls the exact behavior you're looking for. However, since you're doing your calculations in the DataSource, the ListGrid summary logic thinks that field (margin), is just another normal field, and there's no such control available.

Thus, the simplest solution is likely to override ListGrid.getGridSummary(field), which returns an array of summary values (one for each summary row) given a field in the ListGrid (see docs). In this case, you'd want to have 3 different behaviors:
- if the field is not an input for the margin computation, and not margin, then you just fall through the native implementation
- if the field is an input to margin, then you'd want to cache the return value somewhere in your own data structure for later use
- if the field is margin, then you'd want to perform your calculation using your earlier stored value(s) and then return the result as the result of getGridSummary()
Note that this API is called in the order the fields are present in the ListGrid, so your margin field will need to be after its inputs in the field ordering. This is because this API was intended to allow you to compute results from the records, not other fields of the summary itself.

A similar approach should work for ListGrid.getGroupSummary(), although that function has slightly different arguments - check the docs.