Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2022 : Report Design
Message Icon Topic: Print null records Post Reply Post New Topic
<< Prev Page  of 3 Next >>
Author Message
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Dec 2014 at 10:13am
please make sure your sample includes some nulls that are causing you an issue
IP IP Logged
rlivermore
Groupie
Groupie


Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
Quote rlivermore Replybullet Posted: 03 Dec 2014 at 10:20am
Is this what you're looking for?

acct name, address, city, state, zip, contracttype
Alpha Direct, 8240 E. gelding, Glendale, CA, 11111 Gold Seal
Banner Casa, 2820 W. Kelton, Chicago, IL, 222222, Silver Seal
GMB Law, 1850 N. Central, Miami, FL, 333333,
Plant Solutions, 7255 E. Adobe, Austin, TX, 444444, Gold Seal
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Dec 2014 at 10:24am
oops I just realized I recommended you to use NOT ISNULL()
 
try
IsNull({tblContracts.ContractType}) OR {tblContracts.ContractType}<>'whatever you want to exclude here'


Edited by DBlank - 03 Dec 2014 at 10:24am
IP IP Logged
rlivermore
Groupie
Groupie


Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
Quote rlivermore Replybullet Posted: 03 Dec 2014 at 10:38am
After the adjustment it's displaying every record in the database minus the contractype=cloudit, it seems to be excluding all of the other formulas before it now.

{tblServiceOrders.DateReceived} > DateTime (2010, 11, 01, 00, 00, 00) and
{tblServiceOrders.SOType} in ["AMC", "Service"] and
{tblServiceOrders.Status} in ["Closed", "Ready for Invoicing (AR)", "Soft Close (CS)"] and
{tblServiceOrders.DateClosed} > DateTime (2014, 11, 29, 00, 00, 00) and
IsNull({tblContracts.ContractType}) or
{tblContracts.ContractType} <> "CloudIT"
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Dec 2014 at 11:03am
{tblServiceOrders.DateReceived} > DateTime (2010, 11, 01, 00, 00, 00) and
{tblServiceOrders.SOType} in ["AMC", "Service"] and
{tblServiceOrders.Status} in ["Closed", "Ready for Invoicing (AR)", "Soft Close (CS)"] and
{tblServiceOrders.DateClosed} > DateTime (2014, 11, 29, 00, 00, 00) and
(IsNull({tblContracts.ContractType}) or
{tblContracts.ContractType} <> "CloudIT" )
IP IP Logged
rlivermore
Groupie
Groupie


Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
Quote rlivermore Replybullet Posted: 03 Dec 2014 at 11:12am
I added the parens and now its back to square one, only displays data that has a contract type associated with it
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Dec 2014 at 11:29am

hard to say with your data set but usually once you start throwing date select criteria into the mix it will ruin your intention of the outer join. This was why I had originally indicated that if you had other select criteria you might want to change to the join process.

THe best solution is to move the criteria into the join but you will have to write a command or stored proc or SQL view to do that.
is that an option?
IP IP Logged
rlivermore
Groupie
Groupie


Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
Quote rlivermore Replybullet Posted: 03 Dec 2014 at 11:32am
I dunno, I've never tried your suggestion
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 03 Dec 2014 at 11:34am

is your data source sql?

do you have rights to making a view?


Edited by DBlank - 03 Dec 2014 at 11:34am
IP IP Logged
rlivermore
Groupie
Groupie


Joined: 27 Sep 2012
Online Status: Offline
Posts: 70
Quote rlivermore Replybullet Posted: 03 Dec 2014 at 11:34am
Writing the code doesn't sound like something I'm capable of doing...
IP IP Logged
<< Prev Page  of 3 Next >>
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.