CODEWITHSUNDEEP
sqlcrystal-reportsnull
Jon Clay
Jon Clay

Reputation: 11

NULL values in Crystal Reports

I am trying to produce a Crystal Report showing MAIN and ALTERNATE contact details for our customers.

All works fine if the customer has both types of addresses (i.e. MAIN and ALTERNATE) but no report comes out at all for customers who only have a MAIN address. So, to summarise, all customers will have a MAIN address but only some will MAIN and ALTERNATE addresses.

I need to get the report to print sheets for all customers where or not the second address (in this case in our database as Addresses2.AddressType is ALTERNATE or NULL).

My selection formula is shown below:

{SubscriptionMembers.Current} and
{Categories.CategoryType} = "M" and
{SubscriptionMembers.MainSubscription} and
{Addresses.AddressType} in ["MAIN"] and
{Addresses2.AddressType} in ["ALTERNATE"] and
{Addresses.Country} = "United Kingdom" and
not {SubscriptionMembers.Lapsed} and
not {Contacts.Deleted}

Upvotes: 1

Views: 608

Answers (3)

James Deam
James Deam

Reputation: 49

Because only some have an 'Alternative address', the selection formula is only going to show customers with a 'main' AND 'alternative'.

I assume every customer has a main address A way I would deal with this is take out {Addresses2.AddressType} in ["ALTERNATE"] and and just drop the 'alternative' address details in the same section(group) as the main address.

As long as these are linked correctly it will display every customer from your other selection parameters, therefore will show 'main' and the customers who have an 'alternative' address. How it should look:

Group - CustomerID

'MAIN' address details here               'ALTERNATE' address details here

Group - Details

'DATA' here

Upvotes: 0

Siva
Siva

Reputation: 9101

Since you need To get customer in either case then one way would be dont take address in record selection formula. Instead create a formula as to check null for address2

If isnull ({Addresses2.AddressType} in ["ALTERNATE"]) then your code Else If (Addresses.AddressType} in ["MAIN"] or {Addresses2.AddressType} in ["ALTERNATE"]) Then your calculation

This way you can handle easily

Upvotes: -1

Arvo
Arvo

Reputation: 10570

I would recommend to use SQL Expression Field, containing something like (isnull({Addresses2.AddressType}, 'ALTERNATE')) and use this field in record selection formula - this way you do not depend on report options about handling NULL values etc.

Upvotes: 0

Related Questions