Part 2 - Conditional Joins in Crystal Reports ? ! ?
This is the second blog in a series for using Crystal Reports with Sage 100, Sage 500, and Sage X3. Previous blog was Pulling Fields From Other Tables Into Your "Work" Table Without a Join.
Problem: What can you do when you need to join two or more tables but the data relationship is dependent upon variable values?? It doens't work to use the Database Expert Links tool to set up conditional relationships. So what are your options?
Solution: Cheat! Use the Select Expert to set up conditional relationships that you would otherwise not be able to do in Database Expert.
Example: We needed historical invoice header/detail information based upon what vendor categorization (a user defined field) is selected at report run-time. The entered report parameter value, in this case “Consign”, “Direct”, or “Both” (consign and direct only, exclude all other types), becomes the join criteria for the underlying table.
*** Setting up the filter to function as a join tool significantly reduces the number of records returned, improving performance, and reduces the headache of having to build
conditional suppression which often has unwanted running total repercussions. ***
Steps:
- Use the Database Expert to select the tables that you need and set the linking as makes sense up to the point of your conditional requirements.
- Use the Select Expert to create the conditional relationship. Take a look at the sample, below (click the picture for the raw text):
Obviously, you can add it whatever additional criteria (in grey text above) you need for your report. The black text is specific to the subject.
Nifty trick and very effective for slimming down your report results for performance and continued report development. If you missed it, the first blog in the series Pulling Fields From Other Tables Without a Join. Part 3 in the Crystal Report series will publish on Aug 30th, outlining how to create multiple versions of one report.
If you have any more questions on how to be more effective with Crystal Reports contact RKL eSolutions at 888-222-8827.
