13 Replies Latest reply on Jun 29, 2009 1:18 PM by philmodjunk

    Sub Summary Report



      Sub Summary Report


      1st Sub Summary based on Client Name

      2nd Sub Summary based on File Number


      There could be 2 of the same File Number's for the same client with a total of 3 records.



      File Number





      How can I show count of two in the Client Number summary sort?

        • 1. Re: Sub Summary Report



          Thank you for your post.


          Create a Summary field that is equal to a Count for "File Number".  Then, put this Summary field into the Sub-Summary part based on File Number.  You will get the desired result.



          FileMaker, Inc. 

          • 2. Re: Sub Summary Report

            I have created a summary field, but I always end up with "3" instead of "2".



            • 3. Re: Sub Summary Report
                 Make sure that you've sorted your records by file number and that your Sub Summary part is set to "when sorted by file number".
              • 4. Re: Sub Summary Report

                I must be doing something wrong.  : )


                I'm still coming out with "3"

                • 5. Re: Sub Summary Report

                  Did you put the summary field in the Sub-Summary part?


                  If even one pixel of the field crosses the line, filemaker will treat it as part of the body or header instead of as part of the sub-summary part. If the boundaries are tight, try re-sizing the sumb summary part so there's a little white space above and below the field's boundaries.


                  What you should see is:


                  File Number count: 1



                  Filenumber  count: 2



                  • 6. Re: Sub Summary Report
                       Yes, I can produce what you have...but I'm looking for a total count of "2" instead of "3" for then entire count.
                    • 7. Re: Sub Summary Report

                      Inaz wrote:
                      Yes, I can produce what you have...but I'm looking for a total count of "2" instead of "3" for then entire count.


                      Please explain, that makes no sense to me.


                      Referring to the sample data, the total count is 3, not 2.  The sub total, which is part of the sub summary part: "Filenumber count: 2" is 2. The summary field will display a sub-total if you put it in the sub-summary part and a total if put anywhere else.


                      If you want the count to be displayed below the listed items, then create a new sub-summary part, sorted by filenumber, and set it to "print below" the summarized items.


                      Perhaps you can type up an example of what you want the report to look like.

                      • 8. Re: Sub Summary Report

                        This is what my report looks like:



                        Client Name: ABC



                        File Number: 1111

                                         2222 - there are two filenumbers records with 2222.


                        I want to account for two that are showing...does this make sense?



                        • 9. Re: Sub Summary Report
                             Where are you putting your summary field?
                          • 10. Re: Sub Summary Report
                               I tried it everywhere.
                            • 11. Re: Sub Summary Report

                              Where do you want it and what value do you want to see? I'm trying to complete my understanding of what you want.


                              Here's what I'm guessing that you want:


                              Client Name: ABC



                              File Number: 1111   [1]

                                                 2222   [2]


                              Where the number in brackets is your file count. If so, do this, paying careful attention to the ------ marker that divides the different part types:


                              -----Sub-Summary when sorted by client name-----

                              Client Name: ABC

                              ------Sub Summary by File Number ---------

                              File Number: Filenumber Field   SummaryCountField



                              Note that there is no "body" part to the layout.

                              Using that set up, you'll get what you want except that you'll see the layout text "Filenumber:" to the left of each filenumber field. You can eliminate the repetitions two ways: Use conditional formatting, Set the layout text's vertical text alignment to "bottom" and resize the text so that the upper selection handles extend across the part boundary into the upper sub-summary part.

                              • 12. Re: Sub Summary Report



                                I'm looking for this:



                                File Number: 1111     [1]

                                                   2222   [1]  ... although behind the scenes there are [2] records for 2222.


                                Total number of Viewed File Numbers is [2]

                                • 13. Re: Sub Summary Report

                                  That explains the issue, but there's not a simple solution available. You want a count of all unique filenumbers for a given client. Your summary field won't do this for you as it will count either all the records in your table or all the records for a given client.


                                  I had to test this before I was sure it would work.


                                  I'm assuming that you have a table called "clients" and a table called "files" with a relationship such as:




                                  Define a new value list and call it "FileList".

                                  Specify Files::Filenumber as the field supplying the values.

                                  Click the "Include only related values starting from:" option and select "clients" from the dropdown list to its right.

                                  Now define a calculation field set to return number:

                                  ValueCount(ValueListItems ( Get(FileName) ; "FileList"))


                                  Use this calculation field in your report.