[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Crystal report- cross tab in group not calc. correctly

Posted on 2009-12-23
2
Medium Priority
?
480 Views
Last Modified: 2013-11-15
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
0
Comment
Question by:kelsanit
1 Comment
 

Accepted Solution

by:
kelsanit earned 0 total points
ID: 26115564
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

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this post we will learn how to connect and configure Android Device (Smartphone etc.) with Android Studio. After that we will run a simple Hello World Program.
What do responsible coders do? They don't take detrimental shortcuts. They do take reasonable security precautions, create important automation, implement sufficient logging, fix things they break, and care about users.
Progress
Screencast - Getting to Know the Pipeline

834 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question