Skip to Content
avatar image
Former Member

Running Total Formula

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".

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Apr 06 at 07:46 AM

    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

    Add comment
    10|10000 characters needed characters exceeded

    • 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

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

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

    Add comment
    10|10000 characters needed characters exceeded