This exercise has been carried out on OBIEE 11.1.1.6
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.
Done !!!
Thank you.
This is one more useful option in OBIEE 11g
Look at the Years.
Look at the Years and Amount Sold.
There is no Amount for the Year 2002 but client wants zero to be displayed for that year.
Now this is where the option Add custom Calculated Item comes handy.
This enables us to display zero and that too at front end level.
Right click on Year 2001 and follow the steps as shown below - Click on Add Custom Calculated Item
Label is 2002 and value is 0.
Table and graph -
The exercise is based on an option which is nothing very new to OBIEE 11g but found very useful while working on one kind of requirement.
On the answers page of one specific subject area - we actually wanted to have many views with different columns - we managed to show them as a group on the dashboard.
The option Create Compound Layout is very useful.
I created 3 views and renamed them. All the 3 views have different columns of the same subject area.
If you put this report on the dashboard - only one view will be visible -which is the obvious behavior.
So from the Home page of OBI - go to the Edit tab of this report and copy the URL.
Now on the dashboard properties - Add the Embedded Object and paste this URL - Increase the frame size so that this will look like -
Thanks.
I am using
Platform:OBIEE 11.1.1.6 on windows 7- 32 bit
Microsoft Outlook : 2010
Browser:IE8
I was trying to deliver a simple report ( Pivot view + Gauge view ) directly to my outlook mail account without selecting any kind of attachment option.
Now just have a look at the Front end report first.
Now look at the outlook mail snapshot. Observe the formatting of pivot view. It is an incomplete image.
(In gmail - Pivot/table view gets converted to plain text view
In yahoomail - Pivot/table view is no more present)
After playing around with the tool - I found one workaround.
I went to pivot table view properties and changed the border type to "ALL" - as shown below -
Save the changes and run the agent again.
Now look at the Outlook mail.Pivot view is complete.
Pivot view formatting is now fine.
Thanks.
NOTE : The Pivot/Table view display behavior is not normal in gmail, yahoomail and outlook and a bug has been registered.
<Bug : 14078840><TABLE/PIVOT VIEW AGENT NOT PROPERLY SHOWN UP IN OUTLOOK MAIL>
Looks like I wasted a big time in displaying the city names (horizontally) in a single cell separated by space in my earlier post. The following way is bit easier.
Take a column city in your workbook and open the narrative view.
Select HTML - Use this code for ex: <a> @1 </a>
Note : In the Rows to display option I have selected 100 - But in order to show all the city names, you have to increase the parameter value until you see all the names in the output below.
Let us rename the narrative view ( new feature in OBIEE 11.1.1.6) and change the font color.
Remove extra views from the answer. Look at the result.
Thanks.
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 !!!
Hello Friends
Have a look at the snapshot below - Date format.
Let us try to change the format using Evaluate function - This is just to check the Evaluate functionality in OBIEE 11.1.1.6
Error message is very much explanatory.
We have to set the Evaluate setting in the NQSConfig.ini - By default it is ZERO - so change this setting.
Restart the BI services. Evaluate will work.
Thanks.
