Dynamic Subtotal Reports

[Jarvis]

Hi everyone!

I have a database that tracks time spent working on various customer projects.

The basic fields are:

CUSTOMER NAME (text)

TASK DESCRIPTION (text)

ELAPSED MINUTES (Calc > Stop time - Start time)

I would now like to generate a report that summarizes the time spent working on each type of task. In some cases we will do the task just once. Other times we will do the same task many times.

I would like the report to indicate just one subtotal per task description. IE. if the task happens three times, sum the three discrete entries and report them as one subtotal.

I would also like , if possible, for this report to be dynamic.

As each unique task is created, I would like the report to recognize that we will need to include this new task in the subtotal report.

Can this be done within Developer 7, or will it need a custom plug in?

Thanks,

Jarvis

[Ender]

Try a columnar report with a Sub-Summary by Type of Task part. Use a Summary(Total) field to total the Elapsed Minutes. No plug-in required.

[Fenton]

Well, you don't say what "tables" you have in this database. You should have, at the very least, Customers and Tasks, related by CustomerID.

[Probably you should also have a Projects table in between.]

The report would be in Tasks. You would have a Subsummary Part on the layout, with its "break" field (sort field) CustomerID.

A 2nd Subsummary Part would have the Task (it would be good to have a reference table with Tasks, so they'd have IDs, but a consistent name would do). The part's break field would therefore be Task (or TaskID).

You would also have a field, "TaskTime_SUM," a Summary type field, Total of Elapsed Minutes (actually what you've got now is seconds, so first divide by 60 in the calculation if you want minutes).

The special ability of a Summary field is to produce the correct arithmetic result for whatever part it's in, with the hierarchy dependent on the Sort order. So, in the Task Subsummary Part it would be the total for that Task, for that Customer, if you Sort by the CustomerID field, then Task.

To show the Subsummary parts you must enter Preview Mode. That's the only time they show; mostly used for printing.

[Jarvis]

Ender & Fenton,

Thanks for aiming me in the right direction.

This is the first time I've ever paid attention to a subsummary part on a layout. I think I should get my head around this concept before enlisting more help.

Thanks again!

Jarvis