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

[chonchos]

Hello Tek-Tips members!

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.

[pre]
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
[/pre]
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.

[pre]
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
[/pre]

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!

 

[chonchos]

Well,

I've figured out a very awful way to do it. But I'd prefer something a little more... "professional"?

I used the select expert on the email field.

Starts with "A-Z" in capitals, "a-z" in lowercase, and 0-9. Pretty sure you can't start an email address with special characters. At least I hope. So for the time being this is working. It kicked all the other lines I didn't need and is only showing contacts with something in the email field.

But it's such a juvenile way to get about what I'm trying to do.

So I'll still happily take some suggestions!

 

[PeterDimitrov]

Did you try to add this to the record selection formula:
ISNULL(<emailfield>) OR <emailfield><>""

I would prefer to add a SQLExpression. It depends on the database type. For SQLServer it will be
ISNULL(<emailfield>,'')
then in the record selection formula add

<sqlexpressionname> <>''

http://www.R-Tag.com

Viewer and Scheduler for Crystal reports, SSRS and Dynamic Dashboards.

 

[Danster]

Best way to do this (in fact I'd say "the proper way") is to suppress the details section if email = "" i.e empty string.

Right click the word on the left of your screen called Details, to the left f the design canvas. Go to Section Expert. Click the X+2 button next to Suppress.
This gets you into the suppression formula.

Type in {table.field} = ""
When it is true, the row will be suppressed. Obviously, change the {table.field} to match your own data for the email field.

Cheers

 

[pmax9999]

Actually, PeterDimitrov's answer was a much better solution. It is always more efficient to exclude unwanted records at the database level than return the additional rows to the local PC and use Crystal Reports to suppress them.

Cheers
Pete

 

[JamesFlowers]

I would create two groups ,

create a formula , as PeterDimitrov's suggestion

ISNULL(<emailfield>) OR <emailfield><>"" then group by this to show

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
Smith Company
Jane Smith 555-123-4567 Accountant 1/1/1999


this would then trap the data that doesnt have emails , so they can be filled in to give more complete data

James Flowers
Crystal Consultant