DataPower 2 Summaries
How can I get totals of a field (ie. summed over all the records in
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:
Select 'GroupField', Sum('MyField')
Group By 'GroupField'
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
Sum Count Average Min Max
and you can use arbitrary expressions within and outside the aggregate
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.