Here's the little db I put together for working on this problem.
Your first post shows a good start. But if you don't want to see the indivdiual entries, just the sub totals for each sub category grouped by category, click on the body part label and press the delete key to remove it from the layout.
And you can resize your sub summary layout parts as they do not need to be so tall.
Then define a pair of summary fields to compute the total and average of your total amount field.
Place those summary fields in the sub category sub summary part to see a total and average for each sub category. Place them in the category sub summary layout part to see a total and average for the entire category. And then you can place the same two fields in a footer or trailing grand summary layout part to get a grand total and average.
Finally, make sure to include both category and sub category fields in your sort order and make sure that your records are sorted. Such a layout with no body will be blank unless your records are sorted in an order that includes at least one sub summary layout part's break field.
Dear PhilModJunk (or just Phil?),
Thanks for your reply. I've deleted the Body part. I will resize the Subsummary parts when I've got the report ready to go, they're just that size right now so I can see the whole label.
However, I'm having trouble with defining the summary fields you suggest. Maybe it's just because I've got a cold and my brain isn't working too well today, but there's something here I'm not seeing.
I go to File > Manage > Database as usual, in the Fields window click on the Create button, type in a field name Summary 1 and then choose field type Summary. So far so good. Then when I click on the Create button, it takes me to an options window where I have a list of choices of types of summary, plus a list of my four already-defined fields. However, the already-defined fields and the Okay button are grayed out and not selectable. And none of the things I can choose at this point result in being able to select a field and/or click on the Okay button.
I kind of understand about putting the summary fields in several different places. I expect I will understand it better once I get a working report and can see it in action.
Creating a script to Find the range of records I want included in the report and then Sort them appropriately seems to be part of the process. So I need a "Generate report" script. It would be nice if it could pause for me to enter which dates I want the report to include. Seems to me I figured that out awhile back, so I'll work on figuring that out again.
Oh, another question. I'm having trouble understanding the term "break field". I think it's the field referenced in each subsummary part? Why do they call it a break field?
Will this report, when it works, show results for all the categories and subcategories (assuming the sort order is correct) ?
Thanks for your patience, and TIA for your assistance.
Here's another odd thing. In Layout mode, I select the Date paid field, but I the choices in the Data Formatting section of the Inspector are grayed out. And if I go up to the menu bar and open the drop-down menu, I don't see any options for formatting the date there either. Again, what am I missing?
Yes the "break" field is the "sorted by" field specifed for the sub summary part. I can only guess that it is called that as it is the field used to break your report up into groups for your sub totals.
Summary reports can only total up a field of type number or a calculation field with Number selected as the result type. Perhaps your fields are all of type text? They also can only summarize a field that is defined in the same field as is the summary field that summarizes it.
Ah HA! That was the problem. And the problem with my date format question. I had neglected to specify "number" and "date" for those fields.
Okay! Now things are coming together. I've attached a screen shot to show you my progress.
And that leads me to more questions. Why are there two blank lines at the top of my report?
Also, you can see that I modified the field name from "groceries" to "a groceries" to get it to sort first. What would be a less kludge-y way to do this? (I.e., without changing the field name.) Some kind of priority field?
I'm chomping at the bit to try my newfound slightly-improved report skills at the bigger db!
If your subcategory is a dropdown list, edit the value list to put them in the order you want them to appear. Then in your script that creates the report, where you get the pop up screen 'Sort Records', click on the subcategory field in right, , click custom order based on value list, and select the subcategory value list.
As far as the blank lines, what does the layout look like in layout mode?
Steve is suggesting what I would have suggested. Another method is to add a number field to which you assign a value for setting the sort order for your categories. That approach, however, requires modifying your layout design as this then uses a different field as the break field. You'd thus have to update your sub summary layout parts and any scripts that sort your records to use this new field. Steve's method avoids those complications.
It looks like you have at least two records where there is no visible text in the category field, One may be empty and the other may have nonvisible text such as a return or space character.
Thanks again for the suggestions. There did turn out to be a stray empty record causing the blank lines; I deleted it, and that glitch went away.
Now a couple more questions.
I've re-created the script in my larger Expenses db, and it seems to be running okay after some fiddling around. It's now creating a simply report for me.
I'm puzzled as to why there is so much space between the individual lines. I've got the fields snugged up to the part boundries as close as I can (as far as I can tell), and the parts tightened up. I can keep reducing the text size, but I still wind up with that pesky extra space.
Would you happen to know of a control that can eliminate some of that? Its got to be somewhere in the Inspector on the Position or Appearance tab, but I can't figure out which one of the sections might do the trick.
Thanks again. I'll include a screen shot or two.
Here's the other screen shot.
Gah! Apparently I pressed the wrong button and the first question /screen shot didn't get posted. Let's try this again.
Now I've got a simple report. So far, so good! But the lines have more space between them than I'd like. I can keep reducing text size, but that pesky space is still there. The control has got to be in the Inspector in either the Position or Appearance pane, but I can't figure out which section.
Here's the screen shot of what my report looks like:
And, hey! I had another question about scripts, but I was able to figure it out myself. Yay me!
(I'm sure I'll come up with more, just gimme a minute.)