Question: When would you want to change the Link Enforcement options in the Database Expert?
- 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.
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.
- In the Database Expert – Links Tab, right-click on the join line and selecting Link Options.
- Select Enforced From in the Enforce Join options:
- Only include fields from the TO (customers) table on the report.
- 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.
- 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).
- 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.