9 Replies Latest reply on Aug 15, 2010 10:25 PM by mayor

    Generate report grouped by year, month

    mayor

      Title

      Generate report grouped by year, month

      Post

      Hello,

      I got 2 tables called student & registration

      Student (name, id_no, address……..)

      Registration(reg_date, month_reg,  year_reg…….)

      I have setup fields as below:

      month_reg = MonthName(reg_date)

      Year_reg = year(reg_date)

       

      I want to generate the report  grouped by YEAR & MONTH using reg_date as example below

       

                      Name                    Address                             Reg_date

      2010

                      January

                      John                      Penang Street                   2.1.2010

                      Michel                  Kuala Lumpur                    5.1.2010

       

      February

                      John                      Penang Street                   23.2.2010

                      Michel                  Kuala Lumpur                    15.2.2010

       

      Please, advise me.

      TQ

        • 1. Re: Generate report grouped by year, month
          philmodjunk

          Do you know how to create summary reports?

          You'd need to create two subsummary parts, one for Year_reg and specify Year_reg as the break or "Sorted by" field and another subsummary part for month_reg with month_reg specified as the break field.

          Then you'd sort your records by Year_reg first, then by month_reg to group your records first by Year and then by month within the year.

          Note: with versions of filemaker older than filemaker 10, the resulting report is only fully visible in preview mode or when you print the report.

          Here's a tutorial on summary reports that may help you get this working:  

          Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: Generate report grouped by year, month
            mayor

            I have created 2 subsummary parts for year_reg & month_reg, I got the form of the report. But, the data display only month JANUARY for all my records in my database, although the particular reg_date is FEBRUARY.

            For your info, I use FMP ver 9.

            Please advice.

            TQ

            • 3. Re: Generate report grouped by year, month
              philmodjunk

              Are all your records in your found set or just January records?

              How have you sorted your records?

              What "sorted by" field did you specify for the subsummary part where you located this field?

              • 4. Re: Generate report grouped by year, month
                mayor

                All records in found set are pun under JANUARY. If the starting record is April, all the records in found set

                are pun under APRIL.

                I sorted the records by year_reg, month_reg and register_date

                On subsummary parts, I used field year_reg and month_reg

                I have try to set the layout to read data from table student or registration, its still doesn't work.

                Please help!

                • 5. Re: Generate report grouped by year, month
                  philmodjunk

                  What's the relationship between registration and student? Is it:

                  Registration::StudentID = Student::StudentID ?

                  With that relationship, you should be able to use a layout that refers to registration in Layout Setup... and then be able to put fields such as the student's name in the body part of the layout.

                  "If the starting record is April, all the records in found set are pun under APRIL."
                  I can't tell for sure, but that sounds like the layout part where you've put the month name field isn't a subsummary part, but is instead a header or grand summary part. Check to make sure that the field doesn't cross or touch the boundary line between the sub-summary part and any header or grand summary part.

                  By the way, instead of using a Month name field, you could just put the registration date field on your layout and format it to only show the name of the month. Also, what I've suggested should group your records by month name, but will sort those groups into alphabetical instead of calendar order.

                  To get them in calendar order, you could define a value list of month names in calendar order and refer to it as custom sort order or you could define this calculation field and sort by it. It takes the registration date and returns the date for the first day of that same month so that all records with dates from the same month have exactly the same month. This is a trick I learned from Comment--another member of this forum:

                  define cMonth as a calculation that returns date: reg_date - day ( reg_date ) + 1

                  Sorting your records by cMonth will group your records by month, sorted in Calendar order.

                   

                  • 6. Re: Generate report grouped by year, month
                    mayor

                    Yup, for relationship, I use studentID and set it as TEXT. If I set that key field as NUMBER, is it will give different result?

                    I have tried what you have recommend to me, thanks VM, but still give the same result. I have checked the year_reg and month_reg fields, its doesn't cross or touch the boundary line between the sub-summary part.

                    I have no idea how to resolve this, anyway, I will keep trying to get what I meant.

                    I have try to create cMonth and set that field as mentioned and sort by cMonth, but not sure what you mean by define a value list of month names in calendar order.

                    Thanks

                    • 7. Re: Generate report grouped by year, month
                      philmodjunk

                      I'm talking about how to sort your record groups correctly.

                      I'm talking about the difference between sorting by MonthName as apposed to a date field such as cMonth.

                      If you sort by MonthName, your records will be sorted like this:

                      April
                      February
                      January
                      July
                      June
                      March
                      May

                      In alphabetical order.

                      If you sort by cMonth, you'll get:

                      January
                      February
                      March
                      April
                      May
                      June
                      July

                      In Calendar order--which is what I belive you'd want in this type of report.

                      The third option is to define a value list of month names--which you may already have--listed in the same order as they are listed in a calendar. When you sort your records, you can use the "custom order based on value list" option with this value list to sort your records by Month Name, but in calendar order instead of alphabetical.

                      • 8. Re: Generate report grouped by year, month
                        LaRetta_1

                        All of this would be unnecessary if one simply creates a calculation cMonthDate of:

                        Date  - Day ( Date ) + 1

                        ... which turns all dates into generic first day of month (or better phrased, turns all dates into their month).

                        Then sorting will always work properly, as will sub-summaries based upon month, as will display of month/year for reports (handle it from date format layout level).

                        It also is used for portal filtering by month/year and relationships.  Having the year separate from the month will never provide the full range of functionality that using a TRUE date will provide.

                        • 9. Re: Generate report grouped by year, month
                          mayor

                          Oh my good! At last I got what I want. Thanks soo much  PhilModJunk for your kind of advice. It works...

                          I feel dumb to get the report in order, but I got it.