Report Design
 Crystal Reports Forum : Crystal Reports 9 through 2020 : Report Design
Message Icon Topic: Report not showing any data when it is blank Post Reply Post New Topic
Author Message
jon.clay.irm
Newbie
Newbie


Joined: 31 Oct 2014
Online Status: Offline
Posts: 2
Quote jon.clay.irm Replybullet Topic: Report not showing any data when it is blank
    Posted: 26 Nov 2014 at 12:32am
Hi there everyone

I have a problem within Crystal Reports v11 that I'm pulling my hair out over, and I wondered if someone could please help me.

I have a report containing various tables, but particularly a table called Addresses along with an alias table of Addresses called Addesses2. The report is grouped by ContactID so each of our contacts should receive a report showing the details that we hold on our database about them.

Both tables contain a column called AddressType. My report is pulling out address details from the Addresses table where AddressType = 'MAIN'. So far all is working fine.

I am also asking it to pull out address details from the Addresses2 table where AddressType = 'ALTERNATE'. This works fine if an incidence of AddressType = 'ALTERNATE' exists.

However, if this doesn't exist then that particular customer is not receiving a report at all.

Ideally what I want is a customer report showing all details where Addresses.AddressType = 'MAIN' and also the details where Addresses2.AddressType = 'ALTERNATE' and a blank section if Addresses2.AddressType = 'ALTERNATE' does not exist.

I've tried to explain this in the easiest way possible, so I hope it makes sense.

Many thanks,
Jon
IP IP Logged
z9962
Senior Member
Senior Member
Avatar

Joined: 04 Jul 2012
Online Status: Offline
Posts: 161
Quote z9962 Replybullet Posted: 26 Nov 2014 at 2:31am

As soon as you put a condition on the second table it becomes a enforced inner join.

 
Not sure why you have addresses twice? Can you not also group on AddressType? or filter in the main report where addressType in ["MAIN","ALTERNATE"]??
 
if you do require both then you will need to return addresses2.AddressType where = null or Alternate
IP IP Logged
DBlank
Moderator
Moderator


Joined: 19 Dec 2008
Online Status: Offline
Posts: 9053
Quote DBlank Replybullet Posted: 26 Nov 2014 at 4:20am
IMO - To clarify the outer/inner join issue
when you put a select criteria on an outer joined table it applies the filter after the join was made and the joined data set was 'created'. It is not necessarily the same as an inner join.
the inner join only selects items where the fields are matched on both tables .
the outer join selects all records from table 1 and only matching records from table 2.
If you then place a WHERE onto the outer join data set (use the select expert) it will often filter out items you were trying to get at in the outer join process but not necessarily all of them.
if you move the condition onto the outer join itself it alleviates the problem. You would need to use a command object or a stored proc to achieve this. 
Z has a reasonable approach but I think you will still need to do some manuevering to display NULL address types. likely doing a grouping and a GFa and GFb to show the two address types
 
 
IP IP Logged
jon.clay.irm
Newbie
Newbie


Joined: 31 Oct 2014
Online Status: Offline
Posts: 2
Quote jon.clay.irm Replybullet Posted: 28 Nov 2014 at 1:09am
Thanks guys. I'll take a look at this now.

Best wishes
Jon
IP IP Logged
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.018 seconds.