OBIEE: February 2012

Add Google Search on the dashboard page

Put the below HTML code in the Text object of the dashboard editor.
Check the HTML option.

<HTML>

<center>
<FORM method=GET action="http://www.google.com/search" TARGET="_blank">
<TABLE bgcolor="#FFFFFF"><tr><td>
<A HREF=" http://www.google.com/">
<IMG SRC="http://www.google.com/logos/Logo_40wht.gif" border="0" ALT="Google" align="absmiddle"></A>
<INPUT TYPE=text name=q size=31 maxlength=255 value="">
<INPUT TYPE=hidden name=hl value="en">
<INPUT type=submit name=btnG VALUE="Google Search">
</td></tr></TABLE>
</FORM>
</center>

</HTML>

Thanks.

Watching a drop down report in an IFrame - OBIEE 11g

Thanks to bittu4u4ver ( IT TOOLBOX - Javascript group ) for helping me correcting the javascript which aims at viewing a report in the IFrame that has been selected in the drop down list. Note that this exercise is carried on OBIEE 11g.

First of all open any of the existing 4 reports in the front end application and copy their URLs and keep them aside. We will be using them in the Option value of the Select tag.

Now let us look at the Javascript code which you have to put in Text object of the Dashboard editor.

<html>
<body>
<script type="text/javascript">
</script>

<table bgcolor="#FFDEAD">
<tr>
<td>
<font color="red">
<h3>Select the report from the drop down list and it will open in the Iframe.<h3></font>
</td>
</tr>
</table>
</br>

<FORM>

<SELECT NAME="url" onchange="document.getElementById('myframe'). src=this.value">
<OPTION VALUE="http://usblrnmehandal1:7001/analytics/saw.dll?PortalGo&Action=prompt&path=%2Fshared%2FRRR%2FEmptyPage"
style="font-family:arial;color:black;font-size:90px;">Select a report >>

<OPTION VALUE="http://usblrnmehandal1:7001/analytics/saw.dll?PortalGo&SearchID=u9eelpmhafltb1meukpuv65thm&Path=%2fshared%2fRRR%2fColumnSelector&ViewState=5s57ml624m72vg5if1ifvdm3pu&ContainerID=o%3aportalgo%7er%3areport&RootViewID=go"
style="font-family:arial;color:orange;font-size:90px;">Column selector report

<OPTION VALUE="http://usblrnmehandal1:7001/analytics/saw.dll?PortalGo&Action=prompt&path=%2Fshared%2FRRR%2FDistrictSelector"
style="font-family:arial;color:Yellowgreen;font-size:90px;">District selector report

<OPTION VALUE="http://usblrnmehandal1:7001/analytics/saw.dll?PortalGo&Action=prompt&path=%2Fshared%2FRRR%2FInline%20Prompt"
style="font-family:arial;color:red;font-size:90px;">Inline prompt report

<OPTION VALUE="http://usblrnmehandal1:7001/analytics/saw.dll?PortalGo&Action=prompt&path=%2Fshared%2FRRR%2F25th%20Percentile"
style="font-family:arial;color:blue;font-size:90px;"> 25 percentile report

</SELECT>

</FORM>
</br>
<iframe id="myframe" height="600" width="1000" src="http://usblrnmehandal1:7001/analytics/saw.dll?PortalGo&Action=prompt&path=%2Fshared%2FRRR%2FEmptyPage"></iframe>
</body>
</html>

Note : I have created one dummy report ( Empty Page) that shows only 1 string "SELECT A REPORT FROM THE DROP DOWN LIST" and this is defaulted. It shows a meaningful message to the user; asking him to select a report.
Adjust the height and width of IFrame as per your requirement.

Let us look at the snapshots.

1: In the beginning. ( Default page says Select a report from Drop down )

2: Select any report from the drop down list for ex - District selector report

District selector report is opened.

This way we can select any report and it will open in the IFrame.

Have a nice day !!!

Count the number of users logged in to the OBIEE Application

Thanks to Kurt Wolff on IT Toolbox - Oracle-BI group who gave an excellent solution.

I thought let us implement the logic with some modifications. Here it is .

Create a table in the database.

CREATE TABLE USERS(USERNAME VARCHAR2(20),CDATE TIMESTAMP WITH LOCAL TIME ZONE,COUNTER NUMBER);

COMMIT;

Create a row level insert trigger Q1

CREATE OR REPLACE TRIGGER Q1

BEFORE INSERT ON USERS

FOR EACH ROW

DECLARE numrows INTEGER;

BEGIN

SELECT G1.NEXTVAL INTO numrows FROM dual;

:NEW.COUNTER := numrows;

END;

COMMIT;

Create a sequence G1

CREATE SEQUENCE G1

START WITH 1

MAXVALUE 1E27

MINVALUE 1

NOCYCLE

CACHE 20

NOORDER;

COMMIT;

Make sure the trigger and sequence is valid and enabled.

Import this USERS table in the RPD.

Create a session initialization block NOOFUSERS with the session variable USERLOGGING.

Save the changes.

I have now logged in to OBIEE front end application few times with user weblogic and with some other user names. So now let us go to database and see the USERS data.

Observations :

There are total 5 users logged in to the application as of now.
The last logged in user was AZIFF.

You can query this table in a different ways in order to get various combinations

Thanks.

Drop down reports

