Hi Greg,
I read the replies to your question, but don’t think these are completely true. First of all: do not use this checkbox, because it is possible to get results you really don’t want. I’ll try to explain this with an example (but the subject is still giving me headaches, so I hope I’m being clear).
Imagine a universe with three tables. Customer - Order - Orderline. (Customer:Order=1:n; Order:Orderline=1:n) Tables contain these values:
Customer: Cus_id,Name
(1,a) (2,b)
Order: Cus_id,Ord_id,Total
(1,1,10) (1,2,15) (2,3,10) (2,4,20)
Orderline: Ord_id, Ord_l_id, Amount
(1,1,6) (1,2,4) (2,4,8) (2,4,7) (3,5,3) (3,6,7) (4,8,20)
Having this implemented in your universe, BO could generate the following SQL:
Select Cus_id, Ord_id, sum(Total), sum(Amount) From Customer, Order, Orderline
Where Customer.cus_id = Order.cus_id
and order.ord_id = orderline.ord_id
Group by cus_id, ord_id
This is called the fan trap (in stead of chasm trap). Your database will do this:
1, 1, 10, 6
1, 1, 10, 4
1, 2, 15, 8
1, 2, 15, 7
2, 3, 10, 3
2, 3, 10, 7
2, 4, 20, 20
Your results will be:
Customer 1, Order 1, Total 20, Amount 10 Customer 1, Order 2, Total 30, Amount 15 Customer 2, Order 3, Total 20, Amount 10 Customer 2, Order 4, Total 20, Amount 20 Apparently you would like the totals to be equal to the amounts.
This is why Business Objects came up with the idea of the check box your question is about. If you have this check box checked, Business Objects will generate two SQL’s. One to sum the totals for the customers, one to sum the amounts for the orders. The results will be joined at the client instead of the database. Now you will get the right results.
Still you should not use the check box because when you take a condition on orderline into your querie, Business Objects will still create two SQL’s, but it will take the condition into both SQL’s, which results again into the wrong result for the customer-order-querie (because the querie joins through to orderline).
When you really want to solve this problem, you’ll have to create an alias and two contexts (which Business Objects won’t detect for you, because it only detects contexts within loops). You’ll have to alias order and have one context containing customer and order_alias and one context containing order and order_line. All measure objects selecting order now have to select order_alias. By this you’ll prevent BusinessObjects from stepping into the fan trap.
This solution seems inefficient, but it is the only way to get around the shortcomings of SQL and get meaningful querie results.
I really hope this explanation is somewhat clear.
Best regards,
Susanne Bakker
ING Netherlands.
Listserv Archives (BOB member since 2002-06-25)