1 2 Previous Next 22 Replies Latest reply on Mar 12, 2012 9:40 AM by comment

    Summary Reports with Many Variables

    rmark

      I am creating reports with summary statistics of about a hundred vatiables (e.g. E01-E99). First I have to create the summary variables (S01-S99) one at a time. Then I need to set up the subtotals in the wizard one at a time, even though they are all alike. The grand totals have to be reentered the same way, one at a time. This is hours of needles work. Surely, there must be a better way. Why can't we select a range of variables to be treated alike? Why not a button on the grand total dialog that says "same as subtotals"?

        • 1. Re: Summary Reports with Many Variables
          comment

          Robert Mark wrote:

           

          Surely, there must be a better way.

           

          There probably is. Could you describe the data you have and the way it needs to be summarized in more detail?

          • 2. Re: Summary Reports with Many Variables
            rmark

            In this case, I have about 2000 petroglyph panels in 25 clusters. Typical panels contain between 1 and perhaps 50 glyphs. The data are counts of different glyph types (almost 100 types) for each panel. I would like subtotals for each type by cluster, and grand totals.

            • 3. Re: Summary Reports with Many Variables
              comment

              Could you make it a bit easier for those of us not familiar with the subject? So far, I see:

               

              Clusters -< Panels -< Glyphs

               

              Now, when you say you have "counts of different glyph types for each panel" - do you have a record for each such count? For example:

               

              PanelGlyph Type
              Count
              001X20
              001Y25
              002X18

               

              If so, you only need one summary field and two sub-summary parts to get "subtotals for each type by cluster, and grand totals".

               

              If not, you'll probably need to reorganize your data first.

              • 4. Re: Summary Reports with Many Variables
                rmark

                I have a record for each panel. The record contains the count for each of the glyph type (E01-E99).

                I need a report with glyph type columbs, and panel number rows, sorted by cluster. Subtotals by cluster (S01-S99).

                • 5. Re: Summary Reports with Many Variables
                  comment

                  If you mean your table has 99 fields, one for each glyph type, then you indeed need to restructure your data. Or add 99 summary fields.

                  • 6. Re: Summary Reports with Many Variables
                    rmark

                    I have already created 100 summary fields, one at a time. There should be away to automate this step.

                    • 7. Re: Summary Reports with Many Variables
                      comment

                      I am afraid I cannot agree with you. The real issue here is that your data is not normalized. No one should need to create 100 summary fields - and FMI programmers should not be spending their time assisting those that insist.

                      • 8. Re: Summary Reports with Many Variables
                        rmark

                        I'm sorry you don't appreciate my requirements. The glyph catagories will ultimately be combined into a smaller number, but we must understand the distribution before we do that.

                        I see no reason why the variables have to be selected one at a time to include for subtotals. And even more annoying, why it must be done all over again for grand totals.

                        I understand that most application don't have this many variables, but that is the nature of our science. FileMaker Pro seems to make this unnecessarily difficult. Certainly it should be simple to duplicate the subtotal structure for grand totals.

                        • 9. Re: Summary Reports with Many Variables
                          comment

                          Robert Mark wrote:

                           

                          I understand that most application don't have this many variables

                           

                          I don't think there's anything wrong or unusual with your requirements. The problem, as I've been trying to say, is that you have organized your data in a "spreadsheet way" instead of in a "database way" - and Filemaker is a database, not a spreadsheet.

                           

                          A database makes it very easy to have as many variables as you want, without requiring a change in your schema. The way you have it now, adding a new type requires adding a new column. In a properly structured database, adding a new type is a data operation, not a schema change. You would simply add a new record to the Types table, and enter the related counts for each panel. It's handled he same way whether you have 9 types, 99 types or 9,999 types.

                          • 10. Re: Summary Reports with Many Variables
                            jlegasey

                            If I understand the requirements of the study you are undertaking, you want to talk about glyphs. How many glyphs are of this type and how many are of that type. And where do you find the glyphs in the Panels. And are there clusters of types across panels and within panels. If there was a record for each glyph, you could create very powerful summary reports. Each record would have a field for the variable type, a field for the panel, and whatever else you want to say about a particular glyph. The summary data will then "count" for you.

                            • 11. Re: Summary Reports with Many Variables
                              rmark

                              There are anywhere from a few to a few hundred panels per cluster (25 clusters). The data is collected on a per panel bases (counts of glyph types per panel). One record per panel. There are a total of about 8000 glyphs on 2000 panels. There are about 100 glyph types. Some panels have a single glyph, some as many as perhaps 50 or more.

                              • 12. Re: Summary Reports with Many Variables

                                Robert Mark wrote:

                                 

                                FileMaker Pro seems to make this unnecessarily difficult.

                                 

                                Hi Robert,

                                 

                                When designing becomes exceedingly difficult, it usually indicates a structure problem.  FileMaker isn't making this unnecessarily difficult, I'm afraid ... you are.  Michael is absolutely correct that those 99 should be records and not fields.  It is wise to listen to the veterans who do this for a living (and have for more than a decade or two solving problems such as these).  It isn't a judgement thing ... it is simply the correct thing. 

                                • 13. Re: Summary Reports with Many Variables
                                  rmark

                                  The data is collected in the field by panel, and there is one record per panel.

                                  • 14. Re: Summary Reports with Many Variables
                                    BruceRobertson

                                    That is the "parent" record.

                                     

                                    Because there is one PARENT record, that does not mean that all the data associated with that record needs to be defined as fields within the same record. That is the whole point of relational database design, which it seems you have not quite grasped yet. One-to-many and many-to-many data relationships.

                                     

                                    I'm joining the chorus. You have a data structure problem.

                                     

                                    Listen to LaRetta; and Michael. They know what they're talking about. Sometimes they even think I know what I'm talking about.

                                    1 2 Previous Next