Skip to Content
0

Running Total Formula

Apr 05 at 09:20 PM

49

avatar image
Former Member

I have a report that needs to total "C" materials and "R" materials separately (I have running total fields SumNew and SumRetired to calculate these values) then provide a total of the two using a formula called sumTotalNew. The report is grouped by projected and then by status Both statuses do not always exist for each projectid. The remainder of the report is reliant on there being at least a zero value returned for the total of each status, as I these values are used in later formulas.

How do I write a formula to say something to the effect of 'if you find "C" values in status give me the value returned by the formula @sumTotalNew, but if you don't find any "C" values return 0 or 0.00?' Obviously, the same would be true for "R".

10 |10000 characters needed characters left characters exceeded
* Please Login or Register to Answer, Follow or Comment.

2 Answers

Ian Waterman Apr 06 at 07:46 AM
0

Replace your Running Total with a formula

@CAmount

If FieldA = 'C' then Valuefield else 0

Repeat for R

These formula can then be summed at group and report level in usual way.For Total of two

@TotalReport

Sum(@CAmount) + Sum(@RAmount)

If you want a group level total

@TotalGroup

Sum(@CAmount, GroupField) + Sum(@RAmount, GroupField)

Replace FieldA, ValueField and Groupfield with relevant database fields

Ian

Show 2 Share
10 |10000 characters needed characters left characters exceeded
Former Member

Ian, I wrote the formulas as you suggested. Everything works fine if I have only "C" or "R" values, but as soon as both values exist @CAmount continues to return 0, while @RAmount returns the correct sum. Example of my formula is this: If {pdstakingsheetmaterial.status} = 'C' then {#sum labor (C)} else 0. #Sum Labor (C) is the running total field created previously in the report. @RAmount is written the same with "R" substituted for "C" accordingly. I've substituted the formulas throughout the report for the running totals with the only exception being where the running totals are initially created. What am I missing?

Thanks!

0

You have not really done what I suggested.

Not sure what #Sum Labor(C) is doing. Did you try the formula I suggested without the Running Total and then just performing a normal sum on the Formula?

It could be that your RT # sumLabor is failing when both C&R exist together

Ian

0
avatar image
Former Member Apr 19 at 09:41 PM
0

Thanks, Ian! I'll give it a shot & let you know how it goes.

Share
10 |10000 characters needed characters left characters exceeded