Aim is to select a report from the drop down list and open a report in a new window

This exercise is carried out on OBIEE 11g version.

First of all I am going to open few existing reports and copying the URLs in the address bar of the IE. We have to use those URLs in the java script code shown below.

In the code - I have copy pasted the URLs of the 4 existing reports on my machine - look at the Option value tag and have done some kind of formatting.

You can replace the URLs by URLs of your own reports and check if that works or not.
Here I want the report to be opened in a new window and hence the window.open(newPage) in the java scription function GOTO()

Put this code in the Text object of Dashboard editor and check the HTML option.

<html>
<SCRIPT LANGUAGE="JavaScript">

</SCRIPT>
<table width="1200" border="7" >
<tr>
<td style="background-color:#FFB089;">
<h3>SELECT A REPORT FROM THE DROPDOWN LIST AND PRESS GO</h3>
</td>
</tr>
</table> <br /><br />
<FORM>

<SELECT NAME="url" WIDTH=20>
<OPTION VALUE="None">Select a report >>>

<OPTION VALUE="http://usblrnmehandal1:7001/analytics/saw.dll?PortalGo&Action=prompt&path=%2Fshared%2FRRR%2FColumnSelector">
Column selector report

<OPTION VALUE="http://usblrnmehandal1:7001/analytics/saw.dll?PortalGo&Action=prompt&path=%2Fshared%2FRRR%2FDistrictSelector">
District selector report

<OPTION VALUE="http://usblrnmehandal1:7001/analytics/saw.dll?PortalGo&Action=prompt&path=%2Fshared%2FRRR%2FInline%20Prompt">
Inline prompt report

<OPTION VALUE="http://usblrnmehandal1:7001/analytics/saw.dll?PortalGo&Action=prompt&path=%2Fshared%2FRRR%2F25th%20Percentile">
25 percentile report

</SELECT>
<INPUT TYPE=BUTTON VALUE="Go!" onClick="GOTO(this.form)">
</FORM>
</html>

We have selected here Inline Prompt report and pressed Go.

Thanks.

Groupings - Report column values and Prompt values

This exercise is carried out on OBIEE 10g Version - Paint Subject area.

Look at the report below :

I would like to group the first 3 Brands namely 'Enterprise', 'Magicolor', 'McCloskey' as [A] and the remaining Brand 'Valpsar' as [B]

I am going to edit the column formula of Brand.

I have added a presentation variable as a filter on the column Brand - Name it as A

Create a dashboard prompt on column Brand.

Sql used to populate the prompt is ->

SELECT case WHEN Products.Brand IN ('Enterprise', 'Magicolor', 'McCloskey') then 'A' else 'B' end FROM Paint

We have also set the presentation variable to A. Give a label to the prompt and save.

Now pull the prompt and report on the dashboard and let us see the result.

Thanks.

Use of option button to select a report

This exercise is based on OBIEE 11g.

Use Text object and put the HTML code. For example.

[ Note : Input type is RADIO and value is obtained by copying the URL from the Individual report , There are 4 report options are present, Also I want each report to open with a new page so I am using window.open(ob[i].value,'_blank'). I have customized the style of button and text and form. You can modify the code according to your requirements. ]

<html>

<table width="1200" border="7" >
<tr>
<td style="background-color:#FFA500;">
<h3>Choose a report</h3>
</td>
</tr>
</table> <br /><br />

<form action="../">
<fieldset>

<input type="RADIO" value="http://usblrnmehandal1:7001/analytics/saw.dll?PortalGo&SearchID=6ci2di5j21pk00tn7hvffpuh5a&Path=
%2fshared%2fRRR%2fShowing%20the%20lst%20run%20time%20of%20the%20report&ViewState=h63oupbhm227u6lch494h9v6fa&ContainerID=o%3aportalgo
%7er%3areport&RootViewID=go" name="userChoice" id="navRadio01">
<label for="navRadio01"><b>Showing the Last run time Report</b></label><br>

<input type="RADIO" value="http://usblrnmehandal1:7001/analytics/saw.dll?PortalGo&Action=prompt&path=%2Fshared%2FRRR%2FInline
%20Prompt" name="userChoice" id="navRadio02">
<label for="navRadio02"><b>Inline Prompt report<b></label><br>

<input type="RADIO" value="http://usblrnmehandal1:7001/analytics/saw.dll?PortalGo&Action=prompt&path=%2Fshared%2FTesting%2FDouble%20column%20feature"
name="userChoice" id="navRadio03">
<label for="navRadio03"><b>Double column feature report<b></label><br>

<input type="RADIO" value="http://usblrnmehandal1:7001/analytics/saw.dll?PortalGo&Action=prompt&path=%2Fshared%2FRRR%2FColumn%20with%20Action%20-
%20Inline%20Prompt" name="userChoice" id="navRadio04">
<label for="navRadio04"><b>Column with Action - Inline Prompt report<b></label>
<br /><br />

<input type="BUTTON" value="Go there!" onclick="ob=this.form.userChoice;for(i=0;i<ob.length;i++){
if(ob[i].checked){window.open(ob[i].value,'_blank');};}" style="color:#DC143C;background-color:#FFFF47;font-family:verdana;border-style:solid;" />

</fieldset>

</form>
</html>

1) Open the first report for example

Click on the first option button and hit the Go button.

Thanks.