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".
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
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!
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
Thanks, Ian! I'll give it a shot & let you know how it goes.