Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Crystal Reports Linked Tables/ formula

Posted on 2012-09-14
13
Medium Priority
?
899 Views
Last Modified: 2012-09-18
Hi, I hope I can explain this correctly, complete novice in Crystal!

I have a report created that I export data form an Access DB into a csv file. The crystal report contains 3 tables at the moment, linked with left outer join and with internal reference as the field that should be matched. This works fine

I have now added another table that contains around 12 records. I have checked and these 12 records are present in all 3 of the existing tables, so I added the table again with a left outer join and with the same field. I have a formula field in crystal called paid, which adds up the field paid in all 4 tables. Since I have added the 4th table, this field now only show amounts when there is data from the 4th Table, otherwise the field is just blank. All other fields are as expected. I'm certain that this is something to do with my links, I have tried everything but can not get the field to show the correct data in the paid field. The formula is very basic and just adds the paid fields from all the tables together. If I remove the 4th table from the formula all works fine, but without adding the amount from the 4th table.

Tables are linked as follows
table1 -left outer join =internal ref table 2 -left outer join =internal ref table 3 -left outer join =internal ref table4

I have tried other links but when I do this I only get the data from table 4, I missing something really basic I know

Can anyone put me out of my misery :-)
0
Comment
Question by:George_Milton
13 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 38398467
Do you have any selection rules operating?
0
 

Author Comment

by:George_Milton
ID: 38398512
Could this be the formula? Would I need to add if is null for the field paid from table 4?
0
 

Author Comment

by:George_Milton
ID: 38398520
Only selection is on client name which is the same
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
LVL 13

Expert Comment

by:LIONKING
ID: 38398547
Could this be the formula? Would I need to add if is null for the field paid from table 4?

I would try this... The NULL that you get with the left outer join could make your formula behave unexpectedly.
0
 
LVL 19

Expert Comment

by:GJParker
ID: 38398548
Sounds like your paid formula is failing because some values are null, there are a few ways to handle this

try

File menu -> report options -> convert database null values to default, check thi sbox and rerun your report
0
 

Author Comment

by:George_Milton
ID: 38398612
Hi
Tried that does not make any difference unfortunately. It appears that when calculating the formula is there is no data in table 4 it does not add up the rest of the values in table 1, 2 and 3
0
 
LVL 19

Expert Comment

by:GJParker
ID: 38398617
please post your formula
0
 

Author Comment

by:George_Milton
ID: 38398637
{DamagePayment_ThirdPartyDamage_Paid}+{DamagePayment_ThirdPartyDamage_Outstanding}+{MD_DamagePayment_OwnDamage_Paid}+{BI_DamagePayment_OwnDamage_Paid}+{MD_DamagePayment_OwnDamage_Outstanding}+{BI_DamagePayment_OwnDamage_Outstanding}+{.BI_DamagePayment_ThirdPartyInjury_Paid}+{BI_DamagePayment_ThirdPartyInjury_Outstanding}+{Migration.Total Cost}

As soon as I add the last field is when it only returns values with an internal reference in that table. I want it to return the total of all those fields, even if there is no internal reference, in a table, does that make sense

Thanks
0
 
LVL 19

Expert Comment

by:GJParker
ID: 38398654
Try this, this should fix the issue if it is down to nulls : make sure you untick the "convert database null values to default" option first

local numbervar cMTCost := If IsNull({Migration.Total Cost}) Then 0 Else {Migration.Total Cost};

{DamagePayment_ThirdPartyDamage_Paid}+{DamagePayment_ThirdPartyDamage_Outstanding}+{MD_DamagePayment_OwnDamage_Paid}+{BI_DamagePayment_OwnDamage_Paid}+{MD_DamagePayment_OwnDamage_Outstanding}+{BI_DamagePayment_OwnDamage_Outstanding}+{.BI_DamagePayment_ThirdPartyInjury_Paid}+{BI_DamagePayment_ThirdPartyInjury_Outstanding}+MTCost


if this doesn't fix it then your issue isn't down to null values, can you upload the report with saved data for us to look at.
0
 
LVL 19

Accepted Solution

by:
GJParker earned 1000 total points
ID: 38398656
typo error in first post fixed

local numbervar MTCost := If IsNull({Migration.Total Cost}) Then 0 Else {Migration.Total Cost};

{DamagePayment_ThirdPartyDamage_Paid}+{DamagePayment_ThirdPartyDamage_Outstanding}+{MD_DamagePayment_OwnDamage_Paid}+{BI_DamagePayment_OwnDamage_Paid}+{MD_DamagePayment_OwnDamage_Outstanding}+{BI_DamagePayment_OwnDamage_Outstanding}+{.BI_DamagePayment_ThirdPartyInjury_Paid}+{BI_DamagePayment_ThirdPartyInjury_Outstanding}+MTCost
0
 

Author Comment

by:George_Milton
ID: 38398953
That seems to have worked, thank you. Any chance you could explain what it is doing? I guess it is creating the MTCost buy looking at the field, is that contains a null then show 0?
0
 
LVL 19

Expert Comment

by:GJParker
ID: 38399383
Yes it's simply replacing a null value with a zero, strange the first option didn't work as that shoukd have the same effect.

Anyway glad you got it working.
0
 
LVL 35

Expert Comment

by:James0628
ID: 38401195
It sounds like GJParker has this sorted out, but just to add to what he has already said:

 If the problem is nulls and this is just a regular formula and not something like a record selection formula, then "Convert Database NULL Values to Default" really should have taken care of it.  You did change that setting under File > "Report Options", correct?  You can also find that setting under File > Options, but that sets the default, which won't change an existing report.

 The more recent versions of CR also have a setting in the formula editor to control how an individual formula handles nulls.  You could see if you have that setting.

 FWIW, here's an explanation of the problem from the CR 10 Help:

In general, when Crystal Reports encounters a null valued field in a formula, it immediately stops evaluating the formula and produces no value. If you want to handle null field values in your formula, you must explicitly do so using one of the special functions designed for handling them: IsNull, PreviousIsNull or NextIsNull.
0

Featured Post

[Webinar On Demand] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Crystal Reports: 5 Tests for Top Performance It is complete, your masterpiece report.  Not only does it meet your customer’s expectations, it blows them out the water, all they want is beautifully summarised and displayed in a myriad of ways. …
I hate sub reports and always consider them the last resort in any reporting solution.  The negative effect on performance and maintainability is just not worth the easy ride they give the report writer.  Nine times out of ten reporting requirements…
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

571 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question