1 2 Previous Next 18 Replies Latest reply on Nov 19, 2010 10:49 AM by pdoak_1

    Combine Summary reports

    pdoak_1

      Title

      Combine Summary reports

      Post

      Background Info: I am using the following

      1. Filemaker pro Advanced 11
      2. Mac OS X 10.6.4
      3. Single user mode
      4. Just started using Filemaker

      I have two tables that summarise different data by client and then account.  I would like to combine the summarised data into a single report however I am having problems getting the desired results.  Whichever table I base the layout on, I can get the summarised data from that table but when I try and incorporate the summarised data from the other table it will only summarise by client and not by client and then account.  I have made sure that I have sorted both table by client and account but that does not seem to make any difference.

      Firstly, I assume that it is possible to achieve a combined report as described and, secondly, can anyone suggest a solution.

      Many thanks

      Layout.jpg

        • 1. Re: Combine Summary reports
          philmodjunk

          Much depends on how you've related your tables.

          Please describe the tables and how they are related.

          • 2. Re: Combine Summary reports
            pdoak_1

            I changed the relationship and now it works as expected.  Thank you.

            The report now correctly shows the summary by client and then account.  I would now like to add a trailing grand summary but I cannot get the grand totals from the other table to show anything - it just displays 0.  Is there a way of showing the grand totals from another table?

            Many thanks

            • 3. Re: Combine Summary reports
              philmodjunk

              Yes there's a way, but we'd need to know your current setup, the relationship between tables and the field definitions for your grand totals to figure out why it's not working for you. It may be necessary to add an additional relationship to get them.

              • 4. Re: Combine Summary reports
                pdoak_1

                Here are the links to images of my setup:

                Layout Setup http://dl.dropbox.com/u/2139659/Layout.jpg

                Layout Screen Shot http://dl.dropbox.com/u/2139659/LayoutScreenShot.jpg

                Relationship Diagram http://dl.dropbox.com/u/2139659/RelationshipDiagram.jpg

                Table http://dl.dropbox.com/u/2139659/Table.jpg

                In the layout Screenshot (the layout is based upon the TO trancapital_allocationdata_INSTRUMENTCOSTTABLE) the last 0.0 should be the sum of CashMovements.  This field is from trancapital_allocationdata_instrumenttable_ACCOUNTCASH3.  I had to set up the TO Accountcash3 in order to get the cashmovement summary by client and then account (this TO is related to the trancapital_allocationdata_INSTRUMENTCOSTTABLE by _kf_clientID and ClientAcct).

                If you need any further information, then please let me know.

                Many thanks

                • 5. Re: Combine Summary reports
                  philmodjunk

                  the last 0.0 should be the sum of CashMovements.  This field is from trancapital_allocationdata_instrumenttable_ACCOUNTCASH3.

                  I can't tell the name of this field. It should be trancapital_allocationdata_instrumenttable_ACCOUNTCASH3::CashMovementSum.

                  Is it? (It has to have both the correct TO name as well as the correct field name)

                  As a cross check, the calculation field: Sum ( trancapital_allocationdata_instrumenttable_ACCOUNTCASH3::CashMovement ) defined in your layout's table, should give you exactly the same result as this summary field.

                  I'm assuming your records and sub summary part are set up to group your records by both client and account so that the group of two records in your screen shot all have the same client and account IDs and that your second group of four records also all have the same client and account ID's -- though different from the first group.

                  • 6. Re: Combine Summary reports
                    pdoak_1

                    I can't tell the name of this field. It should be trancapital_allocationdata_instrumenttable_ACCOUNTCASH3::CashMovementSum.

                    It is.

                    As a cross check, the calculation field: Sum ( trancapital_allocationdata_instrumenttable_ACCOUNTCASH3::CashMovement ) defined in your layout's table, should give you exactly the same result as this summary field.

                    Sorry but I don't understand what you mean by this?

                    I'm assuming your records and sub summary part are set up to group your records by both client and account so that the group of two records in your screen shot all have the same client and account IDs and that your second group of four records also all have the same client and account ID's -- though different from the first group.

                    Yes, the two records in the screenshot have the same client and account IDs.  

                    The Layout part set up is by ClientAccount and the table is sorted by ClientID and then ClientAccount.  From your reply it sounds as though it should work the way I have it set up so I must have made a mistake somewhere.  What else could I check?

                    • 7. Re: Combine Summary reports
                      philmodjunk

                      Sorry but I don't understand what you mean by this?

                      It's just a different way of computing the same total.

                      Yes, the two records in the screenshot have the same client and account IDs.  

                      TWO records? What I see in your screen shot indicates that you should have 6 records, 2 in one group and 4 in another group. Is this what you have?

                      What else could I check?

                      Are there any other fields on your layout that pull data from this same TO? If they work and this field doesn't it suggests a problem I am not seeing with your field definition. If no field from this TO displays any kind of data, it suggests a problem with the relationship such that none of your records match--you'd need to check the relationship definition, the field types of the key fields and the actual data stored in your records on both sides of the relationship to see what isn't correct.

                      • 8. Re: Combine Summary reports
                        pdoak_1

                        TWO records? What I see in your screen shot indicates that you should have 6 records, 2 in one group and 4 in another group. Is this what you have?

                        Sorry there are six records for two combinations of client and account.

                        The CashMovementSum shows the correct figures by client and account but does not show anything when placed in the grand total part of the layout.

                        • 9. Re: Combine Summary reports
                          philmodjunk

                          Hmmm, The Grand Total part of the layout...

                          If you use the same relationship in a trailing grand summary part, that normally uses either the first or last record in the found set to match records in the other file (I forget which is the case) and that likely will not return a grand total, only the subtotal for that record.

                          I'd like to refresh my memory by looking at your layout and relationship graph but those links are now dead. Frown

                          • 10. Re: Combine Summary reports
                            pdoak_1

                            The files are still in my public dropbox - I just tried the links and they seem fine.  Please try again - it should work.  I have tried changing the relationship but it does not seem to make any difference.

                            • 11. Re: Combine Summary reports
                              philmodjunk

                              With each one, I am getting "Internet Explorer cannot display the web page..." Frown

                              • 12. Re: Combine Summary reports
                                philmodjunk

                                Tried again and now I can view them. I can see now that I was earlier looking at the wrong part of the layout.

                                You are getting 0.0 because this is the correct subtotal for the last group.

                                If you want a grand total for all the records in the related table, create a new table occurrence using the X operator to match to all records and use it to refer to this summary field.

                                If this is a "grand total" for a subset of the records, you'll need to set up a relationship that correctly matches to just those related records. I can't tell you how to set up that relationship, as I don't know the criteria.

                                If this grand total could represent many different subsets of data based on finds you perform on your report layout, you may need to define calculation fields that draw the individual values into your report layout's table so that a summary field in the report layout's table can compute the grand total.

                                • 13. Re: Combine Summary reports
                                  pdoak_1

                                  If you want a grand total for all the records in the related table, create a new table occurrence using the X operator to match to all records and use it to refer to this summary field.

                                  It is a grand total for all the records that I require.  I am not familiar with the X operator.  Please could you explain in a little more detail what I need to do using this new table occurrence.

                                  Many thanks

                                  • 14. Re: Combine Summary reports
                                    philmodjunk

                                    Open Manage | Database | relationships.

                                    Double Click a line linking two table occurrences.

                                    In the Edit Relationship Dialog that pops up, you can change the = operator that is the default to X or a number of other operators. This X operator matches all values to all values and thus will give you the grand total you've specified when you place the summary field from the other table on your layout.

                                    1 2 Previous Next