10 Replies Latest reply on Mar 21, 2017 11:57 AM by Malcolm

    sub-summary nesting levels

    rtolliver

      I've created a layout that I'd like to use sub-summary fields, the fields involved are "Year", "Month" and "Item".

       

      The layout that I'd like to achieve is:

       

      Year (A list of all years)

      Month (A list of all Months for that year)

      Item (A  list of Items by Months for that year)

       

      I've attempted to create the sub-summary fields in a layout but am unable to get the sorting correct.

        • 1. Re: sub-summary nesting levels
          Malcolm

          To create the sub-summary parts you will have selected a break field. You simply use the same break fields when you sort the data.

           

          Sort by :  Year

                          Month

                          Item

           

          malcolm

          • 2. Re: sub-summary nesting levels
            rtolliver

            Malcolm, I've tried your suggestion and unfortunately it didn't work.

             

            This is what actually is happening;

             

                2015

                      January

                          Item A

                      January

                          Item B

                  2015

                      February

                          Item A

                      February

                          Item B

                      February

                          Item C

                  2015

                      March

                          Item A

                      March

                          Item B

                2015

                      April

                          Item A

                      April

                          Item B

             

                  ----------------------------------------------------------------------------

            Here's what I'm trying to do;

             

                2015

                      January

                          Item A

                          Item B

                     February

                          Item A

                          Item B

                      March

                          Item A

                          Item B

                      April

                          Item A

                          Item B

                2016         

                      January

                          Item A

                          Item B

            1 of 1 people found this helpful
            • 3. Re: sub-summary nesting levels
              philmodjunk

              You need separate sub summary layout parts, one should be "when sorted by year" and the one below it should be "WHen sorted by month". The field that is displaying the month name should go into that second sub summary part. Item A, Item B.... should be placed in the Body layout part.

              1 of 1 people found this helpful
              • 4. Re: sub-summary nesting levels
                Malcolm

                A subsummary part is displayed for each unique piece of data. So, the subsummary part you are using to display the month name must have two unique pieces of data in Jan, three in Feb and two in March. Possibly you are sorting by a date field? To enable subsummary reports like this, based on dates, consider creating fields to support the report.

                 

                Given a date field "eventDate", you can generate a calculated number field called Year which is Year(eventDate) and a calculated number field called YearMonth  which is Year( eventDate ) + month(eventDate) / 100. Even though you strictly only need the month value, it is extremely handy to have the year and the month tied together.

                 

                You then create a subsummary part when sorted by Year, another when sorted by yearMonth. You then sort by Year, YearMonth, eventDate.

                 

                malcolm

                1 of 1 people found this helpful
                • 5. Re: sub-summary nesting levels
                  rtolliver

                  I ran into a problem with creating "YearMonth" field, the Year(eventDate) worked out OK.

                   

                  "Year(eventDate) + month(eventDate) / 100" is the Specified calculation for YearMonth field.

                   

                  When I check the fields, Year has a year value and YearMonth has a number value.

                   

                  Both fields were first created as field Type: Calculation with the Specified Calculation

                  and then as field Type: Number with the Specified Calculation, the results were the same.

                  • 6. Re: sub-summary nesting levels
                    philmodjunk

                    And what exactly is the problem? I can't tell from your post what exactly isn't working for you.

                     

                    What I prefer for this type of thing is a calculation with a date result type:

                     

                    EventDate - Day ( EventDate ) + 1

                     

                    This calculates a date for the first day of the same month and year as event date. I can then use data formatting to display this as just a month and year and I can sort on this date value to get values in chronological order as you would need for this type of report.

                    • 7. Re: sub-summary nesting levels
                      rtolliver

                      The result that I'm looking for is a "Year" and "YearMonth" as described in Malcolm Fitzgerald's 11-Mar-17 post above.

                       

                      Following Malcolm's suggestion I was able to create the Year field but not the YearMonth, the YearMonth only displays a numeric value not the expected month.

                      • 8. Re: sub-summary nesting levels
                        philmodjunk

                        Yes a numeric value is exactly what he describes. March 2017 becomes:

                         

                        2017.03

                         

                        Where 03 represents the third month of the year--March. It is a value that sorts correctly. I like my calculation because it will also sort correct and group records by month year, but I can also use it to display the name of the Month of the year.

                        • 9. Re: sub-summary nesting levels
                          rtolliver

                          Thanks for clearing that up.

                          • 10. Re: sub-summary nesting levels
                            Malcolm

                            The yearMonth field is only needed for sorting. To display the month, use the original date field, with custom format to display the Month or the Month and Year.

                             

                            Malcolm