Representation of Presentation variable

Got a request from OTN forum- https://forums.oracle.com/thread/2562081 asking for the formatting of the presentation variable representation-
User wants to show in the dashboard the exact representation of the presentation variable as below
@{My_PV}{Horse}

Used HTML ascii codes for the same-

1. In the criteria column formula use-

case when 1=1 then '@{Geo_Var1}{AMERICA}' else

dummy_column_name

end

2. In the narrative view, prefix use -@{Geo_Var1}

3. In the narrative section - {@1}

4. Rows to display - 1

There should be another way for this...

Comments are always welcome...

Changing the background color on hovering in OBIEE

When there is a requirement to have background color to appear on hovering over the text then the below code will help you out.
Use the below code in the Text inside the section with the HTML check box enabled.

<script type="text/javascript">
function changeColor(id, color) {
element = document.getElementById(id);
event.cancelBubble = true;
oldColor = element.currentStyle.background;
element.style.background = color;
}
</script>
<div id="left1" onMouseOver="changeColor(this.id, '#81DAF5');" onMouseOut="changeColor(this.id, '#FFFFFF');"><font size=2>Sample Text</font></div>

The use of div here is to restrict the color change on the particular section only. If we use Body bgcolor, it will  change the color for the whole page.
And in conditions where there are multiple sections, just add the above code and change the div id values.
Hope this will help many people out there. Thanks to javascript :)

Comments are always welcome...

 

Conditional Hyperlink/ Removing Conditional Hyperlink

In OTN forum, I came across a good requirement and I tried the same.
Cases where you need to keep/ remove the conditional underline  of a text while showing navigation can be handled by our user friendly HTML script. This can be played under the impression where customized text is required for the navigation.
Below piece of code shows the text to represent without an underline beneath the data values. And also do note that the pointer shape is replaced with default to remove the 'Hand' symbol.  You also need to change the Data format of the column to HTML

Save and see the result...

Comments are always welcome...

Left Indent on OBIEE 11g

I have seen couple of questions raising for changing the indent style of the data values in the OTN forum and also me myself had a requirement in OBIEE 11g to change the style of the data values to indent 20 from left. We found to add the indent properties to 20 and see the change.
But you can also still trust on css properties. And of course, both the trick plays their role perfectly.

Take a reference of the below screenshot-

Save and check the results...

Also try using 'text-indent:30px' and would produce the same results

Comments are always welcome...

IsNumeric or NOT

I needed to show the column values containing numbers as 'Numeric' and the values without the numbers as 'Alphabets'. I would first validate the column values with the case statement, if it is containing any numeric value. If it doesnt contain then it is named as 'Alphabets'
And on top of it I need to show the custom messages back ground with different colors.
I have tried the below method with HTML and with conditional formatting.

First method approach is with HTML and case statement.

case when ("Products"."Product" like '%1%')
OR ("Products"."Product" like '%2%')
OR ("Products"."Product" like '%3%')
OR ("Products"."Product" like '%4%')
OR ("Products"."Product" like '%5%')
OR ("Products"."Product" like '%6%')
OR ("Products"."Product" like '%7%')
OR ("Products"."Product" like '%8%')
OR ("Products"."Product" like '%9%')
OR ("Products"."Product" like '%0%')
then '<span style="background-color:Red">Numeric</span>'
else '<span style="background-color:Green">Alphabets</span>'
end

Second method with conditional formatting and case statement.

Here you need to add the below code in the column formula

case when ("Products"."Product" like '%1%')
OR ("Products"."Product" like '%2%')
OR ("Products"."Product" like '%3%')
OR ("Products"."Product" like '%4%')
OR ("Products"."Product" like '%5%')
OR ("Products"."Product" like '%6%')
OR ("Products"."Product" like '%7%')
OR ("Products"."Product" like '%8%')
OR ("Products"."Product" like '%9%')
OR ("Products"."Product" like '%0%')
then 'Numeric'
else 'Alphabets'
end

