13 Replies Latest reply on Sep 1, 2013 12:37 PM by MarciaMorrison

    Trying to create a summary expenses report - help!

    MarciaMorrison

      Title

      Trying to create a summary expenses report - help!

      Post

           Dear Collective Wisdom,

           After a couple months on other projects, I'm back to FileMaker. I'm trying to create a summary report of yearly household expenses, from an expenses database that now contains several years worth of data. 

           I've been reading up on subsummary reports, and I've been able to create a simply report and associated script that gives me a summary total for one particular category, but I'm running into a wall trying to create a larger report that contains a total for each major category, totals for each subcategory, and a grand total for the year, and also shows each month's actual and each month's average expense for the various categories /subcategories. I do not want to see individual data entries in this report, that would make it way too cumbersome. 

           In trying to wrestle this to the ground, I made up a little food expenses db. I'm attaching a screen shot of what I've got. 

           I'm hoping that if someone can show me how to do this with this tiny test database, I will be able to extrapolate to the larger real-world case. 

           Thanks in advance for any advice!

      Test_db_expense_report_layout.png

        • 1. Re: Trying to create a summary expenses report - help!
          MarciaMorrison

               Here's the little db I put together for working on this problem. 

          • 2. Re: Trying to create a summary expenses report - help!
            philmodjunk

                 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.

            • 3. Re: Trying to create a summary expenses report - help!
              MarciaMorrison

                   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.

                   Marcia

                    

              • 4. Re: Trying to create a summary expenses report - help!
                MarciaMorrison

                     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?

                • 5. Re: Trying to create a summary expenses report - help!
                  philmodjunk

                       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.

                  • 6. Re: Trying to create a summary expenses report - help!
                    MarciaMorrison

                         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. 

                    • 7. Re: Trying to create a summary expenses report - help!
                      MarciaMorrison

                           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!

                            

                            

                            

                      • 8. Re: Trying to create a summary expenses report - help!
                        SteveMartino

                             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?

                             Quick SShot

                        • 9. Re: Trying to create a summary expenses report - help!
                          philmodjunk

                               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.

                          • 10. Re: Trying to create a summary expenses report - help!
                            MarciaMorrison

                                 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.

                                 M.

                            • 11. Re: Trying to create a summary expenses report - help!
                              MarciaMorrison

                                   Here's the other screen shot.

                              • 12. Re: Trying to create a summary expenses report - help!
                                MarciaMorrison

                                     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:

                                • 13. Re: Trying to create a summary expenses report - help!
                                  MarciaMorrison

                                       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.)

                                       M.