Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: suppressing Post Reply Post New Topic
Page  of 2 Next >>
Author Message
gizmosilly
Newbie
Newbie
Avatar

Joined: 04 Aug 2009
Location: United States
Online Status: Offline
Posts: 26
Quote gizmosilly Replybullet Topic: suppressing
    Posted: 21 Oct 2009 at 6:54am

I am trying to create a report on customer names.  This report should pull out all customers that are missing information. The fields that could be missing are customer#, customer x-ref, first name,last name,address,city,state and phone number.  I have tried suppressing the records that are complete in the section expert but when I do multiple field suppression by the time I am done every thing is suppressed. so then I thought perhaps I can do something with the record selection and tell it if the {?Missing Address #}=1 then suppress address<>'' but I do not know how to write the formula and once I have this parameter set I would create parameters for each one of the fields. There may be an easier way of accomplishing this if someone can help or has any suggestions on how I may be able to accomplish my end goal that would be great!

Thanks in advance
Ali
IP IP Logged
lockwelle
Moderator
Moderator


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

your formula should use ORs.  A question is, are the missing values NULL or they set to some value. Either way, I would think would look something like this would work in the Section Expert suppression:

ISNULL({table.customerNo}) OR {table.customerNo}="" OR
ISNULL({table.customerXRef}) OR {table.customerXRef}=0 OR ...
 
This will suppress all the blank values, whichis the exact opposite of what you want, so negating it will give you what you need...
NOT(
ISNULL({table.customerNo}) OR {table.customerNo}="" OR
ISNULL({table.customerXRef}) OR {table.customerXRef}=0 OR ...
)
 
HTH
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 7:07am
Thanks for the help I will try it out and let you know how it works!
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Oct 2009 at 7:11am
YOu want to display any record wherer any of those fields are missing, correct?
Likely you just need a really long select statement with a tonm of ORs.
YOu will have to tweak this basedon the field types as I am guessing at them here.
isnull({table.customer#}) or
isnull({table.x-ref}) or {table.x-ref}="" or
isnull({table.first name}) or {table.first name}="" or
isnull({table.last name}) or {table.last name}="" or
isnull({table.address}) or {table.address}="" or
isnull({table.city}) or {table.city}="" or
isnull({table.state}) or {table.state}="" or
isnull({table.phone number)}
IP IP Logged
DBlank
Moderator
Moderator


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

 Sorry, typing at the same time but slower at posting Approve

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 7:23am
DBlank thanks for the help!
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 8:58am
Lockwelle & DBlank,
I tried both of your methods (one using suppression the other using record selection) both had the same result, it will suppress or only pull one blank field just fine but when you place multiples in the formula it will still only supress 1 any suggestions?
These are the formulas I used
 
Suppression
NOT({CUSTOMER.CUSTOMER_XREF_CODE}="" OR {CUSTOMER.LAST_NAME}="")
 
Record Selection
{CUSTOMER.CUSTOMER_XREF_CODE}="" OR
{CUSTOMER.LAST_NAME}=""
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 21 Oct 2009 at 9:18am
Question...is your suppression data per customer on one row or multiple rows of data? I (and I think lockwelle) assumed that all of your data is all on one row per customer.
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:25am

it is on one row per customer but there are 80,000+customers

it looks like this per customer
{customer#}{customerxref}{Last Name}{First Namet}....
IP IP Logged
lockwelle
Moderator
Moderator


Joined: 21 Dec 2007
Online Status: Offline
Posts: 4372
Quote lockwelle Replybullet Posted: 21 Oct 2009 at 9:35am
I know I asked before, but are the values being missed NULL in the database.  After all, NULL <> "", which might be part of the issue.  Though they wil both display the same on a report.
IP IP Logged
Page  of 2 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.031 seconds.