Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: suppressing Post Reply Post New Topic
<< Prev Page  of 2
Author Message
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Oct 2009 at 9:39am
Did you use the ISNULL in your selection. I have found an oddity in using this in crystal. It must be used as the first condition in an OR statement otherwise it is ignored.
Example:
ISNULL(fname) or fname="" won't give you the same results as
fname="" or isnull(fname)
IP IP Logged
gizmosilly
Newbie
Newbie
Avatar

Joined: 04 Aug 2009
Location: United States
Online Status: Offline
Posts: 26
Quote gizmosilly Replybullet Posted: 21 Oct 2009 at 9:42am
I tried doing a formula where it included "" or null just to make sure that some of the fields were blank and others null and I got the same result as just using the "" only.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 21 Oct 2009 at 9:43am
makes sense, as I have 'figured' out that when CR hits and error (that is programatic) it doesn't raise an error, it just continues.  So in fname="" when fname is NULL, it hits an error, and just continues on, ignoring the rest of the or statement.
 
So, I completely agree with DBlank about putting the ISNULL first....
and will have to remember it for myself in the future.
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 21 Oct 2009 at 9:45am
using DBlank's idea, the formula would need to look something like:
isnull({table.col1}) or isnull({table.col2}) or isnull...
or {table.col1}="" or {table.col2}="" or ...
 
do all the isnulls first, as the first field that is null and CR is trying to compare to "" will fail the rest of the report.
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Oct 2009 at 9:48am

Another trick is to make a flag for each row to test your formula. Add the formula field to the detail row to see / use it for testing. Start adding () around each of the NULL or "" per field if you use them (or use Lockwelles all isnull's first suggestion).

Start with one condition then add another until you see it "break".
 
if isnull({CUSTOMER.CUSTOMER_XREF_CODE}) or {CUSTOMER.CUSTOMER_XREF_CODE}="" then 'Suppress' else 'Show'
Did this give the correct flag?
Now add another one to test....
if
(isnull({CUSTOMER.CUSTOMER_XREF_CODE}) or {CUSTOMER.CUSTOMER_XREF_CODE}="")
 or
(ISNULL({CUSTOMER.fname}) or {CUSTOMER.fname}="")
then 'Suppress' else 'Show'
 
Keep going unitl you see what field breaks the expected ('corect') value


Edited by DBlank - 21 Oct 2009 at 9:50am
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 21 Oct 2009 at 9:51am

Of course, if blank is OK, and you don't care about fields being null, you can go to Report/Options/Reporting and check the Convert Database NULL to Default.

Not always what you want, but might be the solution for you today.
IP IP Logged
gizmosilly
Newbie
Newbie
Avatar

Joined: 04 Aug 2009
Location: United States
Online Status: Offline
Posts: 26
Quote gizmosilly Replybullet Posted: 21 Oct 2009 at 12:09pm

DBlank and Lockwelle both of you have been extremely helpfull and I have learned a lot from both of you.  I really appreciate the help. I was able to finally figure out that the last name is the one that is causing the report to "break" one of the development people is going to help me figure out why. Thanks again

Ali
IP IP Logged
<< Prev Page  of 2
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.