5 Replies Latest reply on Jan 19, 2012 1:06 PM by comment

    Group report by month

    JulianJohnson

      Hi There,

       

      I have a report that includes a date field (Invoice Due Date). I need to group these records by month & year. The SubSummary is set to show when sorted by due date and the script sorts by due date. However, it groups the records by the actual date rather than by month, which is what I need.

       

      If I made calculated fields to return Month and Year I could sort by those but the subsummary only shows when sorted by one field.

       

      I'm sure I'm missing something obvious but how do I get this working?

       

      Thanks in advance.

      Jules

        • 1. Re: Group report by month
          comment

          Julian Johnson wrote:

           

          If I made calculated fields to return Month and Year I could sort by those but the subsummary only shows when sorted by one field.

           

          That's not entirely correct. You could sort your records by Year and by Month, and have the sub-summary show when records are sorted by Month.

           

           

          However, there is a simpler way; use only one calculation field (result is Date) =

           

          YourDatefield - Day ( YourDatefield ) + 1
          

           

          This will work for the sort as well as for the sub-summary.

          • 2. Re: Group report by month
            JulianJohnson

            Thanks very much - nice elegant solution!

             

            Thanks

            Jules

            • 3. Re: Group report by month
              LyndsayHowarth

              HI Julian,

               

              What 'comment' has advised you is correct.

               

              I saw, however, no evidence that it was clear to you that it is possible to have many sub-summary parts... either/both above and below the body part. Nor was it clear to me whether you knew much of the power these SS Parts give you over a report... so here is my 2 cents worth.

               

              You could eg. add a SS Part ...by Year ... and the part could be empty... but be set (in the Part Definition dialog) to start a new page or restart the page numbering or such.

              You could then have another SS Part ... by Month Number ... that displayed the field values for  MONTH YEAR. (Sorting by Month Number will order them correctly whereas Month Name will not)

              The third SS Part might be for Date so after being sorted by year then month num the days fall in sequence.

               

              Assuming you have more than one record per day there is one SS part left ... if you want a more versatile report. These records are often automatically relegated to the body part. This is a waste... because no matter how you sort it... the body always shows. For this reason I generally sort by recid or something else in the record that is unique to the record... after all the other sorting of Year MonthNo Date... It could be an ID or a Customer ID or anything that is always populated. ... and base the SS Part on it as a sort order item.

               

              Then... delete the Body Part.

               

              You now have a report that can be sorted by Year for Annual breakdowns, Year MonthNo for Monthly breakdows, Year MonthNo Date for Daily breakdowns or Year MonthNo Date ID for per-record breakdowns... which essentially allows you to hide or show the detail simply by sorting.

              Splitting a report in this way also allows you to have Headings... using a Leading SS Part and SubTotals/Totals ... using Training SS Part.

              It also allows you to compare the business across years... eg by sorting by MonthNo Year you will be able to compare April this year to multiple other Aprils.

               

              HTH

              - Lyndsay

              • 4. Re: Group report by month
                Malcolm

                You need to create subsummary parts for each field that you want to sort by. In this case you need subsum parts for month and year too

                 

                Malcolm

                • 5. Re: Group report by month
                  comment

                  Malcolm wrote:

                   

                  You need to create subsummary parts for each field that you want to sort by.

                   

                  I don't think so.