11 Replies Latest reply on Feb 25, 2013 9:56 PM by BeachedWhale

    Grouping by Month

    BeachedWhale

      Title

      Grouping by Month

      Post

           This shouldn't be a problem which is why I'm stumped. All my records have date processed. I created 2 new fields, one for month and one for year. I then created a part defined layout where the subsummary fields are month and year. The subsummary amounts should appear when I sort by month. Normally when I sort by month, it groups the months together. Now when I sort, they don't separate into groups and then when I click the layout display, only one month shows up. What am I doing wrong?

        • 1. Re: Grouping by Month
          philmodjunk

               Make sure that you are in list view, not form view.

          • 2. Re: Grouping by Month
            BeachedWhale

                 Okay, I made sure it's in list view and it still has the same problem. It won't display all the months, only december if I do the whole year. What else should I try?

            • 3. Re: Grouping by Month
              BeachedWhale

                   And actually it's not even working in table view. Noramally I can do subsummary in table view as well and add trailing groups based on a field. But it won't work for month, it just outputs a grand summary at the bottom of all records, instead of for each month.

              • 4. Re: Grouping by Month
                philmodjunk

                     Are all the desired records in your found set?

                     This type of report is based on a found set of records correctly sorted by the "break" field specified in the sub summary layout part.

                     This may require performing a find or doing a Show All Records followed by sorting the resulting found set of records to group them first by year, then by month.

                • 5. Re: Grouping by Month
                  BeachedWhale

                       Yes I think so, in the table view, all the months and years show up fine. It just won't "break", like the break field isn't working. I tried it on a found set of records all from the same year, so the sort shouldn't be an issue, right? And I also sort the records by ascending month in addition to the break and that doesn't help either. :(

                  • 6. Re: Grouping by Month
                    philmodjunk

                         And I also sort the records by ascending month in addition to the break and that doesn't help either.

                         Huh?

                         The month IS the break field. This should be the field specified in the "when sorted by" setting for the sub summary layout part.

                         Your layout design should be structured like this:

                         -----Sub Summary by Month-----

                         Date fields, month, year fields can go here. put summary fields here for sub totals

                         -------Body--------
                         but fields here from the layout's table occurrence to show data for each record that is part of a given month group

                         -----Trailing grand summary-(optional)-------

                         Put summary fields here to get a grand total

                         You can also have headers and footers if you need them and your sub summary part can also be located below the body or you can do both.

                    • 7. Re: Grouping by Month
                      BeachedWhale

                           That's what I did, but for and the layout doesn't give me in errors or anything, just in list view, it doesn't display the other months. It only displays December and the totals for that month. And the grand summary just adds up and outputs December values. I don't know what's wrong??

                      • 8. Re: Grouping by Month
                        philmodjunk

                             If the grand summary adds up only December values, then you only have records for December in your current found set or the data comes from a different table than that specified in Layout Setup | Show Records From.

                        • 9. Re: Grouping by Month
                          BeachedWhale

                               I sorted by Month and in the picture, it should be sorted into March and then December, but it shows December as the only month?

                                

                          • 10. Re: Grouping by Month
                            philmodjunk

                                 This is not the layout I described. I suggested using list view with a sub summary layout part. This makes for better control of the grouping, in my opinion.

                                 Is Month a number field or a text field?

                                 I'd guess that it is either a number field or a calculation field with "number" selected as it's return type. Change this to text and I think you'll see things start to work for you.

                            • 11. Re: Grouping by Month
                              BeachedWhale

                                   OMG you're the best! Thank you so much, it worked perfectly! Month was a calculation field that I created, which extracted the month as a word from the date, and the result was set to number, not text. I switched it and it solved the problem!