Sub-Summary Report Confusion

[The Professor]

Hi all, and thanks in advance yet again for answering another of my noob questions.

I'm trying to create a simple report that shows total time billed for each staff member. I created a layout with one sub-summary part containing the standard fields "Staff" and "Hours Worked". The sub-summary part is set to sort by "Staff".

What I get in preview mode however is a normal list view of each record for each staff member with the individual hours worked for each record. What I want is a single, cumulative line entry for each staff member that also totals the hours.

What happens now:

Joe Smith 10 hours

Joe Smith 5 hours

Joe Smith 10 hours

Joe Smith 10 hours

Jane Doe 4 Hours

Jane Doe 6 Hours

Jane Doe 4 Hours

Jane Doe 6 Hours

Jane Doe 10 Hours

Fred Brown 2 Hours

Fred Brown 3 Hours

Fred Brown 6 Hours

What I want:

Joe Smith 35 hours

Jane Doe 20 Hours

Fred Brown 11 Hours

I looked at the "Customer Report" layout in the Time Billing template database and it behaves correctly. The template does not use summary fields, just a single sub-summary part that produces a single entry for each customer value with a cumulative total for time. (It sorts by customer name, not staff name as mine does, but is otherwise identical.) When I try to duplicate the report in my database, I get the repeating behavior described above.

[LaRetta]

You need to create a summary field called TotalHours. It would be total of Hours Worked. Place this next to the Staff Name in sub-summary part. Then, DELETE the entire body. The body is simply to show the detail on how the total was derived and isn't necessary. You can't create a columnar list (with grouped data) without a body but you can remove it after the report is created.

You will end up with a report just showing the Staff Name and their TOTAL hours. :wink2:

[The Professor]

My layout never had a body part.

I created the summary field "Total Hours Worked", but I still get individual listings for each record.

Now that I've added the summary field, as far as I can tell my layout is identical in design to the Customer Report layout in the Time Billing template, but I still get individual listings for each record.

For some reason it works it the template, but not in my own identical layout.

Edited March 27, 2006 by Guest

[LaRetta]

Can you post your file or at least a pdf shot of the report layout in layout mode?

[The Professor]

Thanks LaRetta, I figured it out. I wasn't sorting by the Staff field before displaying the report in Preview Mode. I thought the sub-summary part setup dialog did the sorting for you, but now I realize it will only sub summarize if the found set is already sorted by the appropriate field.

Thanks for the help!