I would do this with a self-join relationship. That is, a Table to same-Table relationship using either the Dept name or some fixed ID for the each Dept. in order to group the records together by Dept. Many developers these days would choose to write an SQL statement, but I'm a little more old-school.

Once you define the self-join relationship, you can use a calculation whose context is the left side of the relationship and the operand(s) are from the right side:

Sum( selfJoinRel::Elapsed_Time )

Your situation may be more complex, such as needing the self-join to take "month" into account to provide monthly summaries rather than every record in the table.

I do use that option and it works well. Not only can you use use additional match fields (such as one for the month) to control what data is totaled, but you can set up one row filtered portals with different filter expressions and your summary fields placed in the portal row to get different subtotals of your data.

You can also create a summary layout with sub summary parts (when sorted by Group) and then summary fields placed in the sub summary part would show the sub total for that group. This may or may not be a good approach, you'll have to decide for yourself. The advantage is that you can get a variety of subtotals from the same set of summary fields just by setting up different sub summary parts and controlling both what records appear in your found set and how they are sorted. The disadvantage is that this tends not to be a report arranged in tabular or (Spreadsheet like) format so you'll have to decide for yourself which you like (or use both for different reports for that matter.)

and yes, ExecuteSQL can be used. Since SQL is yet another computer language, one that specializes in querying relational databases, there can be a bit of added learning curve to using that function if you are unfamiliar with SQL. But if you'd like to discuss using that function here, simply ask and I or another community member can then respond with info on that approach.

The following is a sample "simple but brittle" query example for ExecuteSQL:

ExecuteSQL ( "

SELECT Sum ( \"cClosedStatusFieldHere\" ) FROM \"YourTableHere\"

WHERE

Upper ( \"Dept\") = Upper (?)";

"" ; "" ; YourTable::Dept )

This will sum all time values for "closed" for the department specified in YourTable::Dept-- a value from your current record. I've enclosed the field in table occurrence names in double quotes using \" just in case a name might be a reserved SQL word, contain a space or start with an _ all of which might return a syntax error if you didn't quote them. I used Upper to eliminate possible mismatches due to differences in capitalization as text comparisons in SQL are case sensitive. (Could have used Lower to the same result.) Other terms can be added to the WHERE clause to limit results by date, for example

Once you have the basic query working, you might consider rewriting the query such that you con't enclose field and table occurrence names in quotes. When you do that, a future name change via Manage | Database can break your SQL query as it won't update to use the new name when the names are enclosed in quoted text.

The "Button bars as value lists" example in the following file sets out one such approach and the file's custom functions may be imported into your solution if you choose to use them:

There are drawbacks to the method you have chosen to employ. If you later add another group, you have to go in and define more fields. Other approaches to this solution would not require that and adding another group would then be simply a data entry task.