4 Replies Latest reply on Oct 25, 2011 1:09 PM by EmmettOliver

    Calculating the oldest date in a subsummary report

    EmmettOliver

      Title

      Calculating the oldest date in a subsummary report

      Post

      Hi all,

      I am trying to create a subsummary report with no body part and I want the oldest of the records' date to be displayed in the report.  I've read some of the forum posts and saw recommendations for a portal in the leading subsummary so as to create a "dynamic" subsummary report.  However, I can't figure that out.  I've attached a screen shot of my basic subsummary report.  Ideally, I would like all of the information inside the red box in my subsummary report with no body part.  Couple of things: 1) I am new to FM 2) portals confuse me.

      Thanks a million,

      Emmett

      FM_Subsummary_Report_10252011.JPG

        • 1. Re: Calculating the oldest date in a subsummary report
          philmodjunk

          Don't think you need a portal for this, just a summary field defined to return the maximum of your date field here.

          • 2. Re: Calculating the oldest date in a subsummary report
            EmmettOliver

            Thanks for the quick response Phil.  So, I could just use the z_RecordCreationTimestamp?  Although, one thing I failed to mention in my post was that I'm trying to calculate the "oldest open record".  In that they may need to be reassigned based on workloads (sorry for not mentioning that in the original post)  So, it could be a calculated field w/no portal required?  Emmett

            • 3. Re: Calculating the oldest date in a subsummary report
              philmodjunk

              A summary field is not a calculated field. Instead, it calculates some sort of aggregate value such as a total, maximum or average from a group of records. You should read up on these as they are frequently used with sub summary parts.

              I don't know how you determine which records are "open" so that leaves me unable to describe how to do this and not make some assumptions about your database that may be incorrect.

              Let's say you have a field, named 'status' that stores the values "open" or "closed".

              If so, define this calculation field (we'll call it TSIfOpen):

              If ( status = "open" ; z_RecordCreationTimeStamp )

              and select "timestamp" as the return type for this calculation.

              Then define a summary field, sOldestOpenTimeStamp by selecting summary as the field's type and then specify that it take the Maximum of TSIfOpen

              Then you can place this field in your sub summary part.

              In a sub summary part the "sorted by" setting will control what sub set of your total records in your found set will be used to compute that max time stamp of open cases. This typically results in a sub total so working from your screen shot, this would return Oldest open case Assigned to Anderson if that's the field specified as the "sorted by" field for you sub summary.