2 Replies Latest reply on Aug 14, 2010 8:49 AM by LaRetta_1

    Report categorised by Month from Timestamp - how?



      Report categorised by Month from Timestamp - how?


      How do I sort records by month only from a timestamp field? I want to include the results in a Report where the results will be categorised by Month only. At the moment, they are categorised by timestamp time which means there is no categorisation at all... I edited the Date format in the Find so that it showed only the month, but the Timestamp format (which may have been more appropriate to use) could not be formatted by anything that did not include seconds - format by month wasn't even an option.

      How can I do this please?


        • 1. Re: Report categorised by Month from Timestamp - how?


          you haven't to format the timestamp field, but to create a new calculated field:

          Month ( YourTimeStampField )

          and than use that new field to create your report.

          Note that it will consider even same month of other years.

          • 2. Re: Report categorised by Month from Timestamp - how?

            I would create a calculation (result is date) like this:

            Date ( Month ( TimeStampField ) ; 1 ; Year ( TimeStampField ) )

            This turns all your timestamps into generic first day of the month.  This 'generic' date can then be used to group your records for reporting, portal filtering or relationships.  Place this calc in leading part and format the date (at layout level) as only MonthName, year for display.

            You can also use calc (again result is date) similar to:

            GetAsDate ( TimeStampField ) - Day ( TimeStampField ) + 1

            These calcs will take into account the year so that January 2009 and January 2010 don't group together.  In this way, you can find records for 3/* (for example to find all records, all years for March).  Then run your report, sort by either of the calculations I provided, and it will group as:

            March, 2008

            ... body of report

            March, 2009

            ... body of report

            This generic date will be used quite often throughout your solution in many ways.