7 Replies Latest reply on May 17, 2017 11:16 AM by mikebeargie

# Is there a way to do a Summary based on a Calculation?

For example, I have durations (in seconds) for a bunch of records

12

21

42

57

I want to create a running Summary for these items, the only problem is it shows the first records as being 12 as that is the total for that record, I'd like it to start at 0 and continue adding from there as I'm aiming to display the start point. I am sorting in a specific order so the summary is vital that the first record in the sort has a value of 0

12 - 12

21 - 33

42 - 75

57 - 132

I want to display the Summary field like so:

12 - 0

21 - 12

42 - 33

57 - 75

In short I'm adding the durations together but need to ensure that its displaying the start time for each record of which starting at 0 hence the offset. This will always be based on a different set of records (depending on the found set)

Any help would be greatly appreciated!

• ###### 1. Re: Is there a way to do a Summary based on a Calculation?

Couple ways to do this:

1) Add a second field and a trigger that performs a "replace field contents" action or loop script to build these on the fly for a displayed found set. (cached data method).

2) Setup something using the GetNthRecord() function that "looks back" to the previous record for it's value. Then summarize that field for your sum total using a running total summary type.

3) Do the same as #1, but with merge variables instead of field contents.

Probably more ways to do it, but those come to mind.

Note that these types of summaries, and usage of the GetNthRecord() function have have negative performance effects on large data sets.GetNthRecord

• ###### 2. Re: Is there a way to do a Summary based on a Calculation?

Thanks Mike, your pointers have got me thinking.

Get Nth Record is the way i'm most familiar with, so do you know how I'd be able to go about getting the previous record based on the Found Set?

If I try to use GetNthRecord(Duration_Seconds;Get(RecordNumber)- 1) its looking for the previous record for the entire table, not necessarily the one in the found set. Does that rule out using this method?

• ###### 3. Re: Is there a way to do a Summary based on a Calculation?

Get(RecordNumber) IS sensitive to the found set. It’s NOT record ID which is a separate thing.

If I’m in a found set of ten, Get(RecordNumber) will return 1-10 for those records respectively.

Thus, if you have an unstored calculation returning something like:

Case(

Get(RecordNumber) = 1 ; 0 ;

GetNthRecord( “Amount” ; Get(RecordNumber) – 1 )

)

Then it would auto-update based on the found set.

The only “gotcha” here is if you subsummarize a report, the first record in a new group would try to pull from an old group, so you would need something like:

Case(

Get(RecordNumber) = 1 ; 0 ;

Table::SubsummaryField <> GetNthRecord( “SubsummaryField” ; Get(RecordNumber) – 1 ) ; 0 ;

GetNthRecord( “Amount” ; Get(RecordNumber) – 1 )

)

In order to check the previous record being in a different group or not.

1 of 1 people found this helpful
• ###### 4. Re: Is there a way to do a Summary based on a Calculation?

PERFECT! You've helped me crack it Mike! One last issue is that the first record returns blank given that it has no Nth Record to refer to, is there anyway to get this to display as 0 as opposed to blank without using a placeholder (as the value needs to be printed in a PDF)

• ###### 5. Re: Is there a way to do a Summary based on a Calculation?

The Case() statement I posted earlier explicitly returns zero for the first record and the modified one has it for the first record as well as the first record in each group.

Make sure your calculations are set to “always evaluate”, and “display if zero”.

1 of 1 people found this helpful
• ###### 6. Re: Is there a way to do a Summary based on a Calculation?

Thanks again Mike, much appreciated, thats fixed it, happy customer right here sir!

• ###### 7. Re: Is there a way to do a Summary based on a Calculation?

Where can I send the invoice? (j/k)