| Author |
Message |
lowspark
Newbie
Joined: 27 Nov 2019
Location: United States
Online Status: Offline
Posts: 20
|
   Topic: Adding a sum to a group
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.
|
 IP Logged |
|
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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'
|
|
IP Logged |
|
lowspark
Newbie
Joined: 27 Nov 2019
Location: United States
Online Status: Offline
Posts: 20
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
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.
|
|
IP Logged |
|
lowspark
Newbie
Joined: 27 Nov 2019
Location: United States
Online Status: Offline
Posts: 20
|
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.
|
|
IP Logged |
|
DBlank
Moderator
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
|
Posted: 06 Jan 2020 at 2:07am |
|
Brian's book from this site and the internal application help is pretty good.
|
|
IP Logged |
|
|
|
