Summary Fields in a Report

[Kasei]

I hope you guys can help me. Here is my problem. I have created a form that collects information for the following: number of hours billed, number of hours worked, the pay rate, the amount and the month the information is recorded.

I would like to create a report that would allow me to show the total amount of money spent for each month. For example

Month Total Spent Running Total

May $29,26201 $29,262.01

June $29,720.97 $68,982.98

July $49,910.21 $118,893.19

Creating the summary for a running total I can do. However, I am having a very hard time getting all of the months to add up and produce a total. I keep getting each month entry individually brokenout with its amounts.

For example:

May $21020

May $15002

May $9015

June $12982

June $12900

July $5690

July $11281

I know I am missing something simple, but I have been looking at this for a few days now and I just can't figure it out.

Any suggestions would be greatly appreciated.

Cheers,

Kasei

[FMPing]

create either a index calc field or calc field for month and year. Use the field as key for another calc field using sum() function.

[John Mark Osborne]

The proper way to create a report like this is to use a subsummary part. Totaling values through relationships is a great trick but is not designed for large number of records and relationships disregard found sets. Subsummary reports are much more flexible.

Create a subsummary part that sorts by your month field. This is likely a calculation that grabs the month out of the date field:

Month(DateField)

The subsummary part will group all the like records together and allow you to add them by group. That's because summary fields are context sensitive. In a body part, like I think you have done, they total all the records in the found set, regardless of grouping. In a subsummary part, they total by group.

When viewing a subsummary part, sort by the Month field and then preview or print. The report will not show in browse mode.

[Kasei]

I tried creating a report that would total through a realtionship and decided the database would be too large to conduct this process on a regular basis. As far as a calculation to grab the month out of the date filed, do you have one that is reliable? So far I have not been able to get one to work smoothly.

Thanks for your help John.

[comment]

If you're getting two subsummaries for the same month, you probably have inconsistent entries in the Month field, e.g "May" and "May " (with a trailing space).

A calculation of Month (YourDatefield ) should do better - unless your report spans more than a single calendar year. In such case it's best to use a calculation with a Date result =

YourDatefield - Day ( YourDatefield ) + 1

You can format the field to show only the Month/Year on the layout.

Don't forget that both the subsummary definition and the sort order must be changed over to the new field.

[Kasei]

Thanks for the help. I have been trying to get this to work for a couple of days now. Do you know if FileMaker 6 requires a different field arrangement? I have not been able get the results I have been trying to achieve with this type of report.

Thanks again. I know I am missing something really simple and I have yet to discover it.

[LaRetta]

and I have yet to discover it.

And you're not helping US help you to discover it either. Please indicate what you tried and not just that 'it didn't work.' What PART didn't work. What did you do? If you gave us the specifics, we can pin down problems. Generic responses don't help here.

Your assignment, when given a suggestion, is to implement it. If it doesn't work, be specific on what you did (step by step). Include pictures and/or file during this Show And Tell process and talk a lot! :wink2:

[Kasei]

I must apologize for the lack of detail regarding my difficulty with getting this particular type of summary report to work. Because of the nature of the database I am unable to provide screenshots and information.

However, thanks to everyone I was able to figure out what I was doing wrong. It was something minor that I overlooked. I essentially recreated all of my summaries from scratch and reworked how informatoin was being pull. Thus resulting in the type of report I was striving to make.

Thanks again for all of your help. Hopefully I will be able to return the favor soon.