Note this exercise is based on Customers table of SH schema that comes with Oracle database ( 11g for ex )
Requirement is to display all the city names in a single cell.
There are 2 ways - 1) using WM_CONCAT function 2) Row-wise initialization ( not good as far as performance is concerned as we are going to create session initialization boxes )
Way 1:
Create one repository variable initialization block with the SQL as shown below-
SELECT REPLACE(WM_CONCAT(DISTINCT CUST_CITY),',',' ')
FROM CUSTOMERS WHERE CUST_CITY LIKE 'Z%'
( Note - Total count of cities is very large and here in my case if I do not put a filter on CUST_CITY - I will get an error saying "Empty Result Set" - This is because the resultset is too big to handle.
So we have to test until OBIEE does not throw this error. So we can rebuild the query like this -
SELECT REPLACE(WM_CONCAT(DISTINCT CUST_CITY),',',' ')
FROM CUSTOMERS WHERE CUST_CITY LIKE 'Z%' OR CUST_CITY LIKE 'Y%'
Go on adding filters on CUST_CITY until you do not get the error while testing the BOX.
So for remaining city names - You may to create another repository block. Here I am using very simple queries as I am just testing the approach.
Look at the box - STRING1
Have a look at another box - STRING2
Save the changes - Go to front end - Reload the files and metadata. Refresh the browser.
Take CITY_NAME column for example in a workbook and this type of column formula.
Sometimes I found union query does not return all records in the front end - In this case we have to restrict the box filter with only one Letter. This is entirely depends on your dataset.
Way 2 :
Create one session initialization box with the query as shown below. DEMO1
Row wise initialization is enabled for this box. Note You have to put filters on city names else the box testing will give an error. ( at least in my case resultset would be bigger )
SELECT DISTINCT 'VAR',CUST_CITY FROM CUSTOMERS
WHERE CUST_CITY LIKE 'Z%'
Create another session initialization box - DEMO2 ( Execution Precedence DEMO1)
SELECT REPLACE('VALUEOF(NQ_SESSION.VAR)',';',' ') FROM CUSTOMERS
Save the changes.Go to front end - Reload the files and metadata. Refresh the browser.
Thanks,
Have a nice day ahead !!!
Requirement is to display all the city names in a single cell.
There are 2 ways - 1) using WM_CONCAT function 2) Row-wise initialization ( not good as far as performance is concerned as we are going to create session initialization boxes )
Way 1:
Create one repository variable initialization block with the SQL as shown below-
SELECT REPLACE(WM_CONCAT(DISTINCT CUST_CITY),',',' ')
FROM CUSTOMERS WHERE CUST_CITY LIKE 'Z%'
( Note - Total count of cities is very large and here in my case if I do not put a filter on CUST_CITY - I will get an error saying "Empty Result Set" - This is because the resultset is too big to handle.
So we have to test until OBIEE does not throw this error. So we can rebuild the query like this -
SELECT REPLACE(WM_CONCAT(DISTINCT CUST_CITY),',',' ')
FROM CUSTOMERS WHERE CUST_CITY LIKE 'Z%' OR CUST_CITY LIKE 'Y%'
Go on adding filters on CUST_CITY until you do not get the error while testing the BOX.
So for remaining city names - You may to create another repository block. Here I am using very simple queries as I am just testing the approach.
Look at the box - STRING1
Have a look at another box - STRING2
Save the changes - Go to front end - Reload the files and metadata. Refresh the browser.
Take CITY_NAME column for example in a workbook and this type of column formula.
Sometimes I found union query does not return all records in the front end - In this case we have to restrict the box filter with only one Letter. This is entirely depends on your dataset.
Way 2 :
Create one session initialization box with the query as shown below. DEMO1
Row wise initialization is enabled for this box. Note You have to put filters on city names else the box testing will give an error. ( at least in my case resultset would be bigger )
SELECT DISTINCT 'VAR',CUST_CITY FROM CUSTOMERS
WHERE CUST_CITY LIKE 'Z%'
Create another session initialization box - DEMO2 ( Execution Precedence DEMO1)
SELECT REPLACE('VALUEOF(NQ_SESSION.VAR)',';',' ') FROM CUSTOMERS
Save the changes.Go to front end - Reload the files and metadata. Refresh the browser.
Thanks,
Have a nice day ahead !!!