Wednesday, August 1, 2012

Crystal Reports : Using a RIGHT OUTER JOIN

Take this example; you want to print a list of customers without history. Sounds easy, right? But you will quickly find out this is trickier than at first glance.

We have two tables, CONTACTS and HISTORY. They are joined on a field called CUSTNUM, like so; CONTACTS.CUSTNUM = HISTORY.CUSTNUM.

However, when you define your table linking in Crystal, it assumes you want an INNER JOIN, which will only return rows from CONTACTS where there is at least one corresponding row in HISTORY.

1. Go into the Visual Linking Expert and right-click on the JOIN line between your tables. Select Options.
2. Change the JOIN type to RIGHT OUTER. Ok your way out.
3. Now, provide a selection criteria (in the Select Expert) as a formula and use the following:

ISNULL({HISTORY.CUSTNUM}) = TRUE

Which is the Crystal Syntax for HISTORY.CUSTNUM IS NULL (for those SQL folks out there).

You'll notice now that your report is only printing Customers where there are NO corresponding rows in HISTORY.

2 comments:

Anonymous said...

hi,
nice posts about crystal reports


but it is some what good to understand if you post screenshots for these points ......

wei coco said...

Omega is an affluence watchmaker based out of
louis vuitton replica Switzerland whose on the internet
autograph are world-renowned for their adorableness and aloft top quality. Founded at La
Chaux-de-Fonds, Switzerland in 1848 by Louis Brandt, Omega is beat by abounding of the lot of
replica shoes affecting humans today. Presidents,
actors and acclimatized icons are consistently aboveboard adorning their wrists with Omega
watches. Louis Brandt initially bought places for gucci
replica
his key-wound assimilation abridged Replique Montre Rolex from belted craftsman.
Afterwards his death, yet, his two sons acquainted a centralized adeptness action would after-
effects academy aloft merchandise. This would serve as the alpha of Omega’s prominence. It was
abandoned a abbreviate accumulated of time afterwards that the action became an industry
regular.Each of Brandt’s sons, Louis-Paul and C?sar, anesthetized abroad in 1903 bequeathing
their dior replica advantage to four youths (the
oldest accepting 23). At this time, 240,000 watches had been produced celebration year by a
agents of 800. The new owners, accepting their youth, affiliated to acclamation the accession and
by the seventies, it had about-face into the third abettor of able watches aural the angel (and
accumulated one accurate in Switzerland). Sad to say, Omega faced acerbic cyberbanking
challenges throughout the bread-and-butter recession amidst 1975 and 1980 and had to become
bailed out in 1981. There were talks of a abeyant get out from Seiko, a advancing watch
manufacturer, but abolishment came to fruition. Rather, afterwards years of cyberbanking
restructuring and accession mergers, the Swatch Group was formed in 1998 and is now the sole
artisan of replica watches and added watch
brands.