I can do a portal for each category, filtered by each category, but that only really displays the number, I need a number I can use in further calculations.
If you ...
- create a summary field “Total of”: Discount Amount in the Discount table
- put this field into a filtered portal (the field can be invisible; or create a one-line portal with the same filter just for the field; this is nice for display purposes, where having the result in every portal row would be distracting)
- give the field an object name (e.g. "total_promo", or "total_QSA")
... you can get the result for the respective filtered set using GetLayoutObjectAttribute ( "total_promo" ; "content" ), or GetLayoutObjectAttribute ( "total_QSA" ; "content" )
If you need the result inside a script, use e.g.
ExecuteSQL ( "
SELECT SUM ( \"Discount Amount\" )
WHERE \"Discount Category\" = ?
" ; "" ; "" ; "Promotional" // or "QSA", or a field or variable reference …
Or you can just use that summary field in a summary report.
Set up a list view layout based on your table. Add a sub summary layout part "when sorted by" your category field. Put this summary field and the category field into this layout part. Delete the body layout part.
Do a find for the records you want in your report and sort them by category. (subtotals will not appear until records are sorted by category.)