A good question for which I do not have an answer. But I do know of a way to list these sub totals in a portal...
Hi Phil - Ok, so let's try the portal route, how do I go about doing that?
My only hesitation in using a portal would be that I need these numbers accessible for further calculations, like comparisons to budget numbers to see if they are over or under...etc... I have used one line portals in the past, but as you know they trap the data and can not be used in further formulas. I like the SQL route, but boy is it finicky... My end result on this project will be to have a dashboard that will show each category of items, there totals from both cost and budget with another column showing the difference (Budget - Cost sort of thing).
I generally limit myself to one response per thread per day. Otherwise, volunterring in the forum consumes way to much of my day.
An alternative approach comes to mind that could make the ExecuteSQL work, though the performance hit involved might not be acceptible. A recursive custom function could be crafted to take the results returned by ExecuteSQL and reformat the number column to supply trailing zeroes after the decimal and even, if desired a leading currency symbol.
Using the portal method that I referred to ealier:
You'd need relationships similar to this:
DashBoardTO::anyField X Categories::anyField
Categories::CategoryFIeld = Expenses::CategoryField.
Then you can place a portal to Categories on your DashBoard layout with the Category field and a summary field from Expenses can be placed in your portal row. A calculation field defined in Categories can also compute these sub totals using the sum function to sum a field from expenses.
And yes, this totals all expenses in each category. There's an extremely good chance that what you really need are subtotals for a sub set of all the records in Expenses such as all the expense data for a specified month, quarter or year. Adding additional match fields in the Categories to Expenses relationship can accomplish that result. You can even use a global field or Fields defined in Categories so that the user can edit them on teh DashBoard layout to get different sets of sub totals.