I did a couple of posts a while back on totaling here, here and here - I recently got a question asking how to do some conditional totaling, more specifically in a list of invoice amounts how I can total according to an invoice type - something along those lines anyway. To make myself clearer assume you have the following data:
Take a look at the INV_TYPE, we have 'Standard' and 'Internal' invoice types. How can we get totals by invoice type?
One method would be to group by the INV_TYPE, that way we could very easily get the totals, but lets assume we can not do that, our users do not want that type of grouping. The just want an invoice listing with a total fro all invoices and totals for each type. We can achieve this using an XPATH expression.
If we loop over the invoices ie for-each:G_INVOICE_NUM to list the invoices we can then generate a total for the invoice entered (ENT_AMT) and accounted amounts (ACCTD_AMT) quite simply with:
<?sum(ENT_AMT)?> and <?sum(ACCTD_AMT)?>
To get at the totals for the invoice types and assuming that we know the different types we can use:
<?sum(ENT_AMT[../INV_TYPE='Internal'])?> and <?sum(ACCTD_AMT[../INV_TYPE='Internal'])?> and
<?sum(ENT_AMT[../INV_TYPE='Standard'])?> and <?sum(ACCTD_AMT[../INV_TYPE='Standard'])?>
the XPATH statement is essentially an if statement, including only those amounts that satisfy the boolean expression. Notice the ../, we need that because INV_TYPE is at the same level as the amount elements, so we effectively go up a level to the parent and then down to the INV_TYPE.
Now lets assume we do not know the number of invoice types in the data but we want to show them and their invoice totals. For this we are going to have to use some grouping. We can group the invoices by INV_TYPE and then do a sum on the invoice amount values.
Its pretty striaght forward (once you're familiar with the regrouping) we have a for-each-group on the G_INVOICE_NUM parent and group by INV_TYPE thus:
Then we just create the table to hold the results, for the totals we do not need the XPATH any more :
<?sum(current-group()/ENT_AMT)?> and <?sum(current-group()/ACCTD_AMT)?>
We need the current-group() command because our amounts are inside the newly created INV_TYPE group. This approach does mean that we have to effectively loop over the data again but I think its cheaper than to create updateable variables to store types and totals as you do the main loop.
Hopefully we have now covered the totaling question with these articles, you can get the sample template and data here.