4 Replies Latest reply on Oct 11, 2010 11:44 AM by RonRohlfing

    Reporting summary fields

    RonRohlfing

      Title

      Reporting summary fields

      Post

      I hope I'm not just missing something but here goes...

      Is there a way to have summary fields from multiple tables be shown on one layout?

      I have multiple records for a table with one or more summary fields in the table.

      I have multiple table with summary field(s).

      Can I report all the summary fields on one report layout?

      I hope this is clear.

      Thanks.

        • 1. Re: Reporting summary fields
          philmodjunk

          There's a lot of different set ups that fit your description. Some work and many do not. It depends on the relationships between the tables and the totals and subtotals you need to compute. Generally speaking, there's almost always a way to get this to work, but the devil is in the details here.

          If your table relationships look like this:

          Table1-----<table2----<Table3   where ---< means one to many.

          Then you can create a summary report based on table3 and draw related values from tables 1 and 2, placing them in sub summary parts, or other locations as needed.

          The less of a "straight line" linkage between your tables, the harder it is to set up a summary report. Some times you need to setup a sliding portal or even define a special report table into which you import selected records in order to simplify the table structure.

          Also keep in mind that if you place a summary field from one table on a layout based on another, the relationship that links the two tables will control what summary value is computed. A "total of" summary field, for example, will compute the total of the related records in the related table--which may or may not be the value you want to see in your report.

          • 2. Re: Reporting summary fields
            RonRohlfing

            I have had my tables related in a couple of different ways to try and make the report with the results I want.  I initially had my tables related like you have shown.  I had in issue where if not every table had a record then the relationships failed.  In your example, if table2 had no records, there was no relationship between table1 and table3.

            I changed my relationships to a star pattern using one field to make the relationship between tables, this way if a record doesn't exist for one table, it doesn't affect the relationship between my "main" table and any other table.

            table1>--------table2-------<table3     where table 2 is my "main" table

            If I had a summary field in table1 and another one in table3 and I wanted to report these two summary fields in table4, a completely separate table, is there a way to get the correct values from the summary fields?  I have tried a report table that is related to my "main" table and one that is not.  Still haven't be able to get what I'm looking for.

            I understand the relationship that links the tables together controls what summary is computed, just still working on figuring out how this is working.

            If worse comes to worse, I can go the brute force method and loop to get the sums that I need in a variable to put into a report table.

            I appreciate your help.  The more I see what FM can do the more I understand it's going to take awhile to understand it all.

            • 3. Re: Reporting summary fields
              philmodjunk

              If I had a summary field in table1 and another one in table3 and I wanted to report these two summary fields in table4, a completely separate table, is there a way to get the correct values from the summary fields?  I have tried a report table that is related to my "main" table and one that is not.  Still haven't be able to get what I'm looking for.

              Unless you define a relationship between table4 and the other two tables, the only way you can see these summaries would be by physically copying the data into fields defined in table4. If you do have a relationship, then you should see totals based on what records "match" via the defined relationship.

              • 4. Re: Reporting summary fields
                RonRohlfing

                Thanks, I'm now getting what I expect.