Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Having Problems with the SUM with 2 differnt table Post Reply Post New Topic
Author Message
will99
Newbie
Newbie


Joined: 01 Nov 2015
Location: Canada
Online Status: Offline
Posts: 9
Quote will99 Replybullet Topic: Having Problems with the SUM with 2 differnt table
    Posted: 06 Jan 2016 at 8:35am
Hi Guys, i hope somebody can help me here.

I have 2 tables {INVENTORY} and {SALESORDERSITEMS}. My goal is to have the following on the report:

{inventory.Item} | {inventory.description} | sum({inventory.Onhand} | sum(SalesOrderItems.OrderQty}

Inventory.ONHAND has to be added cause we use multiple warehousing and the {INVENTORY} table has a WHSE field.

So if i stick only to the inventory table, all the SUM's i use for ONHAND, ONPURCHASE etc are working perfectly but AS SOON as i use a field
from SalesOrderItems table, all my SUM's are MULTIPLIED by hundreds? For example the total on hand in all my warehouse for the same product is
supposed to be 650 pcs but the reports gives me 27000 pcs. How can i fix this? I tried to GROUP them by Item #, by WHSE # etc. etc. but nothing seem to work.
I have tried also subreports but still nothing...

i'm missing something but i have no idea how to do it and this is creating same problems in other reports so if i find a solution for this one, i will have the solution for the other reports!

i appreciate your help

Will!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 06 Jan 2016 at 11:10am
when you add a field from a second table it will 'enforce' the join which may cause your records to end up with 'duplicate rows'if one table has multiple matches from the other table. If you did not join the table it will make a Cartesian result.
If you did join the tables you can use Running totals (or shared variable formulas) to get the values you want.
if you do not to or cannot join the tables you may have to consider sub reports but not sure how that will work as you need some way to match the content.
IP IP Logged
will99
Newbie
Newbie


Joined: 01 Nov 2015
Location: Canada
Online Status: Offline
Posts: 9
Quote will99 Replybullet Posted: 06 Jan 2016 at 1:25pm
Thanks for the infos, I'm trying now the Running totals but its simply not working, it gives me always the same results, My tables are joint and when the joint is not there, the results from the INVENTORY tables are good but the results that are supposed to come from the SALES table are not, not even close. The Running Total i have used all combination that i thought possible but nothing.

Also, what's a CARTESIAN result? If i don't joint the tables, Crystal Reports advise me to JOINT them.

I'm a bit lost..

Will

Edited by will99 - 06 Jan 2016 at 1:25pm
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 07 Jan 2016 at 2:32am
A cartesian result is every possible combination between the two tables. basically every row from table one joined to evert row in table two.

Not that running totals only work in the detail section or footers. If you place them in headers they only include the first row of the data (relevant to the header it is used in).
Your running total will likely be set to evaluate on 'a change of a field' then pick the PK field from the relevant table.
If you are grouping the data (likely you need to do this to hide the details section to hide the duplicate rows) then you can place the RT in the group footer and have it reset on change of group.
IP IP Logged
will99
Newbie
Newbie


Joined: 01 Nov 2015
Location: Canada
Online Status: Offline
Posts: 9
Quote will99 Replybullet Posted: 07 Jan 2016 at 11:06am
Thank you Dblank but do you think i have to absolutely Joint the 2 tables?

I'm doing it without joint and nothing seem to work. Obviously when i join 2 tables the numbers are just all screwed up and way too up.

Thanks

Will
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 08 Jan 2016 at 3:07am
Unless you are going to use sub reports you will need to join the tables.
Do you have a'products' table too?
One of the things you have to be cautious of is possible excluding products that are not in one of the two other tables. Not knowing your data and working on some assumptions of how it managed I would...
left join use the 'products' table and left join both the {INVENTORY} and {SALESORDERSITEMS} to it.
I would
group on the products.productid
create two running totals to calculate each of the values you want
-sum({inventory.Onhand} and
--sum(SalesOrderItems.OrderQty}.
use "on change of file" using the PK of each table in the RT set up as the evaluate for option.
Set each to Reset on change of group
place in the detail section to verify it is executing as desired
place in the group footer for display
suppress the group header and details
IP IP Logged
will99
Newbie
Newbie


Joined: 01 Nov 2015
Location: Canada
Online Status: Offline
Posts: 9
Quote will99 Replybullet Posted: 08 Jan 2016 at 11:10am
Thank you very much DBlank, i'm advancing really fast and MOST of the numbers are pretty good but sometimes, for no reason, my RT's are BLANK... I did trying the 0 as a default value but nothing seem to work!

Would you have an idea why this would happen?

thanks

Will

Edited by will99 - 08 Jan 2016 at 1:24pm
IP IP Logged
will99
Newbie
Newbie


Joined: 01 Nov 2015
Location: Canada
Online Status: Offline
Posts: 9
Quote will99 Replybullet Posted: 09 Jan 2016 at 4:07am
Thank you very much Dblank, you have answered all the questions... and i finally finished it with your help. I'm just a beginner with Crystal so i appreciate all you guys help..

You have been such a good help for me!

thank you

Will
IP IP Logged
will99
Newbie
Newbie


Joined: 01 Nov 2015
Location: Canada
Online Status: Offline
Posts: 9
Quote will99 Replybullet Posted: 09 Jan 2016 at 4:08am
FOUND IT!

thanks

Will
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.016 seconds.