Adding a sum to a group
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22852
Printed Date: 30 Apr 2024 at 3:35am
Topic: Adding a sum to a group
Posted By: lowspark
Subject: Adding a sum to a group
Date Posted: 02 Jan 2020 at 5:37am
I have created a report that has work order(WO) information at different levels
Group 1 = Total number of WO's
Group 2 = Total Closed WO's
Total Open WO's
Group 3 = Info on all WO's by group ( open or closed)
This took some learning but I finally got all the counts correct by choosing the sigma icon and doing a distinct count for each group. so all is good.
NOW I need to calculate the cost for each WO. When I do this Only the close WO's show due to the fact that the cost is not calculated for the WO until it is closed.
What have I tried:
For the cost formula I tried the following :
if {WORKORDER.STATUS}="Closed" then {WORKORDERCOSTSUMMARY.EQUIPMENTCOST}+{WORKORDERCOSTSUMMARY.LABORCOST}+{WORKORDERCOSTSUMMARY.MATERIALCOST} else 0
What I am looking for:
Would love for the report to work the same as it did before but now for the closed work orders have the ability to show the cost associated with it.
with the same effect. Any help would be great
If there are Questions about where this report has come from please see a previous post that got me to where i am now http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22849&PN=2If there are Questions about where this report has come from please see a previous post that got me to where i am now http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=22849&PN=2
-------------
Nothing left to do but Smile, Smile, Smile.
|
Replies:
Posted By: DBlank
Date Posted: 02 Jan 2020 at 6:36am
Do you want to show this per work order, per group, per report total?
If you have NULLs in your data set makes sure for your formula field that you are setting the formula to use default values for nulls.
If you have duplicate rows to deal with and these are in the clustered together you might consider using a Running Total with an on change of WOid as condition as well as the status='closed'
|
Posted By: lowspark
Date Posted: 02 Jan 2020 at 10:02am
looking to have a sum for each group 1,2,& 3. so kind of rolling the cost of the WO up through the report. I do not have nulls in my data that i know of. I think I am just doing my If Then statement wrong.
if {WORKORDER.STATUS}="Closed" then {WORKORDERCOSTSUMMARY.EQUIPMENTCOST}+{WORKORDERCOSTSUMMARY.LABORCOST}+{WORKORDERCOSTSUMMARY.MATERIALCOST} else 0
this gets me my cost per WO but then all the data for WO's not "Closed" is removed from the report
-------------
Nothing left to do but Smile, Smile, Smile.
|
Posted By: DBlank
Date Posted: 03 Jan 2020 at 2:08am
ahhh...
this sounds like a join issue.
The formula field is enforcing the join to the WORKORDERCOSTSUMMARY table which has no matching rows for closed orders.
In crystal the join is not automatically enforced. You have to set it to enforced or use a field from each table int he join (or end node in a chain) for it to become enforced.
You will need to set the join to an outer join.
|
Posted By: lowspark
Date Posted: 03 Jan 2020 at 9:14am
you da man !! thanks so much is there some documentation that you may be able to pass on so i can educate myself on the different versions of links?
-------------
Nothing left to do but Smile, Smile, Smile.
|
Posted By: DBlank
Date Posted: 06 Jan 2020 at 2:07am
|
Brian's book from this site and the internal application help is pretty good.
|
|