and in the column properties-->conditional formatting-->add condition

Save and check the results...

Comments are always welcome...



Change Cursor Types

We can change the shape of the cursor while pointing to the text in the column. This can be achieved by adding a small css cursor property in the column properties in the style fo the column.

For Example-
cursor:move;

And check the results...

Cursor Types to Try-

-auto
-crosshair
-default
-e-resize
-help
-move
-n-resize
-ne-resize
-nw-resize
-pointer
-progress
-s-resize
-se-resize
-sw-resize
-text
-w-resize
-wait
-url(smiley.gif),url(myBall.cur),auto

Comments are always welcome...



Highest value in the narrative view/ Rank

We needed to show the Highest value for a measure column in the narrative view. The report for example will consist of two columns- dimension and measure column.
I placed a dummy column for finding the highest value of the measure by using RANK function in the column formula and calculating its position using javascript.


First will drag a dummy column of the measure and use the rank function as shown below-

Next will be using javascript to calculate the appropriate position of the 1st highest value of the measure column.

<script language="javascript" type="text/javascript">
for(var i=0;i<1;i++)
{
var a=@3;
if(a==1)
{
document.getElementById("Format").innerHTML='Biggest Profit-->@2';
}
}
</script>

And save and view the result...

Comments are always welcome...

Thousand separator in Narrative View

To display the thousand separator comma for the measure values in the Narrative view Bill suggested to use the HTML format.
https://forums.oracle.com/forums/thread.jspa?threadID=2393458&tstart=15

Use the below format in the narrative view with HTML enabled.

<td id="Format" style ="vertical-align: top; text-align: right; width: 150px;"> @2 <br>
</td>
<script language="javascript" type="text/javascript">
document.getElementById("Format").innerHTML=number_format(@2, 2, '.',',');;
</script>

@2 is the position of the fact measure column.

And also as per Nico-
This is a bug:
NLS:NARRATIVE VIEW DOES NOT USE CRITERIA DATA FORMAT
https://support.oracle.com/CSP/main/article?cmd=show&type=BUG&id=13791979
And it's fixed in the version 11.1.1.7.0 ..

Comments are always welcome...

Pop Up/ New Window

When the user needs to have a new google search window on click on the value and then search the value by default.
We can have the customized html code in the column formula and change the Data format of the column to HTML.
'<a target="_blank" href="http://www.google.com/search?hl=en&q=' ||col_name||'&btnG=Google+Search">'||col_name|| '</a>'






After clicking on the value-

Also follow the below link for opening to a new window within the report-
http://shahin-obiee.blogspot.in/2012/05/normal-0-false-false-false-en-us-x-none.html

Comments are always welcome...

Hierarchy in Answers

Hierarchies can be created in the RPD and viewed in the answer report. Hierarchies plays an important role in OBIEE to see the drill down hierarchy order. In RPD hierarchy is created according to the requirement and viewed accordingly in the answers.
Here in my case, I will show you the case of creating a hierarchy based report from the answers directly. But remember this is not a hierarchy columns created from the RPD Hierarchy.
And you need to remember that the values into the column should be in the order of the hierarchy that is to be displayed on the page.
NOTE- Only dimension columns seems to work fine without fact columns.
So, this structure can be used to display a certain hierarchy level without the means of fact columns.

In the report, drag the dimension column which contains the values to be displayed in hierarchial order.

Add a second dummy column in the report with the HTML Data format enabled. Add the mentioned column formula to stimulate the required changes using HTML. I am adding a default RED color to display behind the word.

And view the report and save it.

Comments are Welcome..

Line Break in the column

We need to display the text inside the column formula with a line break to show the data broken with horizontal pieces.
So as always we can call HTML into rescue for such situation. And also to mention OTN serves good to come up with different ideas meeting the requirements.

We can replace the words blank spaces with a <br> tag like the one shown below-

Replace('Good Day To People',' ','<br>')

