Crystal report- cross tab in group not calc. correctly

I have the following Crystal report command and in the report it groups by district, salesrep & plantype.  I have a cross tab in the salesrep grope and the district group. All summary and formulas work perfect in the sales group but in the district group I can not get it to calc. the correct MTD % PLAN (Last field in cross tab - see screen shot).  The formula is just actamt/planamt.  I have tried doing sum at group level but number never displays correct in the correct cross tab box.

Example: Sales should be 112.33% but shows 1011.40.  Also, if I do the sum(actamt)/sum(planamt) at the dist level it puts 1194.75 in each bucket (see 2nd screen shot)

Any ideas??



select
vw_shippingdays.totalshippingdays,
vw_shippingdays.currshippingdays,
smsnmaster.email,
smsnmaster.manager,
smsnmaster.district,
smsnmaster.salesrepname,
smsnmaster.salesrepnumber,
slsrepplan.slsrep,
slsrepplan.plantype,
slsrepplan.actamt,
slsrepplan.planamt,
fiscalperiods.begindate,
fiscalperiods.enddate,
fiscalperiods.shippingdays,
vw_shippingdays.currshippingdays as vw_currshippingdays,
case when slsrepplan.planamt = 0 then 0 else (slsrepplan.actamt/slsrepplan.planamt)*100 END as "plan%",
case when slsrepplan.planamt = 0 then 0 else (slsrepplan.actamt/fiscalperiods.shippingdays) * vw_shippingdays.currshippingdays END as "target",
case when slsrepplan.planamt = 0 then 0 else vw_shippingdays.currshippingdays/fiscalperiods.shippingdays * 100 END as "target%today"


from (((slsrepplan inner join fiscalperiods on slsrepplan.mth = fiscalperiods.month and slsrepplan.yr = fiscalperiods.year) inner join smsnmaster on smsnmaster.salesrepnumber = slsrepplan.slsrep) left join vw_shippingdays on fiscalperiods.shippingdays = vw_shippingdays.totalshippingdays)


shot1.jpg
shot2.jpg
kelsanitAsked:
Who is Participating?
 
kelsanitConnect With a Mentor Author Commented:
I figured it out! if I remove the sales rep group it works.  So, I am going to just do a sub report and pull it into the main report
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.