Aggregates - Delphi XE - CDS

Aggregates - Delphi XE - CDS

What I am trying to do is to find out the total for each item that is fully owed for every prescription. I can't do this on the main datasets because you have to be really careful as the display is easy to disrupt, so I have a new dataset.

Dataset:

object cdsPrescriptionItemSummary: TClientDataSet
Aggregates = <>
FieldDefs = <>
IndexDefs = <
item
Name = 'IDX_PRESCRIPTION_ID'
Fields = 'prescription_id'
GroupingLevel = 1
end
item
Name = 'IDX_PRESCRIPTION_ITEM_ID'
Fields = 'prescription_item_id'
GroupingLevel = 2
end>
Params = <>
StoreDefs = True
OnCalcFields = cdsPrescriptionItemSummaryCalcFields
Left = 224
Top = 240
object cdsPrescriptionItemSummaryprescription_id: TIntegerField
FieldName = 'prescription_id'
end
object cdsPrescriptionItemSummaryprescription_item_id: TIntegerField
FieldName = 'prescription_item_id'
end
object cdsPrescriptionItemSummaryqty_dispensed: TBCDField
FieldName = 'qty_dispensed'
ProviderFlags = [pfInUpdate]
currency = True
Precision = 14
Size = 6
end
object cdsPrescriptionItemSummaryqty_owed: TBCDField
FieldName = 'qty_owed'
ProviderFlags = [pfInUpdate]
currency = True
Precision = 14
Size = 6
end
object cdsPrescriptionItemSummarycalcFullOwing: TBooleanField
FieldKind = fkCalculated
FieldName = 'calcFullOwing'
Calculated = True
end
object cdsPrescriptionItemSummarycalcRightPrescriptionItem: TBooleanField
FieldKind = fkCalculated
FieldName = 'calcRightPrescriptionItem'
Calculated = True
end
object cdsPrescriptionItemSummaryitem_total: TCurrencyField
FieldName = 'item_total'
end
object cdsPrescriptionItemSummarycalcFullOwingItemTotal: TCurrencyField
FieldKind = fkInternalCalc
FieldName = 'calcFullOwingItemTotal'
end
object cdsPrescriptionItemSummaryaggPrescriptionOwingTotal: TAggregateField
FieldName = 'aggPrescriptionOwingTotal'
KeyFields = 'prescription_id'
Active = True
currency = True
Expression = 'SUM(calcFullOwingItemTotal)'
end
end

The OnCalcFields is as follows:

procedure TfmDispensing.cdsPrescriptionItemSummaryCalcFields(DataSet: TDataSet);
var QtyDispensedValid : Boolean;
begin

DataSet.FieldByName( 'calcRightPrescriptionItem' ).AsBoolean := DataSet.FieldByName( 'prescription_item_id' ).AsInteger = dmDispensing.cdsPrescriptionItemprescription_item_id.AsInteger;
QtyDispensedValid := DataSet.FieldByName( 'qty_dispensed' ).AsCurrency > 0;
// IMPORTANT: an owing is defined as the difference between the quantity dispensed and the quantity issued.
// So, why am I using qty_owed instead? Because a full owing can only be calculated when the prescription item is
// created, which is also the only time where the pharmacist may not have the item. Because of this,
// I can do it this way that is easier.
DataSet.FieldByName( 'calcFullOwing' ).AsBoolean := DataSet.FieldByName( 'calcRightPrescriptionItem' ).AsBoolean And QtyDispensedValid And ( DataSet.FieldByName( 'qty_dispensed' ).AsCurrency = DataSet.FieldByName( 'qty_owed' ).AsCurrency );

// This can also be a bit tricky to follow: the issue we have is that obviously we want to
// calculate the item total only when there is a full owing. We therefore have two choices:
// 1) Only add the total if there is a full owing and avoid a calculation OR,
// 2) Always include the total and calculate only the owed ones, setting to zero for
// other cases.
//
// I think that number 2) works better because it's more flexible and resilient.
// If we ever have to change how we calculate the owing, this will not influence how we
// expose the owed amount to the world. Doing 1) would have moved the calculation outside of this
// creating potential issues down the line.
if DataSet.FieldByName( 'calcFullOwing' ).AsBoolean then
DataSet.FieldByName( 'calcFullOwingItemTotal' ).AsCurrency := DataSet.FieldByName( 'item_total' ).AsCurrency
else
DataSet.FieldByName( 'calcFullOwingItemTotal' ).AsCurrency := 0;
end;

The calculated field is never null, yet the aggregate always shows null.

Anybody has an idea?

Thanks!

A