10 Replies Latest reply on Apr 7, 2013 4:08 AM by davehob

    Sub-summary report analysis

    davehob

      I'm creating a subsummary report of scores for various outcomes, and so far so good. I'm sorting the data by outcomes and people to give me, for example:

       

      Outcome - "Self-confidence"

       

      Fred

      1/2/2013 - score 1

      1/3/2013 - score 3

      1/4/2013 - score 4

       

      Jane

      1/2/2013 - score 3

      1/3/2013 - score 2


      Outcome - "Taking Responsibility"

       

      Fred

      (etc.)

       

      The next step is a summary part for each outcome to say how many of the people achieved an increased score in the data reported. So, in the example, Fred would have increased, but Jane wouldn't. I've been trying to achieve this with min and max summary fields, but can't work how to combine these values with their dates to ask "is the oldest score greater or less than the newest score?".

       

      If you have any suggestions, I'd be very grateful. (FM11)

       

      Dave.

        • 1. Re: Sub-summary report analysis
          taylorsharpe

          Too bad you don't have FM 12 to utilitize ExecuteSQL.  But you can still do it with normal relationships. Here it is with just relationships. Unfortunately I don't have FM11 on this laptop, so I hope you have 12 to open it up and see it.  

           

          Outcomes.jpg

           

          Fields.jpg

           

          map.jpg

           

          Note that the relation shiop to Max and Min are done with a sort so that the date sorts ascending for min and descinding for max. 

           

           

           

           

          dd

          • 2. Re: Sub-summary report analysis
            davehob

            Taylor,

             

            That's just great.  I do indeed have FM12, so I've had a look at your sample file, and it's just the job.  I haven't yet built it into the solution that I'm working on, but your example explains very clearly the idea of looking only at the first related record to get the score on the earliest/latest dates. 

             

            I think I'll have to do some tinkering to make it work with a found set, rather than the whole table, but I'm sure this is going to make such a difference.

             

            Thanks so much for your help - I really appreciate it.

             

            Dave. 

            • 3. Re: Sub-summary report analysis
              wimdecorte

              Another approach is to use GetNthRecord to loop through your found set without actually moving through it and collect / aggregate the data that way, using "named bucket variables" (repeating variables whose repeat # is the name of the person).  When all the data is collected you dump it into a scratch table or a web viewer to present it to the user.

              This way you don't have to burden your design with slow summary fields and extra TOs and relationships.

              1 of 1 people found this helpful
              • 4. Re: Sub-summary report analysis
                davehob

                Taylor,

                 

                Your suggestion works fine in my solution, and thanks again for sharing it. However, I’m now having difficulty with limiting the analysis by date range.  I’m using global date fields to create a found set of outcome scores, and need the status to reflect the scores in the found set, rather than the whole table.  I’ve tried various ways, including adding the dates to the “max” and “min” relationships, but to no avail - the status still reflects the whole table, rather than just the recs in the found set.

                 

                I’m sure I’m missing something simple.  Can you point me to where I’m going wrong?

                 

                Dave.

                • 5. Re: Sub-summary report analysis
                  taylorsharpe

                  The relationship in my example tied the max and min tables via the Outcome and Name and not by the found set.  There is no such relationship that can be done in the relationship graph based on a found set unless you code a field in the found set.  But if you're going to do that, you'll need to run a script.  If you're running a script, why not just have it loop through and determine the max and min you want and store them for each person for that report.  The limitation is that the results are not dynamic and are only accurate when you run the script.  But that would do what you are looking for.  Does that make sense?  Do you need an example script?

                  • 6. Re: Sub-summary report analysis
                    davehob

                    Taylor,

                     

                    Thanks again - and yes, I would love an example of how this should be done.

                     

                    I am already running this analysis via a script, which basically does this:

                    1. Collect the criteria (date range, outcome type, etc.)
                    2. Find the relevant Outcome recs
                    3. Go to the analysis layout
                    4. Sort the found set to create the subsummaries.

                     

                    ... and it seems to work fine so far.  So I guess I now need to write the values required to a “scratch table”, as suggested by wimdecourt?

                     

                    If you have time to show me how this would work with your example file, I would be (yet again) very grateful.

                     

                    Regards,

                     

                    Dave.

                    • 7. Re: Sub-summary report analysis
                      taylorsharpe

                      Personally, I would just change the calcultion field that calculates that status and change it to a text field and have the script fill in the Status.  If this is a simple database where multiple people won't be running this script at the same time, this will work good enough. 

                      • 8. Re: Sub-summary report analysis
                        LSNOVER

                        The ExecuteSQL function is a great tool.   As noted the inability to easily limit queries to the found set is a bit of an issue in the scope of Filemaker.  I'd encourage everyone that cares to petition FMI to provide us a List command that works in the scope of the Layouts main table.  This would be a great help in the scope of traditional Filemaker scripts as well as with ExecuteSQL and also when passing a reference to Found set records back to a SQL database being used by ESS. 

                         

                        This feature has been requested for literally years, but never quite seems to make the cut.  PLEASE FMI? ;-) 

                        • 9. Re: Sub-summary report analysis
                          timwhisenant

                          Lee et. All,

                           

                          Kevin Frank addressed this shortcoming in one of his articles …. http://www.filemakerhacks.com/?p=1065

                           

                           

                           

                          Maybe this will help add to the SQL Tool belt,

                           

                          Tim

                          • 10. Re: Sub-summary report analysis
                            davehob

                            Thanks for this very helpful response.  I wasn't aware of the potential for using repetition in this way - it'll be really useful for all sorts of "working storage" situations.

                             

                            Dave.