Business Objects Universes - joins to restrict data

Question

I'm very new to universe design and would really appreciate a point in the right direction...

I have a table Sales that records details of sales made by staff members. It includes the following fields:

• SaleID
• StaffID
• SaleDate

I also have a table StaffDepts that has the historic department information for each member of staff:

• StaffID
• DeptName
• StartDate
• EndDate

I would like to build a universe that pulls through the name of the department in which the member of staff was placed on the date of the sale.

I tried linking the two tables with three joins in Designer (I amended the Expression box in the Edit Join window of each join):

1. Sales.StaffID = StaffDepts.StaffID
2. StaffDepts.StartDate <= Sales.SaleDate
3. (StaffDepts.EndDate IS NULL) OR (StaffDepts.EndDate > Sales.SalesDate)

This failed pretty spectacularly! Can any suggest how I can achieve what I am trying to do?!

Many thanks!

Answer 1

You've created three separate joins in Designer, but I believe you really want them all to work as one. You can (and if I understand what you want to do here, should) create only one join in Designer and include all of the clauses above in it with AND.

I'm assuming the spectacular fail was because by creating three separate joins, you'd introduced loops into your universe. You would only want to create separate joins if you needed the two tables to be joined differently in different contexts -- and in that case, you would create those separate contexts and assign the joins to them accordingly.