1 2 3 Previous Next 41 Replies Latest reply on Nov 10, 2010 4:33 PM by philmodjunk

    Help creating summary report

    Sunny1

      Title

      Help creating summary report

      Post

      Hello there.

      I need to create a summary report that will total all participants in a particular category by grade.  For example:  It should look like a grid with grade levels (6 Grade levels) going down the left side and categories (7 Categories) going across the top.  The body of the grid will contain the number of students from each grade level in each category. 

      I have a table of "Students" and a table of "Projects".  Projects has the category and grade and Students has the category and grade also.

      Do I need another table to do the summary from?  I need help on creating this report, so hopefully you can help.

        • 1. Re: Help creating summary report
          philmodjunk

          This type of report format:

          Grade 12
             Project A:   5
             Project B:   6
             PRoject C:  3

          Grade 11
             Project A: 3
             Project B: 2
             Project C: 1

          Etc.

          Is much easier to create in FileMaker than the "grid" you describe. Please confirm that you really need the grid.

          Also, what version of FileMaker are you using?

          You can get your columns with portals, but they can be a little bit easier to set up using portal filters in FileMaker 11.

          • 2. Re: Help creating summary report
            Sunny1

            I have File Maker Pro 11. 

            When I say grid, I only mean it looks like a grid or spreadsheet.  In your example, can we make it read across and down

            ie:  Grade 1                    Grade 2                     Grade 3                Total

             Project A:  5                       8                             7                         20

            Project B:   2                       0                             5                           7

            Total           7                      8                             12                        27

            • 3. Re: Help creating summary report
              philmodjunk

              That's my point, the format you specify can be done but takes a lot more setting up to create and maintain than the classic summary report format I gave as a counter example.

              • 4. Re: Help creating summary report
                Sunny1

                Well, personally, I don't see much difference than the previous summary that I have for my enrollment counts.  So that method will probably suffice.  I am doing this as a favor to someone else and just going by what they gave me. The previous method, I just used it on a tab control not a report so I am not sure how to relate it.

                Do I create a whole other table for counts and such?

                • 5. Re: Help creating summary report
                  philmodjunk

                  Assuming you have this relationship between your two tables, you can do it this way in FileMaker 11:

                  Students::ProjectID = Projects::ProjectID

                  Define a summary field, sStudentCount that is the "count of" a nonblank field in the table such as ProjectID

                  Create a list view layout on a Projects based layout with the project name field in column 1. Place one single row portal to Students for each of the remaining columns you need for your report. Put sStudentCount as the sole field in each portal row. Since each portal will be identical except for their filter expressions, you can set up the first one and then copy it for each of the remaining columns double clicking them to open Portal Setup... where you can specify the appropriate filter.

                  In the first portal you'd create a portal filter expression such as: Students::Grade = 1

                  In the last column, don't use any filter so that you will see the total for that row.

                  Hmmm, now that i've written that out, using FileMaker 11, makes this a much simpler approach than that required for older versions where you'd need a different special relationship for every portal...

                  • 6. Re: Help creating summary report
                    Sunny1

                    In using your example, Yes, I do have a relationship with ProjectID which is the auto serial number that I am creating in the background.  But I had to substitute Project Category for the Project Name field in Column 1 since we want to have totals of students by grade in each category.  So, I created a category field in the student table and added the relationship to Projects. But I am not getting any numbers in the boxes.  What have I done wrong...any ideas?

                    As a list view layout, if we have 10 categories but only three have been used in this example, do I need 10 rows or just three? 

                    • 7. Re: Help creating summary report
                      philmodjunk

                      Let's check some basic assumptions before we get any further.

                      In Students you have one record for every student.

                      In Projects, do you have one record for every student project or one record for every project category? I assumed the latter but it now sounds like every time a student starts a project, you create a record for it in Projects.

                      • 8. Re: Help creating summary report
                        Sunny1

                        Yes, in students I have one record for each student.

                        In Projects, I have one project but could have more than one student. In Projects, I have one category for each project.  But a project titled Project 2 can be category BIO and Project 3 can be category BIO but they each have a separate Project ID.

                        Does that help?

                        • 9. Re: Help creating summary report
                          philmodjunk

                          Just thought of another question where I might assume wrong and mess this up.

                          Are all students assigned to a given project the same grade level? (Split grade level classes come to mind here.)

                          • 10. Re: Help creating summary report
                            Sunny1

                            yes, there are no split grade levels.  Each 8th grader is doing an 8th grade project. 

                            Also, I just learned that while we made the Project ID number consist of the Category, the grade and the auto serial number, I found out that they want it to be the Category, the grade level and the auto serial number within that category.  IE:  BIO07001, BIO07002, BHS07001, rather than BIO07001, BHS07002, BIO07003.

                            • 11. Re: Help creating summary report
                              philmodjunk

                              Project ID should strictly be a serial number that you don't let your users see. You can then add such a field to your projects table for labeling purposes, but when they decide they need to change it like this, it won't break all the links to your student records. Wink

                              OK, we can work with that.

                              Keep the original ProjectID based relationship between projects and students.

                              If you don't already have it, define a calculation field, cStudentCount, in Projects as Count ( Students::ProjectID ). This computes the number of students per individual project.

                              Define a summary field sStudentTotalCount, as the total of cStudentCount. We can use this field to compute total counts within categories.

                              Now we need a new relationship for our portals.

                              Go to Manage | Database | Relationships and drag from the Projects::Category field to outside the box and then back to this same field. This creates a new table occurrence to Projects and relates them by category. You can rename this occurrence from Projects 2 to ProjectsSameCategory.

                              Now select Students and click the button with two plus signs to make a second occurrence of this table. Connect it to ProjectsSameCategory by ProjectID. We'll call this one StudentsSameCategory.

                              Now create a new layout based on Projects, enter layout mode and double click the label for the body layout part. Change it to a sub summary part when sorted by Category. Place the category field as the first field in this sub summary part. then place your portals to the right of it. Make each portal filter an expression that compares a value like 5 to Grade level in StudentsSameCategory to filter out all values except for grade 5 students.

                              Here's a demo file you can look at:  http://www.4shared.com/file/pWRM5Hjy/StudentProjects.html

                              • 12. Re: Help creating summary report
                                Sunny1

                                Earlier in our creation emails, you had me create a summary field in Projects to count Project IDs.  Am I no longer using that field?  Also, in this recent email...I am confused...you say to create a cStudentCount calculation field in Projects as Count.  Is the name cStudentCalculation or Count.

                                • 13. Re: Help creating summary report
                                  philmodjunk

                                  My earlier post was based on incorrect assumptions about what kind of data was stored in Projects. Please disregard.

                                  cStudentCount is the field name I suggested. Count ( Students::ProjectID ) is the calculation expression you should enter into it's field definition.

                                  If you download the demo file and check it out, you'll find this all set up there so you can see in greater detail how this might be set up in your database.

                                  • 14. Re: Help creating summary report
                                    Sunny1

                                    Where to find the demo file?

                                    1 2 3 Previous Next