1 Reply Latest reply on Feb 21, 2011 10:07 AM by philmodjunk

    Accummulating totals in a summary field  with two sub summary elements

    PecCars

      Title

      Accummulating totals in a summary field  with two sub summary elements

      Post

      Consider the the table TA with TA::PropertyId, TA::Year, TA::Stays. You can have several properties in several years. You will have one record per stay. Now ... I need a summary by property and year. I have also decided to do this at the table level instead of using a layout. The reason being those stats and others are the basis for my reports, but are not the reports themselves.

      TA will be sorted by PropertyID and by Year. I can total the stays in TA::AccummulatedStays by property for instance: create a summary field TA::AccummulatedStays; Total it as running with restart when sorted by TA::PropertyID. Works ... now

      How do I tell it to break and restart when the year changes ?

        • 1. Re: Accummulating totals in a summary field  with two sub summary elements
          philmodjunk

          You can use the GetSummary(SummaryField ; breakField) function to compute a sub total, but it computes based on the current sort order where the "break field" would be either your Year or PropertyID field--depending on how you compute the sub total (And you can get different results from this calcuation just by sorting only by the break field or by sorting by another field first, then the break field.)

          Your only other option, as far as I know, is to set up a table of related values where you have one record for each sub category and a relationship that uses these values in links to your TA table. Then you can refer to a summary field via the relationship or use an aggregate function such as Sum() to compute the sub totals.