Result for the above code in the column formula-

The above piece of code holds good in the situtation where there is a line break after every space.

Supposing if we need to have a line break after few sets of words then the code should be like the below-

Replace('Good Day* To People Out* There','* ','<br>')

And the last piece of code without the Replace statement-

'Good'||'<br>'||'Day'

I used the below code to display the values as

'Good'||'<br>'||'Day'||'---->'||"Products"."Product"

NOTE- Please dont forget to change the Data format of the column in the column properties to 'HTML'

Pivot Table Indent formatting

In the OTN forum https://forums.oracle.com/forums/thread.jspa?threadID=2401883&tstart=15
many times people ask for formatting the indent type of pivot tables.
Yes, in OBIEE 11g it has become a bit odd to look to the pivot table formats. User's also start complaining on the indent type color and want to get rid of the default feature provided.
We can modify the xml contents for this bug, but in case when the reports are confined to specific dashboards then we need to format the reports individually or the dashboard reports individually.

In my below post I am trying to restrict the indent color type of pivot table within the report inside the compound layout.

Below is the code using javascript to do-

<script type="text/javascript">
var tds = document.getElementsByTagName('td');
var lCSS = new Array();
for(var td=0;td<tds.length;td++){
if( tds[td].className != 'PTCC'){
continue;
}
tds[td].style.backgroundColor="#FFFFFF";
lCSS.push(tds[td].innerHTML);
}
for(var len =0; len < lCSS.length; len++){
}
</script>

#FFFFFF is the color code and change using your own code

What I am preferring is a simple one line code to do the magic!!!.
You need to have a simple static text and of course HTML check box enabled and have the below piece of simple code.

<style>.PTCC {background-color:white;border-top:white;border-bottom:white;border-left:white;border-right:white}</style>

And use the static text in the compound layout of the report. The above code will change the properties of the pivot table indent to white making it appear as if it has disappeared from the pivot table.
View the below piece of code in the dashboard.


Result-

NOTE * -Data is made as white to hide the information in the screenshot above

Moving the Report Links

In the OTN forum below-
https://forums.oracle.com/forums/thread.jspa?threadID=2268439&tstart=0
got the requirement that we need to move the report links at the top of the page. So which means if the report is too large then the user doesn't need to scroll to the bottom of the report to download or edit the report.
This gives a feasibility to the users to download the report at the first sight of the page and yes you are going to get a good impression too.
So as my previous post says for editing the Report links-

http://obiee10grevisited.blogspot.in/2012/06/background-color-for-report-links.html

Before the changes-

All we need to do is to use the static text in the dashboard and of course HTML enabled and use the below code in the static text.

<style>.ResultLinksCell { background-color: Red; border-top: white; border-left: Black;
	position:relative;
	top:-150px;
	left:-350px;
	}</style>

Result looks good and satisfying. 

Background color for the Report Links

If the user is interested to have a background color to the report links, so that they are highlighted under each of the reports.

Use the below code in a static text with HTML enabled. Of course, you can change the colors. :)

<style>.ResultLinksCell { background-color: Red; border-top: white; border-left: Black;}</style>

Result of the above work-

Disabling Right Click on OBIEE with javascript

We had to disable the right click button of mouse.
Thansk to Javascript ;)

<script language=JavaScript>
<!--
var message="Function Disabled!";

function clickIEbrowser4(){
if (event.button==2){
alert(message);
return false;
}
}
function clickNSbrowser4(e){
if (document.layers||document.getElementById&&!document.all){
if (e.which==2||e.which==3){
alert(message);
return false;
}
}
}
if (document.layers){
document.captureEvents(Event.MOUSEDOWN);
document.onmousedown=clickNSbrowser4;
}
else if (document.all&&!document.getElementById){
document.onmousedown=clickIEbrowser4;
}
document.oncontextmenu=new Function("alert(message);return false")
</script>

Dashboard in Drop Down

