Crystal Reports Division Formula Not Calculating as Desired

crystal-reportscrystal-reports-2008

I am one step away from finishing up a report in Crystal 2008. I have included a snapshot of the report below. For some reason I cannot get the formula in the yellow column to calculate correctly. I am trying to divide the value of Net A for each main data source/year into the yearly Total of Net A to get the % of Data for each Main Data Source. Net A is pulling from a field in access and the total of Net A is a running total based on a current Run Date using formula ({HNT_End_To_End_Data.REPT_YYMM} = cStr(currentdate,'yyMM') in the evaluate section of the running total and the field is reset on each change of Year.

The formula in the yellow column is currently ({HNT_End_To_End_Data.Net_A}/Sum ({HNT_End_To_End_Data.Net_A}))*100 and is showing values that are like it's doing NetA divided into the grand total of Net A of all 3 years (3,727,560/108,666,439) rather than the dividing into individual year (3,727,560/34,981,163). I'm attempting to get the formula to divide each Main Data Source for each year into the yearly Net A total.

I've tried doing many different formulas and have had no luck, so would appreciate any suggestions anyone might have.

enter image description here

Best Answer

Do you have a group on year({sometable.somedate})? You need to add that group into your aggregate (sum) function:

(
 {HNT_End_To_End_Data.Net_A} 
 / 
 Sum({HNT_End_To_End_Data.Net_A}, year({sometable.somedate}))
) * 100

EDIT: After reviewing the report I can see you are using a conditional formula {HNT_End_To_End_Data.REPT_YYMM} <> cStr(currentdate,'yyMM') to suppress the detail section. So SUM({HNT_End_To_End_Data.Net_A},{HNT_End_To_End_Data.SERVICE_THRU_YEAR}) is actually including the records which aren't being displayed. You have a number of ways to get around this. The simplest is probably to create a new formula for net_a:

if {HNT_End_To_End_Data.REPT_YYMM} <> cStr(currentdate,'yyMM') then
 {HNT_End_To_End_Data.Net_A}
else
 0;

You can then do:

(
 {HNT_End_To_End_Data.Net_A} 
 / 
 Sum({@net_a}, year({sometable.somedate}))
) * 100
Related Topic