Crystal Report Grouping

0
votes

I'm having some problems with grouping and running totals in crystal reports, I've found loads of info whilst googling but I'm looking a bit more info. Currently I have 3 tables, jobs, times and costs, I have a running total to calculate the value of the time.hours and another for the costs.amount. The issue I'm having is that the costs are being multiplied by the amount of time.hours records that exist. so if a job has 3 time.hours records and there is only one record of costs.amount that cost is being multiplied by that about of records So what i end up with is this

Job     Hours     Costs
123       5         0
123       4        100
123       3         0
=========================
          12       100

which is exactly right, now if i add another running total without resetting then my total ends up as 

========================
          12       300

I think this is happening because of the way i have them grouped but im not sure how to make them group to only job.ID

1
crystal-reportsreportgrouping
What sections did you place the fields that add up the running totals? It would also help if you could describe how you are grouping the data. – R. McMillan
My apologies, i'm struggling to explain my problem. Essentially i would like to generate a list of all jobs and the charges associated with them. Some jobs will only have time costs, some only material costs and some with both. I'm currently grouping them by job_ID as all tables have that field and I have also set all tables to left outer join with enforced join (both). The problem only seems to be happening if there is material costs so i have a feeling its to do with table joins or possibly my selection formula... – Luke McClelland
my formula for pulling the info is: {timesheet.Time_Date} <={?timDateUJC} and {jobsheet1.Job_JobCompleted} = 0 or {materialcost.Inv_Date} <= {?supDateUJC} and {jobsheet1.Job_JobCompleted} = 0 what is need is to pull all records that were created before a certain date and that are not completed – Luke McClelland
When the datarows are printing on your report do you have duplicate records where the cost for one job is repeated on multiple rows? This would cause the total to over-accumulate and would indicate a problem either with your database/datasource or a need for another level of grouping after job_ID. Its hard for me to say which or if that is the case without a better idea of what your database and report layout look like though. – R. McMillan
Ok so I got this working, the reason I was getting this was due to my grouping within a group, I've since removed the grouping and was still getting an issue with pulling the data I wanted. Once I added parentheses to my select formula everything is working ok – Luke McClelland

1 Answers

0
votes

This was solved using ( ) to break the formula down

({timesheet.Time_Date} <={?timDateUJC} and {jobsheet1.Job_JobCompleted} = 0) 
  or 
({materialcost.Inv_Date} <= {?supDateUJC} and {jobsheet1.Job_JobCompleted} = 0)