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

    Sub Summary Report

    user253

      Title

      Sub Summary Report

      Post

      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.

       

      Ex:

      File Number

      1111

      2222

      2222

       

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

        • 1. Re: Sub Summary Report
          TSGal

          Inaz:

           

          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.

           

          TSGal

          FileMaker, Inc. 

          • 2. Re: Sub Summary Report
            user253
              

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

             

             

            • 3. Re: Sub Summary Report
              philmodjunk
                 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
                user253
                  

                I must be doing something wrong.  : )

                 

                I'm still coming out with "3"

                • 5. Re: Sub Summary Report
                  philmodjunk
                    

                  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

                  1111

                   

                  Filenumber  count: 2

                  2222

                  2222

                  • 6. Re: Sub Summary Report
                    user253
                       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
                      philmodjunk
                        

                      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
                        user253
                          

                        This is what my report looks like:

                         

                        Sub-Summary:

                        Client Name: ABC

                         

                        Sub-Summary:

                        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
                          philmodjunk
                             Where are you putting your summary field?
                          • 10. Re: Sub Summary Report
                            user253
                               I tried it everywhere.
                            • 11. Re: Sub Summary Report
                              philmodjunk
                                

                              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
                                user253
                                  

                                Almost.

                                 

                                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
                                  philmodjunk
                                    

                                  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:

                                   

                                  clients::ClientID--=--Files::ClientID

                                   

                                  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.