Dealing with Inner/Outer Joins

[megs7]

Hi,

We would create a BO universe and deploy it so that the users can create ad-hoc reports from the universe.
The issue here is how do we define the joins in the universe. I have explained in the below e.g.:

Assume, there are 2 tables: BIN and INVENTORY. Here are the sample contents of these tables:

BIN Contents
BIN_NUM BIN_NAME
1 Bin1
2 Bin2
3 Bin3

INVENTORY Contents
ITEM_ID BIN_NUM
a 1
b 1
c
d 3

Possible Reports:
We can imagine that end users may want to produce the following reports.

1) List bins and the items they contain
BIN_NAME ITEM_ID
Bin1 a
Bin1 b
Bin3 d

2) List bins and the items they contain. Display null as ITEM_ID for every bin that does not contain an item

BIN_NAME ITEM_ID
Bin1 a
Bin1 b
Bin2 Null
Bin3 d

3) List items and the bin each is in. Display null for bin if an item is not in a bin

BIN_NAME ITEM_ID
Bin1 a
Bin1 b
Null c
Bin3 d

4) List all bins and items. If a bin is empty, display null for item; if an item is not in a bin, display null for bin

BIN_NAME ITEM_ID
Bin1 a
Bin1 b
Bin2 Null
Null c
Bin3 d

These reports are produced using inner join, right outer join, left outer join and full outer join respectively.

Questions
If users of the ad-hoc reporting tool may want to produce any one of these reports at any time then:
1) What do we specify in the universe.
2) What will end-users see and how do they differentiate between the different types of report.

Please note that the users are very very basic users and they are very new to the tool. (They will not know how to modify SQL or add a filter etc.). All that they would do is just drag and drop. I also thought of aliasing the Inventory table, but that would again involve lot of confusion cos there are many such tables that would fall in this category. Creation of alias tables would again make things complicated, having multiple objects and chances that the user might pull both these objects while creating the report.

Please let me know if any of you have found some solution for this scenario and what is the best possible option to implement.

Thanks,
Meghana

 

[blom0344]

If adding a filter is beyond your user's capabilities than this will become very much a problem. Using filters is the most important asset there is!

If you want this absolutely idiot-proof you may consider creating a universe for each scenario (I know, that goes a bit far) or create the sets 4 times , each in their own folder within their own context and deny the use of cross-joins. However, that will generate error-messages that may lead to some confusion.

Ties Blom

 

[skrandel]

Don't be ridiculous. You MUST provide some user training. Teach them how to choose the proper filter.

Steve Krandel
Intuit