suppressing
Printed From: Crystal Reports Book
Category: Crystal Reports 9 through 2020
Forum Name: Report Design
Forum Discription: The best way to design a report and problems you have encountered
URL: http://www.crystalreportsbook.com/forum/forum_posts.asp?TID=8078
Printed Date: 27 Apr 2024 at 9:58pm
Topic: suppressing
Posted By: gizmosilly
Subject: suppressing
Date 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
|
Replies:
Posted By: lockwelle
Date 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 |
Posted By: gizmosilly
Date Posted: 21 Oct 2009 at 7:07am
| Thanks for the help I will try it out and let you know how it works! |
Posted By: DBlank
Date 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)}
|
Posted By: DBlank
Date Posted: 21 Oct 2009 at 7:13am
|
Sorry, typing at the same time but slower at posting |
Posted By: gizmosilly
Date Posted: 21 Oct 2009 at 7:23am
| DBlank thanks for the help! |
Posted By: gizmosilly
Date 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}="" |
Posted By: DBlank
Date 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. |
Posted By: gizmosilly
Date 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}....
|
Posted By: lockwelle
Date 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. |
Posted By: DBlank
Date 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)
|
Posted By: gizmosilly
Date 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. |
Posted By: lockwelle
Date 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.
|
Posted By: lockwelle
Date 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.
|
Posted By: DBlank
Date 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
|
Posted By: lockwelle
Date 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.
|
Posted By: gizmosilly
Date 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
|