Choosing Table @ Run Time of Report: Business Objects

Choose a Table at the Run Time:

I have seen this peculiar problem where people are asking to choose the Table at the run Time. First time if you think about this, it seems very odd, why there is such a requirement to do that.

I have found a way to do that and I want to share this today.

Lets say I have Table Finance (FI), Accounts Payable(AP) and Accounts Receivable(AR). All these tables have the same columns and the data types. At the Run Time User want to see the data either from Table FI, AP or AR.

Finance (FI) 

Accounts Payable (AP)

Accounts Receivable (AR)

So these tables have the same columns and they should get the prompt at the run time to select the data from FI, AP or AR Table.

To achieve this scenario, first create a derived table with the Union of these tables.
Select FI as Table_Name, Account, Type, Sales from Table_FI
where 'FI' = @prompt('Select Table','A',{'FI','AP','AR'},mono,constrained)
Union ALL
Select AP as Table_Name, Account, Type, Sales from Table_AP
where 'AP' = @prompt('Select Table','A',{'FI','AP','AR'},mono,constrained)
Union ALL
Select AR as Table_Name, Account, Type, Sales from Table_AR
where 'AR' = @prompt('Select Table','A',{'FI','AP','AR'},mono,constrained)

With this definition of the derive table , we will have four columns with the following result set.

Derive Table

With the report created on top of such s derive table, the User will get a prompt to Select Table with list of values FI, AP & AR. These are the dummy values which defines what table to select at the run time.
When User select the value FI, first 2 rows will be shown in the result, for AP 3 & 4 rows will be shown and for AR, last 2 rows will be shown in the report.

I have shown this technique for one table which can be scaled to implement with a large set of tables which are joined with one another. In that situation all the tables first need to have a individual derive table with the Union ALL conditions. One Derive table can have the @prompt condition and that should drive to filter the data from the rest of the tables which are joined with this derive Table. Also we need to use the Table_Name in the join conditions as well.

This technique can also be implemented in IDT where your Tables are stored in multiple databases.

I hope you enjoy this

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 :)