How can I get totals of a field (ie. summed over all the records in a table)?
To make a summary of a field, you can simply create a footer area using the Arrange=>Main footer menu option, then drag the field you want to summarise from the body section into the footer.
Depending on the type of the field, you'll get a default summary type, eg. for a numeric field you'd get a summary field with the formula:
Can I also group the records according to a certain field, and then have separate totals for each group?
For example, can I group my butterflies by colour, and then find the average wingspan for each colour?
You can get a 'grouped' summary using a SQL query as follows:
Having given the SQL query a name and saved it, create a new layout, press Ctrl-A then Delete to get rid of all the frames, and then choose Query/Table=>Attach to=>MySQLQueryName (or whatever you called it), and then bring the fields on from the fields menu on the toolbox. If you create a main header first and set the layout type to All Records, you'll find that the fields are arranged across the page just under the header, so you'll get a tabular result.
You can also sum the values within a subview by dragging a field from the subview onto the main record.
This will give a summary field with the formula:
Sum('MyField') For 'MainTableName'
which shows that there is a separate summary result for each record of 'MainTableName' (ie. whichever table is the main one in the overall query for the layout containing the subview).
In this case DataPower effectively groups the summaries on the same field(s) that join the main table to the subview.
You can see an example of how to do this in the Invoices example on our web site at http://www.iota.co.uk/dp2.
You can alter the formula of a summary field to use any of the following aggregate functions:
Sum Count Average Min Max
and you can use arbitrary expressions within and outside the aggregate function, eg:
Sum('f1') / Max('f2'*'f3')
Note that the aggregate functions, which summarise a field over multiple records, shouldn't be confused with the non-aggregate versions of Sum(), Count(), Average(), Min() and Max(), which can act on normal fields within a record, eg:
is equivalent to:
except that nulls are treated as zero, instead of causing the whole formula to come out null.
Back to DataPower 2 Documentation Page
Back to DataPower 2 Home Page