Below is the method followed to show multiple dashboards with multiple pages in a drop down.
You need to have a default page with static text -HTML enabled and with the following piece of code.

<center>
<html>
<body>
<script type="text/javascript">
</script>
<FORM>
<SELECT NAME="url" onchange="document.getElementById('myframe'). src=this.value"><OPTION VALUE="None" style="font-family:arial;color:red;font-size:90px;">Select a dashboard 
<OPTION VALUE="http://usblrnmehandal1:7001/analytics/saw.dll?Dashboard&PortalPath=%2Fshared%2FTesting%2F_portal%2FTestDashboard&page=Test1">Testdashboard
</SELECT>
</FORM>
<iframe id="myframe" height="1500" width="1500" src="about:blank"></iframe>
</body>
</html>
</center>

If multiple dashboard has to be added then you need to have multiple option values.

Removing Totals of a column

In one of the OTN forum, user asked to remove one of the Totals of a column.
My first thought was javascript.
So below is the report which shows the two dimensions and two measure columns.
Suppose, the user wants to remove the total of the second measure column.

Then first you need to place the report in the dashboard and get the HTML by 'View Source'.
Then you need to find the appropriate td tag and remove it.
This will remove the total of the second column.
Script:

<head>
<script type="text/javascript">
var element1 = document.getElementsByTagName('td');
var count=0;
var classname2 ="TTHT PTHT PTLR PTLC OORT";
for (var j = 0; j < element1.length; j++)
{
var classes1 = element1[j].className;
if (classes1==classname2)
 {
 count=count+1;
 if(count==1)
 {
 element1[j].parentNode.removeChild(element1[j]);
 }
 }
}
</script>
</head>

This should help many folks trying to remove bits and pieces.

Refresh a Single Report

Hi,

In my previous post to refresh the whole page-http://obiee10grevisited.blogspot.in/2012/03/obiee-autorefresh-no-prompts.html.  is useful to refresh the complete page.
In the below post, you can try to refresh the report alone:


<script>
function refreshTheFrame()
{
parent.frames[0].location.href='http://muram:9704/analytics/saw.dll?Go&Path=/users/administrator/2';
}
setInterval('refreshTheFrame()',"6000");
</script>
<div>
<iframe frameborder="0" border=0 marginwidth=0 marginheight=0 hspace=0 vspace=0 width=1200 height=600 scrolling=auto>
</iframe>
</div>

The above code would place the report in a text object and then load the report after the refresh time interval.

OBIEE10g AutoRunPrompt

Thanks to-http://obiee101.blogspot.in/2011/12/obiee10g-autorunprompt.html

The script can be downloaded here: download COBIEEJS.

Copy the file to your b_mozilla directory’s (or other webserver dirs you use)

How to use it?

1. Add an edit box style prompt to your dashboard:

2. Add a textbox with:

<script src="res/b_mozilla/cobieejs.js" language="javascript"> </script>
<script language="javascript">
  AutoRunPrompt('C1  Cust Name');
</script>

don’t forget the Contains HTML Markup checkbox

3. Add your prompted report:

Run the dashboard:

OBIEE Hide the Refresh link within the No Results view

Thanks to-http://total-bi.com/2010/09/obiee-hide-no-results-refresh-link/

Occasionally I’ve come across a requirement to remove the refresh link that is displayed on the No Results view. It doesn’t make sense to have a refresh link in this context and it can confuse your users. Here’s one solution:
Add a ‘No Results’ view to your report

In the text box paste this:

<script type="text/javascript">
<!--
function removeRefresh(){
cols = document.getElementsByTagName('td');
for (x=0; x<cols.length; x++) {
  if (cols[x].className == 'ResultLinksCell' && cols[x].innerHTML.indexOf('Refresh')!=-1)
    cols[x].innerHTML = '';
}}
window.onload=removeRefresh;
//-->
</script>
<p>Please change the dashboard filters and try again<p>
I hope this snippet helps someone out there.