Home / Tech Tip / Crystal Tech Tip / Crystal Reports – Examining Join Enforcement

Crystal Reports – Examining Join Enforcement

Question: When would you want to change the Link Enforcement options in the Database Expert?

Answer:

  • Limit the results of report based on the values from another table without using a WHERE or Selection criteria.
  • Limit the results of your report without having to put unnecessary fields on your report and suppress them.

For example:

Issue: List just the customers who have ordered without having any fields from the orders table on the report.

Solution: Inner Join FROM Orders TO Customers and Enforce Join = Enforced From

Enforced From: Will include the FROM table in the query even if no fields from the FROM table are used in the report.

  1. In the Database Expert – Links Tab, right-click on the join line and selecting Link Options.

  1. Select Enforced From in the Enforce Join options:

  1. Only include fields from the TO (customers) table on the report.

  1. Take a look at the FROM clause in the Show SQL Query (Database>Show SQL Query). The Orders table IS being included and is the reason for the record count of 830.

  1. If we left the Join Enforcement as Not Enforced in this case. Notice the SQL query statement… which results in 91 customers (all my customers, not just ones that ordered).

  1. On the original report, suppress the customer id and customer name fields. Then, do a distinct count on customer id and notice only 89 customers (not complete list of 91 customers) appear on the report.

About ckruger

Check Also

Let's Speak BO Webinar Now You See Me, Now You Don’t! August 8 2017

Webinar: Now You See Me, Now You Don’t!

Join us and Creative Technology & Training Solutions’ Michael Ward, as he demonstrates and discusses …

Leave a Reply

Your email address will not be published. Required fields are marked *