How to suppress an entire "row" if one field is null?

[Former Member]

Hello!

I have googled all morning and cannot find an answer for this. A couple things regarding suppressing DUPLICATES, but not sure how to apply that to my situation. So here's what we have.

I've created a report that is grouped by company name. In the details section it shows the contact name, fax number, title, email address and date updated from the "rolodex" table (where fields are applicable).

It's a very simple and straightforward report. But I will try to show an "example" anyway.

Smith Company                                                          

            John Smith       555-123-4567  President         PresidentJohn@SmithCo.com          1/1/1999

            Jane Smith       555-123-4567  Accountant                                                                     1/1/1999

            Jacob Smith     555-123-4567  Sales Mgr        SalesMgrJacob@SmithCo.com       1/1/1999

        Janet Smith                                                               GopherJanet@SmithCo.com            4/29/2002

        Joe Smith                                                                                                                                 6/6/2006

What I would like to be able to do is... suppress the "rows" that do not have an email address.

For my purposes with this report, if they aren't showing an email address I really don't need to see their information. So I would like to see the following.

Smith Company                                                          

            John Smith       555-123-4567  President         PresidentJohn@SmithCo.com          1/1/1999

            Jacob Smith     555-123-4567  Sales Mgr        SalesMgrJacob@SmithCo.com       1/1/1999

        Janet Smith                                                               GopherJanet@SmithCo.com            4/29/2002

As you can see, Jane and Joe didn't have email addresses so their entire "row" is not showing.

I cannot figure out a way to make this happen.

I'm using Crystal Reports 2013.

I'll gladly answer any other information needed to help you help me.

Thank you in advance!

[abhilash_kumar · Accepted solution]

Hi Shauna,

You should use a record selection formula.

Go to the Report option on the top > Selection formulas > Record. Use this code:

{Email} <> "" OR Not(isnull({Email}))

-Abhilash

[Former Member · Accepted solution]

It looks like this will work!

However, I also have selection criteria based on Company Codes. So now it's filtering out the blank email lines... but it's showing me EVERY company in our system.

I added an "and" after the code you suggested followed by the original selection formula.

Example:

{ROLODEX.EMAIL_ADDRESS}<> "" or not(isnull({ROLODEX.EMAIL_ADDRESS})) and

{COMPANIES.COMPANY_CODE} in ["AASD2", "ASL7", "CACI3", "CCMF1", "CVA2", "EAOD1", "EPPS1", "OASS2", "PKP1", "PTKL1", "RASM2", "TA1", "ZAC1"]

Is there a different way to go about this? Maybe something other than "and"?

[abhilash_kumar · Accepted solution]

(

{ROLODEX.EMAIL_ADDRESS}<> "" or not(isnull({ROLODEX.EMAIL_ADDRESS}))

)

and

{COMPANIES.COMPANY_CODE} in ["AASD2", "ASL7", "CACI3", "CCMF1", "CVA2", "EAOD1", "EPPS1", "OASS2", "PKP1", "PTKL1", "RASM2", "TA1", "ZAC1"]

Notice the round braces I added.

-Abhilash

[DellSC · Accepted solution]

This is because of the way that "or" works in the selection criteria.  Also, you need to test for null first because of the way that null works.  See What is Null and Why is it Important for Crystal Reports | SAP BI BLOG for my explanation of how null works.

So, I would add parentheses around the "or" clause and change it to this;

(

not(isnull({ROLODEX.EMAIL_ADDRESS})) or {ROLODEX.EMAIL_ADDRESS}<> ""

)

and

{COMPANIES.COMPANY_CODE} in ["AASD2", "ASL7", "CACI3", "CCMF1", "CVA2", "EAOD1", "EPPS1", "OASS2", "PKP1", "PTKL1", "RASM2", "TA1", "ZAC1"]

-Dell

[DellSC]

This is not difficult!

1.  Right-click in the gray area to the left of the section you want to selectively suppress and go to the "Section Expert".

2.  On the right-hand side of the screen you'll see a checkbox next to the word "Suppress" and then a button to the right of that.  DO NOT check the checkbox!  Instead, click on the button to the right of the word "Suppress".

3.  Enter something like the following formula:

IsNull({email field}) or {email field} = ""

4.  Save and close, then close the Section Expert.

This should now suppress the section if there is no email address.

However, I see that for "Jane Smith" you have just the text "Accountant" where the other rows are showing an email address.  If the field that contains the email address may have other data in it, you can add something like this to the end of the formula in step 3 above:

or InStr({email field}, "@")

This will also suppress rows where the field doesn't contain a full email address.

-Dell

[Former Member]

This did not work for me. Not sure why. However, the answer you provided above as a reply to Abhilash Kumar does work.

Thank you very much for your help.