Group Summary Fields

[drluggo]

I am relatively new to Crystral Reports.

I have three fields of concern in my projects table.

1) ProjectType
2) LeadHours
3) LeadRate

When I print a project type summary report, which summarizes a bunch of other fields in the same table, I also want to calculate the total labor cost for each job type. On a job-by-job basis, this is simply = (LeadHours * LeadRate). I want the report to calculate this for each record and place the total in a report field for each project type. I cannot figure out how to do this. I know that I can place a calculated field in the database to do this, but I am wondering if there is any way to do it in Crystal. It appears that summary fields cannot be based on calcualtions that are performed for each record, unless I am missing something.

 

[lbass]

Create a formula {@jobcost}:

{LeadHours}*{LeadRate}//place in details section

Then just right click on {@jobcost} to insert a summary at the group level--I'm assuming you are grouping on {projecttype}.

If you have duplicate data then you would need to do running totals on {@jobcost}. Is that the case?

-LB

 

[drluggo]

After I submitted the post I realized I could do what you suggested. I created two fomula fields {@LeadCost} and {@LaborerCost}. I place these in the Details section and then create a summary field which is reset for each group. That works fine.

Now I have another problem. I am grouping by project type and I have four running total fields. These are {TotalFinalBill},{TotalLeadCost},{TotalLaborerCost}, and {TotalAddtionalCost}. I can properly calculate each of these totals by job type.

The problem is that I really want to display a calculated value in the group footer which uses all of these totals. This calculated value is called {GrossProfit} and the formula is :

(({#TotalFinalBill} - ({#TotalLaborerCost} + {#TotalLeadCost} + {#TotalAddtionalCosts})/{#TotalFinalBill}) * 100.0

This gives the %G.P. for each project type. I created a formula field that has this calcualtion in it. I figured I could just place it in the group footer and it would calculate for each group.

It is only calculating for the first group. The remaining groups are blank. Might I add that it is correctly calculating the first group, but why will it not calculate for the remaining groups???

 

[lbass]

Your formula should work unless your running totals are reset improperly or are placed in the wrong section.

If you place each of the four running totals in the group footer, do they calculate correctly for each group?

What are you grouping on? Do you have more than one group? And what did you select for "Reset on change of..." in your running totals? It would be helpful if you used one of your running totals as an example, with detail about how you created it.

-LB

 

[drluggo]

Answers to your questions:

When I place the running totals in the group footer individaully they DO calculate correctly. Note that when I insert my {GrossProfit} formula field in the group footer, I do not have the other running total fields anywhere on the report. Don't know where I would put them.

I am grouping on ProjectType and this is my only group. I have reset on change of Group for all of the running total fields and I evaluate for each record.

So for TotalFinalBill running total field, I simply created by

field to summarize - Projectsroject_FinalBill
type of summary - sum
evaluate - for each record
reset - on change of group (ProjectsrojectType)

The TotalAddtionalCosts summary was the same except it uses a different database field.

The TotalLaborerCosts and TotalLeadCosts are created by placing formulas in the detailed section (which is suppressed) and summarzing based on those fields.

Hope this is enough info.

 

[lbass]

Are TotalLaborerCosts and TotalLeadCosts actually running totals (created using the running total editor) or are they summaries that you created by inserting a summary on a formula field?

-LB

 

[drluggo]

Right Click on Running Total Fields...
New....
Runing Total Fields Editor

field To Summarize = @Laborcost
Type Of summary = sum
Evaluate = For each record
Reset = On group change (Projects.Project_Type)

I am using this through VB6, using the report designer component if that makes a difference

 

[lbass]

Well, I'm stymied--if you have this in the group footer it should work. One thought--is it possible that your result is "0" and that you have your number field formatted to "Suppress if zero"? Or that you have some sort of conditonal section suppression?

The only other thing I can think of is if you had some nulls that were somehow interfering with the formula--but I don't think nulls would prevent running totals from calculating.

Please let me know how you fare.

-LB

 

[drluggo]

Null Values.

Thought I would have gotten an error or even a divide by zero, but the fields were just coming up empty. I have written in to my code a way of avoiding the null value situation so it shouldn't be a problem any more.

Thanks for your help.

Chris