OBIEE – Write Back

Scenario – Your dashboard has a requirement to update a record in the database from OBIEE report.

Solution –
This requirement can be easily implemented with the help of Write Back feature of OBIEE. The solution will need few changes at the dashboard end, and xml file to support the requirement.

Assuming we have a table present in the Presentation Layer of OBIEE named as Population_details. The user needs to manually update the population for the different regions from the OBIEE Report. The following steps need to be followed to implement the requirement:


a) Go to Settings-> Administrator-> Manage Privileges, and check whether Write Back privilege is provided to “Everyone”. If not, you would need to grant this privilege.
You can also grant write-back privilege to any LDAP Group here.


b) Please note that the table should be made not Cacheable. This would ensure that we would get the results as and when we update or insert. This can be done by checking the Cacheable Property in the RPD.


c) Go to Answers and create a report to include the Region and the Population Column from the Population_details table. Edit the Column Properties of Population Column. Change the Value Interaction of the column to Write Back and click ok.

d) Go to the Results tab, go to the Table View. Click on the Write Back Properties Icon.

e) Select the ‘Enable Write Back’ check box in the dialog box. In the space given for the ‘Template name’ mention the name written for your web message name tag in your xml template. Please refer to (e) for more details. Fill in the details for the Button in the same dialog box.

f) Create a similar below .xml file with the name of your choice. This xml file has the SQL which is used by the OBIEE server to write back to database.



Note:
1. Incase the column is number, there is no need to surround with single quotes ‘’, however, it is important to include them for the alphanumeric characters.
2. Also make sure that you give the same name in the WebMessage name, as you have provided in the report in step (d).
3. If there is no insert script, keep a space between tags.

g) Browse to the path in OBIEE Presentation Server: OracleBI/web/msgdb/customMessages section and save your xml template for it to be used by the OBIEE report at runtime. In OBIEE 11g, you can use this path: ORACLE_INSTANCE/bifoundation/OracleBIPresentationServicesComponent/coreapplication_obipsn/analyticsRes/customMessages
h) Bounce the OBI Services to have the changes reflected.
i) Go back to the report and run the report.
j) In case you are using LDAP Groups, then go to the OBIEE Repository-> Manage-> Security-> Groups. Select the group you are using, right click and select Properties. Click on Query Limits.

a) Select ‘Allow’ from the list in Execute Direct Database Requests and click OK.

OBIEE – Navigation from one report to a different dashboard page without using value based interaction

Scenario – Your Overview dashboard page has a report, with a Description column. Based on the values in the description column, you need to navigate to different reports on the different dashboard pages. We need to drill through the same report to different reports on different dashboard pages.

Solution – HTML comes to rescue in this scenario. This change involves a small html code which needs to be used in the column description.

a. Consider a “Summary Report” report on Overview Page.
b. The report has a column called “KPI” with values as Inventory, Supplier_Scorecard and Sales
c. Depending upon the three values, we need to navigate to the Dashboard Page of Inventory Page, Supplier Scorecard Page and Sales Page which are already created.
d. In KPI column description, we can use ahref tag and navigate to the desired report on different dashboard page. Please refer to the Syntax section.
e. Change the Data Format for the “KPI” column to html.



Syntax –


where
/shared/folder_name/_Portal/dashboard_name: should be the path and name of your dashboard. &Page=page_name: should be the name of the dashboard page you want to navigate to. Value: should be what will be displayed in the KPI column value.

eg:


Note: I have used %20 if there is a space in between the words for the folder name, dashboard name or dashboard page name

Since here we have all the three values existing in the same table column, so we would need to use Case Statement.



Paste the above Case Statement in KPI column description in the criteria tab.

Advantages:
1. No usage of different guided navigation links for navigating to the different reports.

OBIEE – Using single GO button for all prompts rather than multiple

Scenario – Your dashboard page has multiple prompts. The reason you have created multiple prompts is because few columns have constraint and you cannot club them together with non-constraint columns in the prompt, as value of any one column in the prompt will affect the values of constraint column. So you end-up creating different prompts. But the issue with different prompts is that multiple prompts will have different GO buttons. But the users do not want to click on different GO buttons for their values to reflect in the underlying report.

Solution – This scenario can have a Single GO Button as a solution, which can be created in addition to the different GO buttons coming by default with the different prompts. We will call this additional GO Button as the Global GO Button. This Global Go Button will take all the dashboard prompts filter entries and will submit them to the reports. Follow the following steps to create single GO button on the dashboard page. This change involves scripting/modifying some of OBIEE js files.

