1 2 Previous Next 19 Replies Latest reply on Aug 5, 2011 1:28 PM by BarryFass-Holmes

    How best to develop a report of summary statistics?

    BarryFass-Holmes

      Title

      How best to develop a report of summary statistics?

      Post

      After reading about how self-joins can produce summary statistics (http://help.filemaker.com/app/answers/detail/a_id/3287/~/using-self-join-relationships-to-summarize-data-in-browse-mode), I'm stuck on whether they would be preferable to use in building the following report of summary statistics for an educational program for students experiencing academic difficulty.

      The relevant (student) data are in two tables related by ID—academics and demographics. The former can contain more than one record per student; the latter contains only one (unique) record per student.

      The data are organized in the fp7 by term and year (the field containing term and year is in the academics table);—fall 2010, winter 2011, spring 2011. As future terms are completed, students' data for those semesters will be imported into the fp7. So the report needs to be able to accommodate future data.

      The summary report additionally needs to include the following statistics in a single layout.

      semester/year

      # students eligible to participate in the program (demographics table; count of unique records)

      # who participated (demographics table; count of unique records)

      # who participated by college (demographics table; count of unique records)

      # who experienced academic difficulty type 1 (academics table; count of multiple records per student)

      # who experienced academic difficulty type 2 (academics table; count of multiple records per student)

      # who experienced academic difficulty type 3 (academics table; count of multiple records per student)

      If I use a self-join on either the academics or demographics table, one drawback is that the report's layout will display all of the records in the table. This probably will be an issue for the fp7's users.

      However, if I instead a build a new table that contains only one record for the report's layout, then the report no longer will involve a self-join. Instead, the report presumably will involve a relation between the new table and the demographics and/or academics table(s). In that case, how do I generate the above values?

      Many thanks in advance!

        • 1. Re: How best to develop a report of summary statistics?
          philmodjunk

          Is the "by colllege" count a single count or will you have a list of colleges with a count for each?

          The last three are made to order for summary fields and a layout created in Academics--no new relationships or new tables required.

          The first two look like values a script can generate and store in global fields for this report (avoiding the problems we had with "sum the reciprocal" in your system.)

          But I'm not sure what you want for the third item.

          How do you distinguish between the students who were eligible and those that particiapated? Perhaps a field in demographics?

          • 2. Re: How best to develop a report of summary statistics?
            BarryFass-Holmes

            Many thanks for your reply. Smile

            The report needs to display a list of the six colleges with a count for each.

            Do you mean that all of these statistics can be handled in a single layout? Or will one layout be necessary for some, another for the remainder?

            For the last three, if I use summary fields on a layout with context in the academics table, then the layout will display as many records as there are in that table, correct? If so, need a different solution. It will be an issue for the fp7's users if they can access multiple records in that layout.

            For the first two, if I build a script, it needs to avoid the same issue (multiple records displaying in the layout).

            All students who have a record in the demographics table are eligible to participate in the program. This value will change over time, however; a student could be eligible one term and ineligible the next.

            A field in a third table indicates whether a student actually participated in the program (participation is voluntary, and to date the percentage of eligible students who participated unfortunately is very low).

            After rereading your reply, I'm still fuzzy on how best to proceed.

            • 3. Re: How best to develop a report of summary statistics?
              philmodjunk

              Things are still fuzzy here as well...

              Is "college" something assigned to each student in demographics? If so, is the name stored or just the link to a "colleges" table?

              For the last three, if I use summary fields on a layout with context in  the academics table, then the layout will display as many records as  there are in that table, correct?

              No, you would not have to have one row of data for every record in the table nor for every record in the found set. Sub Summary layout parts with no body layout part can "condense" multiple rows of data down to just one row for each difficulty type based sub total that you need.

              By using a script to produce your "counts" and storing the totals in a global field for each, you will not have any layout issues based on the number of records in the table nor in the current found set. You can place global fields in the header, footer or a leading/trailing grand summary part.

              The eligible students part looks like what we resolved in the previous thread. You can find the demographics records, use go to related records with the match found set option to pull up the matching records in demographics, then use set field to load this value in a global field.

              The participating students might be handled in a number of ways. What does the relationship to this related table look like? To what table (Demographics or academics?) is it directly linked?

              • 4. Re: How best to develop a report of summary statistics?
                BarryFass-Holmes

                Yes, each student has one of six colleges' names in the "college" field in the demographics table. There is no "colleges table."

                Regrets, but I'm still fuzzy on how to make a layout with context in the academics table that does not display as many records as there are in the academics table. The reason is that, before posting this thread, I built such a layout—the toolbar displayed the same number of records as there are in the academics table, and after changing from layout to browse mode the layout displayed as many instances of the fields and labels as there were record ~170). That's what led me to post here for some advice rather than spinning my wheels. Frown

                The academics table is related to the demographics table with ID as the key field, the third table is related to the demographics table with ID as the key field.

                • 5. Re: How best to develop a report of summary statistics?
                  BarryFass-Holmes

                  OK, my last posting was incorrect.

                  I just repeated the process—created a new layout with context in the academics table, made a subsummary part, set its background color to blue, clicked the "sub-summary when sorted by…" radio button in the Part Definition window, selected the academics table from the popup menu, selected ID as the field to sort on, clicked OK, placed the term/year field (from the academics table) on the layout with the font set to black, switched to Browse mode, and nothing displayed; not even the blue background (instead, the Browse view was white).

                  I obviously am doing something wrong…

                  One more thing—as mentioned above, each of the statistics for this layout will correspond to various terms/years. How do I configure the layout so that each term/year that is in the academics table displays in a separate row along with its corresponding statistics?

                  Here is a rough representation.

                  term/year # students eligible # participants … (other stats' names)

                  FA10 97 3

                  WI11 81 2

                  SP11 82 1

                  FA11 TBD TBD

                  and so on

                  • 6. Re: How best to develop a report of summary statistics?
                    philmodjunk

                    Let's start with a summary report that just counts the academics records for each "difficulty type" to clear that issue out of the way. As I rethink what you've posted, I'm not sure that this approach will work for you as I think you want to count the students, instead of their academics records, but at least you'll learn how to do what can be a very useful type of report.

                    Define a "count of" type summary field in academics. Set it to count an ID field or other such field that is never empty.

                    On a layout based on academics, enter layout mode and double click the "body" layout part label to bring up a dialog where you can change the "body" into a sub summary part. Select your "diffculty type" field as its "when sorted by" field. Put the summary field and the difficulty type field in this sub summary part. (You'll be asked if you want this sub summary part is to be "print above" or "print below". For this layout choose either and you'll get the same result.)

                    Enter browse mode and sort your records by the difficulty type field and you'll get three rows of data with the subtoals for each of the three difficulty types. You won't see any data from individual records as we have replaced the body layout part with a sub summary part. Yes, the status area will still show the total records in your table and found set, but this won't show on the report itself--so this should not be an issue.

                    Since this counts academics records, It doesn't accurately count students--which is where I think you'll have a problem with this method unless I am misunderstanding what you need here. In my database, this is where I'd add the needed fields to setup a sum the reciprocal method to count the number of students in each category. (Note different sets of sum the reciprocal fields can, in theory produce the needed totals for each of the other "unique counts".)

                    In any case, this will still leave the break down by College part of your report missing. I'll wait to here back from you on the above part of your report before tackling that issue.

                    • 7. Re: How best to develop a report of summary statistics?
                      BarryFass-Holmes

                      Thanks for your posting.

                      Before proceeding, I'd like to confirm that the report does need to count students rather than academic records. Here's why.

                      Any given student could have a record for academic difficulty type 1 in term/year WI11 and another record for academic difficulty type 2 in term/year SP11.

                      If the report counts academic records, this student would be counted twice and would lead the fp7 user to overestimate how many students were eligible to participate in the educational program.

                      Instead, the report needs to count students so that when considering the population of eligible students for any given term/year, this student will be counted only once.

                      Does that make sense? If so, do you still want me to proceed with the steps in your last posting, or should I perform other one(s)?

                      • 8. Re: How best to develop a report of summary statistics?
                        BarryFass-Holmes

                        Curiosity got the better of me, and I proceeded with the steps in your last message rather than waiting for your reply to my last posting.

                        1. The value in the field indicating the difficulty type is incorrect.

                        2. Only one row of values displays.

                        I obviously have done something wrong…again!Frown

                        • 9. Re: How best to develop a report of summary statistics?
                          philmodjunk

                          I'm not sure how you want to break down the count by type of difficulty when the same student has academic records that makes them eligible in more than one "difficulty type" category.

                          • 10. Re: How best to develop a report of summary statistics?
                            BarryFass-Holmes

                            Thanks for your reply. OK, my goof. Try again.

                            As mentioned in my original posting, the report needs to display the following statistics.

                            semester/year

                            # students eligible to participate in the program (demographics table; count of unique records)

                            # who participated (demographics table; count of unique records)

                            # who participated by college (demographics table; count of unique records)

                            # who experienced academic difficulty type 1 (academics table; count of multiple records per student)

                            # who experienced academic difficulty type 2 (academics table; count of multiple records per student)

                            # who experienced academic difficulty type 3 (academics table; count of multiple records per student)

                            So, the first column of the report would be term/year, the second would be # students eligible to participate in the program, and so on.

                            In the report's row for FA10, a student with academic difficulty type 1 would be counted once.

                            If that same student experiences the same academic difficulty in WI11, (s)he will be counted once in the row for WI11.

                            If that same student experiences a different academic difficulty in WI11, (s)he will be counted once in the row for WI11.

                            However, the student is counted only once per term/year, even though that student has two records in the academics table. A grand total would count the student only once, even though that student has two records in the academics table. Because it's the same student and we're counting unique students.

                            Hope that makes better sense than my last posting!

                            Assuming it is, again, the layout is displaying the following problems.

                            1. it displays as many records as there are in the academics table, and I can scroll through the records (this will pose a problem for the fp7's users)

                            2. it displays only one row (FA10) rather than three (one for FA10, one for WI11, and one for SP11)

                            3. the fields' values change while scrolling through the records

                            • 11. Re: How best to develop a report of summary statistics?
                              philmodjunk

                              Please note that I've alread posted that this approach, as described, would not work as described if you needed to count the students rather than the academics records.

                              Also, you now indicate that you want this report organized into columns with one row for each term/year. This is a layout design that is not compatible with this approach.

                              That said, I'll reply to your questions just to help you understand how what I described was supposed to work.

                              1) With this layout, scrolling through the records would not be an issue as you will be unable to see the individual records, only the subtotal summary values

                              2) Properly sorted with properly defined sub summary parts, you'll get three rows - one for each difficulty type, but remember that they don't count exactly what you want to see counted.

                              3) If these are in a sub summary parts of a report who's found set was first set up by performing a find, the data would not change when you scrolled through them.

                              I think I see a way to do this with a completely different approach. It entails adding a report table with one record for each term/year and lots of relationships linking to your other tables/records. Please note that this "cross tab" style report takes a lot more effort to create and is much less flexible than a standard summary report.

                              I want to play with a demo file before I try spelling out the details here. So I'll do that and report back either later today or Monday...

                              • 12. Re: How best to develop a report of summary statistics?
                                philmodjunk

                                A follow up question you can answer while I play with my demo files:

                                How would you like to see the "By college" subtotals displayed?

                                Are these additional columns of data in the report? (6 colleges = 6 columns with a count in each?)

                                • 13. Re: How best to develop a report of summary statistics?
                                  BarryFass-Holmes

                                  "Cross tab"—I've intentionally avoided using that term in this thread because

                                  1. cross tab reporting in FileMaker Pro (http://ist.mit.edu/sites/default/files/migration/usergroups/filemaker/fmug/pres/crosstab.pdf) is different from statistical crosstabs (http://www.spss.co.in/chapters.aspx?id=11); and

                                  2. as mentioned in my last post, a grand total is going to double-count students with multiple records and a cross tab is going to include a grand total (thus, this will be problematic)

                                  To clarify, managerial decisions about this educational program need to be based upon numbers of unique students, not numbers of academic records. Serving one student three times in a given academic year (three academic records) is programmatically different than serving three students once each during a given academic year (also three academic records).

                                  Perhaps what this report needs to do simply is beyond FileMaker Pro's scope.

                                  To answer your follow-up question, would you be OK with my sending you a private message that will include a link to a graphic image that I will post to my web site?

                                  I think it will be clearer to answer your question pictorially than in writing.

                                  • 14. Re: How best to develop a report of summary statistics?
                                    philmodjunk

                                    Regardless, what you have described is a "cross tab" type of report where columns of data are derived from different records or groups of records in the database. This can be done in FileMaker and the grand total type student counts (which really aren't grand totals here) will not be an issue.

                                    The format is just more work to set up and less flexible than a straight forward summary report so you need to be sure that this report is really worth the effort to set up and maintain. (And when I've worked with cross tab reports in other database systems, they may have had wizards to make setting up the report easier, but they had much the same flexibility issues once you used a report to present the data instead of a simple table view.)

                                    1 2 Previous Next