Sunday, March 24, 2013

OBIEE 11g - Multiple values in single column using union in advanced tab


Let us start with the SH subject area. We would be combining Calendar_Year,Product_category and Customer_City_Id in a single column with a fact column Amount Sold.

Create an analysis with 2 columns Calendar Year and Amount Sold as shown below.


Go to Advanced tab and copy the SQL.


Do not save the report. Open/Create a new analysis with 2 columns.

Go to the Advanced tab and click on the button 'New Analysis' 



Now simply paste the SQL from the earlier analysis.



First of all modify this sql as shown below. Look carefully the above and below snap and the sql. 


And press OK.



Observe that Year and Product category values in the same column.
Now again do not save the report, go to Advanced tab and copy the SQL.

Create a new analysis with 2 columns Customer City  and Amount Sold.


Go to Advanced tab and click on the button New analysis.



Now paste the sql from the earlier analysis. 



Modify the SQL - Observe 2 snaps ( above one and the below )

 
Press OK.

Now here you can rename the first column.