Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2016 : Report Design
Message Icon Topic: Adding a sum to a group Post Reply Post New Topic
Author Message
lowspark
Newbie
Newbie
Avatar

Joined: 27 Nov 2019
Location: United States
Online Status: Offline
Posts: 18
Quote lowspark Replybullet 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 IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9043
Quote DBlank Replybullet 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 IP Logged
lowspark
Newbie
Newbie
Avatar

Joined: 27 Nov 2019
Location: United States
Online Status: Offline
Posts: 18
Quote lowspark Replybullet 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 IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9043
Quote DBlank Replybullet 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 IP Logged
lowspark
Newbie
Newbie
Avatar

Joined: 27 Nov 2019
Location: United States
Online Status: Offline
Posts: 18
Quote lowspark Replybullet 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 IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9043
Quote DBlank Replybullet Posted: 06 Jan 2020 at 2:07am
Brian's book from this site and the internal application help is pretty good.
IP IP Logged
Post Reply Post New Topic
Printable version Printable version

Forum Jump
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot delete your posts in this forum
You cannot edit your posts in this forum
You cannot create polls in this forum
You cannot vote in polls in this forum



This page was generated in 0.031 seconds.