By no means am I an expert in writing reports with Crystal, but I definitely consider my knowledge of crystal pretty advanced. However, this one report is giving me a horrible time and I can’t figure it out. Normally, whenever I have an issue creating a report in crystal, I create the query in SQL and just paste it into the report, but in this case, that will not work.

It seems like a very simple report and there are only two tables involved:

                JC.TrxHistory

-This table stores every single transaction posted to job cost history. Whenever posting transactions, you always specify a jobdivision, jobphase, jobsubphase and jobcostindicator with that transaction. I’ll refer to the combination of these 4 fields as a cost sequence. You may have 1,000 records for a specific cost sequence within a unique job number. This table stores the actual cost amount.

 

                JC.CostSummary

-This table stores the estimated data for each cost sequence. You would never have more than one record for a specific cost sequence.

 

I’ve attached basic screenshots illustrating the data stored in the tables and also how I need the report to look. The entire report will pull from the JC.TrxHistory except for one field, estimated cost (which comes from JC.CostSummary). I understand that I can’t include the jobestimate field in the detail of the crystal report because it will populate that estimated cost for every single transaction posted to history. It’s not a 1 to 1 ratio between the tables since the JC.TrxHistory stores every single transaction and the JC.CostSummary stores an estimated amount for a particular cost sequence.

The report is used to take the total actual costs from a cost sequence (pulling from JC.TrxHistory) and compare them against the estimated cost for that cost sequence (pulling from JC.CostSummary).  The end result of the report is for me to create a formula that takes the total cost from the detail records of a cost sequence (pulling from JC.TrxHistory) and compare them against the estimate cost for that cost sequence (pulling from JC.CostSummary).

There has to be a way to do this in crystal but I’ve tried everything I can think of and nothing gives me what I want. It seems like a simple report but I can’t figure it out to save my life.

Unfortunately, that doesn't work because that's what I've been trying. It doesn't give me correct numbers for estimated cost whenever I put the estimated amount field on the report.

Whenever I browse data in the report explorer for the estimated amount field, it's obviously correct data. However, whenever I browse data after dragging the estimated amount onto the report, it basically gives me all zero's and 5 random numbers  in a table with over 500k records.

I'm starting to think it's a problem with my groupings.

Any ideas?