Business Objects: Optional Join in the Universe!

How to achieve Optional Join in the Universe:

We know about the optional prompt in the report but the optional join is something that we never heard or thought of in the Universe. While working on some project, I came across a problem where I need to make optional join depending on the values of the columns in the dimension table.

In this blog, I am going to discuss the same scenario here.
The Reporting requirements were such that we required 20 prompts in the Reports and the User don't want to answer the 20 prompts at the run time, but they wanted to have a predefined table in the database and the report should pick up those prompts from the table itself. They just wanted to chose the reporting date and the report template, the rest of the prompts should be picked up from the predefined table.

To make this scenario more clear, let me take two tables A and B. Lets say table A is the Fact table and a reporting table that contains the parameters of the report which is the driving table.

Table A

Table B

We only need 2 prompts at the report, Reporting Date and the Reporting Template. So if some one selects the Reporting Date 1-Sep-2014 and the Report X, we should get the rows 1 &3 from the Table A and we should get Sales of $600.

To achieve this, I made 3 separate joins from Table A to Table B.
Join 1: TableA.Company = Case When(TableB.Company <>'ALL') Then TableB.Company End OR Case When(TableB.Company <>'ALL') Then 'ALL' = 'ALL'

Join 2: TableA.Business_Unit = Case When(TableB.Business_Unit <>'ALL') Then TableB.Business_Unit End OR Case When(TableB.Business_Unit <>'ALL') Then 'ALL' = 'ALL'

Join 3: TableA.Account = Case When(TableB.Account <>'ALL') Then TableB.Account End OR Case When(TableB.Account <>'ALL') Then 'ALL' = 'ALL'

So if we select Report X, Only Join 1 and Join 3 Should get into picture and Join 2 should act as optional Join.  With the above written case statements, this will work and we will get the desired output.

So if we select Report Y, Only Join 1 and Join 3 Should get into picture and Join 2 should act as optional Join.  With this criteria, we need 0001 Company and ALL Business Units but Account Should only be 4444. This will restrict the data to only row 2 and we will get a Sales of $200 only.

So if we select Report Z, Only Join 2 Should get into picture and Join 1 and Join 3 should act as optional Joins.  With the above written case statements, this will work and we will get both the Companies and all Account but the Business Units should be 12345. This will lead to rows 1 and 4 and the Sales will be $400.

The main logic of the join is that only one part of the OR condition will work which will depend on the Value of the Column is ALL or not  and this make the join condition either working or optional. 

This is my first blog and I hope you guys like this :)