Joined: 24 Dec 2009
Online Status: Offline
Posts: 2
Topic: Left Join and totals Posted: 24 Dec 2009 at 7:12am
I am running a BOM report - using aliases and left joins to link the table to itself multiple times to drill down on sub-assemblies. This working well, only when I try to subtotal some of the costs the subtotal does not show up at all unless all levels are found. If the part does not go down 3 levels the subtotal doesn't print.
my tables are Part, Material_0, Material_1, Material_2, Material_3 etc.
I have formulas to calculate costs at each level cost_0, cost_1, cost_2 etc.
If I subtotal each cost and print them individually I get the right info. When I add them together Sum ({@cost_0}, {Part.PartNo}) + Sum ({@cost_1}, {Part.PartNo}) + Sum ({@cost_2}, {Part.PartNo}) Sum ({@cost_3}, {Part.PartNo})
Nothing shows up -- unless there is an entry for Mater1al_3
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 24 Dec 2009 at 7:26am
try adding a isnull clauses to handle missing data.
As an example ...
Sum ({@cost_0}, {Part.PartNo}) + Sum ({@cost_1}, {Part.PartNo}) + Sum ({@cost_2}, {Part.PartNo}) + (if isnull(Sum ({@cost_3}, {Part.PartNo}) then 0 else Sum ({@cost_3}, {Part.PartNo}))
Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Posted: 24 Dec 2009 at 8:03am
Your formula is not going to give you cumulative values from your various partno group values (total for the whole report). It will only give you a the SUM of of each of these per partno group.
If any of these sums do not exist (NULL) it will make the formula stop and return a NULL for the whole formula. You have to handle the NULLS in some manner to force the formula to evaluate all the way through so generally you need to turn a NULL into 0 to finish the formula.
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
IP Logged