a. Add a new function to globalfilterprompt.js. This file resides into multiple locations
\OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res\b_mozilla\prompts
And
\OracleBI\web\app\res\b_mozilla\prompts.

If you are using OC4J, then add the following code in the former location and if you use Web Application Server, then add the code in the file at the later location.

Add the following code to the js file –

//Custom Function To be appended for the Custom Global Go button
function GFPDoFilters_samvi(sViewID, tTable, bGFPReloadInline){
if(sViewID==ksGFPStatePath)
return;

var tExpr = XUICreateElement(saw.xml.kSawxNamespace, 'expr');
tExpr.setAttribute("xsi:type", "sawx:logical");
tExpr.setAttribute("op", "and");

for(var h=0; h{
var tPromptCells_New = tTable[h].getElementsByTagName("TD");

for(var i=0; i{
var tElement = tPromptCells_New[i];
if(tElement.getAttribute("GFPBuilder") != null)
{
try
{
var tFilter = eval(tElement.getAttribute("GFPBuilder"));
if(tFilter)
{
tExpr.appendChild(tFilter);
}
}
catch(e)
{
alert('Exception Resulted'+e);
return;
}
}
}
}

var tDelayedDash = document.getElementById("sawDashboardDelayed");

if(tExpr.childNodes.length==0 && !document.getElementById("sawDashboardDelayed"))
return false;
else if(tExpr.childNodes.length==0)
tExpr = null;
else if(tExpr.childNodes.length==1)
tExpr=tExpr.childNodes[0];

if(tExpr)
{
tExpr.setAttribute("xmlns:xsi", "http://www.w3.org/2001/XMLSchema-instance");
}


var tForm = GetViewForm('', -1, bGFPReloadInline);

if(sViewID==ksGFPStatePath)
{
var tRoot = XUIGetRootXML("idXUIGFPPreview");
tForm.P1.value = saw.getXmlText(saw.getFirstChildElement(tRoot));
tForm.action = saw.commandToURL("ViewPreview");
submitViewForm(tForm, null);
return;
}

GFPApplyFilters(tForm, tExpr, sViewID);
return false;
}

b. Restart presentation service
c. Please go to the dashboard page, edit dashboard and add a “Text” dashboard object, after the prompts
d. Edit the Text object and paste the code attached below and check the “Contains HTML Markup” check-box. Please add script open and close tags to the attached code




e. Please save and you can validate the Global Go Button.

Pros:

1) This method is very helpful, where we need only one button to take the values of the rest of the prompt buttons.
2) Ease of using the multiple dashboard prompts
3) No effect of this button on any other dashboard pages or other projects.

Cons:

1) Oracle has not yet supported this in the forthcoming versions.

OBIEE – Dynamic Rolling Month Supporter

Scenario – Your dashboard has a requirement to show a Future Rolling months report and a Trend Analysis Rolling report where Number of rolling months and Rolling from months are dynamic (user prompted) in both the reports. Usually Rolling months are calculated using Sysdate or Current Date. But there can be a different scenario with dynamic N rolling months from Nth month with the number of rolling months in future (For Forecast Reports) as well as in past (For Trend Reports).

Solution – This requirement can be implemented easily by creating dashboard prompts, presentation variables for the prompts and a rolling month supporter report, in addition to your trend analysis report/ forecast report.

The following are the steps of implementing this requirement:

1) Create a dashboard prompt with i) Month and ii). No. of rolling months. I have taken Year column from Time Dimension to create an Edit Box for the Rolling Months, but you can use any of the columns. Since I will be providing an Edit Box for the user to add the number himself, I am taking any column here to use as Edit Box. You can also pre-populate the number in a drop down.

2)Create a presentation variable for the number of rolling months var_rollno.

3)You can give a default value for the Edit Box. I have given as 12.

4)Similarly, pull the month column from the time dimension and create a presentation variable for this as var_month.

5)Alternatively, you can set default selected value for this prompt but that is optional and not needed.

6)Now create a request with 4 columns respectively “Time Dim”. Month, “Time Dim”. Date, another “Time Dim”. Date and again another “Time Dim”. Date.

7)Rename one “Time Dim”. Date Column As “Last N th Month Dt”

8)Rename other “Time Dim”. Date Column As “Next N th Month”

9)Write the following formula for “Time Dim”. Date Column as- MAX("Time Dim".Date)

10)Write the following formula for “Last N th Month Dt” Column to get last nth month date for respective Date. This will give us the month in the past. MIN(TIMESTAMPADD(SQL_TSI_MONTH,-CAST(@{var_rollno} AS INT),”Time Dim”.Date))

11)Similarly, write the following formula for “Next N th Month” Column to get next nth month date for respective Date. This gives us the future month. MAX(TIMESTAMPADD(SQL_TSI_MONTH,CAST(@{var_rollno} AS INT),”Time Dim”.Date))

12)Create a filter on “Time Dim. Month” by assigning this to presentation variable “var_month” presentation variable.

13)Save this report with “Rolling Month Supporter”.

14) Now create the Trend report in which we are showing the Month wise Quantities.

15)In the criteria tab, select the columns “Time Dim”. Month, “Invoice. Quantity“ Columns.

16)Create the filters based on the “Last N th Month Dt” and “Date” Columns from “Rolling Month Supporter” Report.

17)Save the Trend report.

18)Now create a dashboard page and add the Dashboard prompt and the main Trend Report

19)Select the Month from the drop down list and Enter no. of Rolling months in edit box

20)You will get the last n months data for your given input values. Forecast report will also be developed similar to the trend report, but the filter for the same will be:

OBIEE – Column Tips in report columns in Dashboard

Scenario – Your dashboard has a requirement to show Column Tool Tips for the measures, upon mouse hover.
Solution – This requirement can be easily implemented with the help of HTML. The solution will need few changes at the dashboard end and a small change in the instanceconfig.xml file to support the changes.

Following are the steps of implementing the column tool tips:
a. Please add the False in the instanceconfig.xml file. Make sure that this paramter is added outside of the the AdvancedReporting tags in the instanceconfig.xml file. HardenXSS is set to true by default in all new versions of OBIEE. Setting HardenXSS to False will allow developers to use HTML to mark up reports and dashboards which is useful in many circumstances. Oracle BI Presentation Services is secured against cross-site scripting (XSS) by the HardenXSS element. It does so by prohibiting HTML input in fields in Oracle BI Presentation Services that would otherwise accept HTML. Now if we use html markups in the reports or dashboard, we will see html tags in the application, reason is HardenXSS element is set to true. Default setting was done for making the web application more secure, but Oracle does recommend setting this to False incase we need the html to mark up the reports.
b. Restart the services.
c. Please add the HTML tag within column heading.
d. For this, go to column format and check custom heading.
e. Add html tag like Column Name
f. Run the report and you shall see the tool tip on hovering your mouse on the column.
Pros:
1) Columns or Measures described effectively in tool tips.
2) Effective in showing the calculations used for the columns.
3) More user friendliness.
Cons:
1) This works well in all the views other than Charts. The Measure Labels in the charts do not show the column names or the tool tips upon mouse-over, whereas it shows the HTML tags that are in Column Heading. This is because the Chart Engines do not identify the HTML tags. This has been identified as a bug by Oracle and is expected to be corrected in the future versions of OBIEE.

Passing values across pages using report navigation

Scenario - Navigate from Report A to Report B and Report B data should get filtered based on the selection made in Report A.
Solution -
Note -Report A and Report B should have some common tables and fields. Data filtering upon navigation will only happen on the common tables and columns.
Example 1 - We have a Order header report and want to navigate to Order details, where user will click on Order Number and will be navigated to a detail report with order line information. a. Create Report A with attributes Order Number from table1. b. Create Repprt B with attribute Order Number from table1 and line number from table2 and make a filter Order Number "is prompted" c. Modify Report A, add value level navigation in column order number to report B. d. Once you click on order number column in report A, it will open a new window with order and lines details for the order selected in Report A
Example 2 - Same two reports as mentioned in the above example. Navigation will be based on order number but the target report will be filtered for the customer of the order in Report A a. Create Report A with attributes order number and customer number rom table 1 b. Create Report B with attributes order number and customer number form table 1 and line number from table2 and make a filter customer number "is prompted" c. Modify report A, add value level naviation on column Order Number to report B d. Once you click on Order Number in Report A, it will open Report B where data might be filtered by the customer. Wierd! Right?
Answer is in the ordering of the columns in the report A. Note - All the columns in Report A, which are required to pass values to Report B, should be placed to the left of the column on which navaigation link will be placed.