Kishore Analytics Solutions: OBIEE 11g Custom HTML And Java Styles

Dashboard Prompt Expand limit on Drop down values.

Ammend Instanceconfig.xml which is located in the OracleBIData\Web\Config Folder

- ENTRIES BELOW NEED ADDING JUST ABOVE </ServerInstance> AT END OF FILE.

<ReportAggregateEnabled>true</ReportAggregateEnabled>

<Prompts><MaxDropDownValues>5000</MaxDropDownValues></Prompts>

Dashboard Menus Custom Java Style

Insert text box onto Dashboard and "Contains HTML Markup" must be ticked. then the following statements can be used independently.

================== Dashboard Menus Custom JAVA Style ========================

--- Create a Title With a Nice Line Underneath
<table class="TitleTable" cellspacing="0" style="" width="100%">
<tr>
<td class="TitleCell" style="" title="">Dashboard Menu</td>
</tr>
<tr>
<td class="TitleNameCell" style="" title=""></td>
</tr>
</table>

----------------- Multiple Dashboards configured in Instance Config ----------------------

For a drop down menu in a dashboard for Dashboard groups on Top - Find the sawDGroup in (View Source Code IE For Dashboard)
This is Used for Multiple Dashboards where the instance Config has been Changed with entry
<DashboardMaxBeforeMenu>1</DashboardMaxBeforeMenu>

<table>
<tr>
<td>
<a href="javascript:void(null)" onclick="return NQWPopupMenu(event,'sawDGroup2')">Multiple Dashboards in Same Folder</a>
</td>
</tr>
</table>

----------------------- 1 Dashboard Link Only -------------------------------------------
Create Bookmark Link for Dashboard and alter accordingly - Do not put in the whole URL as reports will move
from server to server.

<table>
<tr>
<td>
<a href="saw.dll?url here</a>
</td>
</tr>
</table>

This is a good one
<table class="TitleTable" cellspacing="0" style="" width="100%">
<tr>
<td class="TitleCell" style="" title=""><a href="saw.dll?urlhere";Done=Close" target="_blank"> Upload Functionality
</a>
</td>
</tr>
<tr>
<td class="TitleNameCell" style="" title=""></td>
</tr>
</table>

-------------------- Menu Title with Logo ------------------------------------------

<table class="TitleTable" cellspacing="0" style="" width="100%">
<tr>
<td class="TitleLogo" style="" rowspan="4"><img border="0" src="res/s_oracle10/images/report_trends.jpg"></td>
<td class="TitleCell" style="" title="">This is my report</td>
</tr>
<tr>
<td class="TitleNameCell" style="" title=""></td>
</tr>
</table>

Basic HTML Link on Dashboard

Insert a text box onto Dashboard "Contains HTML"
Paste a url in as below.. And Done..

<A HREF="res/s_oracle10/myfolder/OBIEE_Basic_Training_1.htm" TARGET="_blank">OBIEE Training - Create a Basic Report[br/][br/]</A>

Save and view on dashboard or report

OBIEE Pivot Table insert a word in Top Right Cell

Place static code in Answers Request or Dashboard "Contains HTML"

<script type="text/javascript" language="javascript">
window.setTimeout(InsertText,100);
function InsertText() {
   if (document.body!=null)
{
    var cols = document.getElementsByTagName('td');
      for (var x=0; x<cols.length; x++) {
if (cols[x].className == 'PTIndentCell' && cols[x].innerHTML.indexOf('')!=-1)
    cols[x].innerHTML = 'This is the Top right woohoo';
}

   } else {
      window.setTimeout(InsertText,100);
   }
}
</script>
<style type="text/css">
.PTIndentCell
{
             color:#335C85;
             background-color:#ffff99;
             font-family:Arial;
             font-size:8pt;

             font-weight:bold;

             text-align:center;

             border-top:   black 1px solid;

             border-left: black 1px solid;
             border-right: black 1px solid;
          }

</style>

Dashboard Tab Colours

The script below allows the Tabs on a dashboard to be coloured in differnent colours , Quite cool...

Just insert a Textbox into a dashboard "Contains HTML"
Paste the below code in

<script type="text/javascript">
var tblId=document.getElementById('TabsTable');
tblId.tBodies[0].rows[0].cells[1].className='';
tblId.tBodies[0].rows[0].cells[2].className='';
tblId.tBodies[0].rows[0].cells[3].className='';
tblId.tBodies[0].rows[0].cells[4].className='';
tblId.tBodies[0].rows[0].cells[5].className='';
tblId.tBodies[0].rows[0].cells[6].className='';
tblId.tBodies[0].rows[0].cells[1].style.backgroundColor='red';
tblId.tBodies[0].rows[0].cells[2].style.backgroundColor='red'
tblId.tBodies[0].rows[0].cells[3].style.backgroundColor='white'
tblId.tBodies[0].rows[0].cells[4].style.backgroundColor='white'
tblId.tBodies[0].rows[0].cells[5].style.backgroundColor='blue'
tblId.tBodies[0].rows[0].cells[6].style.backgroundColor='blue'
</script>

Dashboard When opening report in New window and Drill back button

When you create a dashboard and you have a drill down report the drill down report opens in the same window. So when you hit the return link the drill down report also closes. Thats fine a back button can be placed in the answers request also when there are No Results a custom no results page can be added to look exactly the same but with a back button too.

Insert a static text box "contains HTML Markup" into answers request.

<table align="left">
<tr >
<td><a href="javascript:history.back();">Back</a></td>
<tr>
</table>

For No Results back button in answers just paste the code below directly into the Text box. Also removes the refresh button if needed else just strip out the function for remove refresh.

<head>
<script type="text/javascript">
window.setTimeout(removeRefresh,100);
function removeRefresh()
{
if (document.body!=null)
{
  var td =document.getElementsByTagName('td');
  for(var i=0;i<td.length;i++)
    {
       if(td[i].className=='ResultLinksCell')
       td[i].style.display='none';
    }

}
else
   window.setTimeout(removeRefresh,100);
}

</script>
</head>
<table align="left">
<tr>
<td class="ErrorTitle" valign=top><img src="res/bmozilla4/common/msginfo.gif"> </td>
<td class="ErrorTitle">No Results</td>
</tr>
<tr>
<td> </td>
<td class="ErrorInfo">The specified criteria didn't result in any data. This is often caused by applying filters that are too restrictive or that contain incorrect values. Please try running report with different parameters.<span style="font-size: 4pt;"><br><br></span><font point-size=4><br></font>
<table class="FilterFirstTable" border=0 cellspacing=0>
<tr>
</tr>
</table>
</td>
<tr>
<td><a href="javascript:history.back();">Back</a></td>
</table>

Dashboard Compare 2 Prompt dates to see if they are the same and show ERROR

The following script enables to compare 2 date prompts on a dashbaord to tell the user to select a prior Date less than the current Date warning message..

Insert a text box into a dashboard "Contains HTML"
Then paste the following code into the text box

<head>
<script type="text/javascript">
var curr = '@{currentdate}{2013-01-31}';
var prev= '@{previousdate}{2012-12-31}';


dt1=parseInt(curr.substring(8,10),10);
mon1=parseInt(curr.substring(5,7),10);
yr1=parseInt(curr.substring(0,4),10);

dt2=parseInt(prev.substring(8,10),10);
mon2=parseInt(prev.substring(5,7),10);
yr2=parseInt(prev.substring(0,4),10);

  date1=new Date(yr1, mon1, dt1);
date2=new Date(yr2, mon2, dt2);

if (date1<=date2)
    alert('Please enter the Prior Date value as less than Current Date');

//

</script>
</head>

Refresh a Dashboard in intervals

Hi you can refresh a dashboard in intervals using the following code

Insert a text box onto the Dashboard - "Contains HTML"

Paste the following code into the textbox and save

<script>

</script>

OBIEE Table make a row BOLD with JAVA

The below code allows a row in a table to be in Bold - insert a static text box into an answers request and "Contains HTML"

Paste below code and change "yourword" to the rows you want to highlight with identifier yourword.... Also ammend to the number of columns you have just keep adding cols on..... this example is for 6 columns..

<head>
<script type="text/javascript" language="javascript">
window.setTimeout(InsertText,100);
function InsertText() {
   if (document.body!=null)
{
    var cols = document.getElementsByTagName('td');
      for (var x=0; x<cols.length; x++) {
if (cols[x].innerHTML=='yourword)
   {
         cols[x].className='PTROW1T PTRIL OOLD'
         cols[x+1].className='PTROW1T PTRIL OOLD'
         cols[x+2].className='PTROW1T PTRIL OOLD'
         cols[x+3].className='PTROW1T PTRIL OOLD'
         cols[x+4].className='PTROW1T PTRIL OOLD'
         cols[x+5].className='PTROW1T PTRIL OOLD'
         cols[x+6].className='PTROW1T PTRIL OOLD'
}
    }

   } else {
      window.setTimeout(InsertText,100);
   }
}
</script>
</head>

Note : I have OBIEE internal css class - PTROW1T PTRIL OOLD to display font as bold.

Dashboard Specific remove Print ICON

The script below enables you to remove the Dashboard print icon for a specific Dashboard as sometimes it gets confusing for users between the answers print icon and dashboard icon

Insert a Text box anywhere on the dashboard "Contains HTML"
Paste the following code into the text box

<script type="text/javascript">
window.setTimeout(removePrintIcon,100);
function removePrintIcon()
{
if (document.body!=null)
{
  var rows =document.getElementsByTagName('a');
  for(var i=0;i<rows.length;i++)
    {
       if(rows[i].title=="Printer Friendly")
       rows[i].style.display='none';
    }

}
else
   window.setTimeout(removePrintIcon,100);
}


</script>

Thats it view your dashboard and the dashboard print icon for that particular dashboard will disappear.

Default Prompts Button Dashboard

The script below allows a button to be displayed on the dashboard which resets all the dashboard prompts back to the original Default values .

Insert a text box above the dashboard prompts section. Click " Contains HTML"
Paste text below into the text box you have inserted.

<div class="XUIPromptEntry minibuttonOn"><a href="#" onclick="return PersonalizationEditor.removeDefaultSelection(false)">Default Parameters</a></div>

Save and view on dashboard ( Its best to left align the section containing the dashboard prompt and the above button so its looks good)

Session Variable set for Report only that is running

THESE ARE SET AT QUERY LEVEL FOR A REPORT OR PROMPT RESULTS ONLY

If you want to set session variables for a report that is running in OBIEE then create an answers report and goto the Advanced tab of the Answers report or Dashboard Prompt

Under "prefix" paste the code below with your session variable name and either a variable from a prompt or a value

SET VARIABLE sessionv1= '@{prompt1}',sessionv2= '@{prompt2}{2011-01-01}',sessionv3='@{prompt3}';

sessionv1 is the session variable name in RPD
prompt1 etc are the variable in names in my dashboard prompts
2011-01-01 is just a static value im passing this could have also been a prompt presentation variable

Remeber you will not see the session variable change in the sessions window in RPD but it will take effect.

Purge all Cache From Dashboard

Using the Java code below OBIEE BI Server cache can be cleared from Dashboard

1.Insert a Text box into a dashboard "Contains HTML" must be ticked
2. Paste below code into Text Box

<script language="javascript">
document.write(
"<iframe width=Opx height=Opx src=" +
document.location.href.match(/^[^?]+/) +
"?IssueRawSQL" +
document.location.href.match(/&_scid=[^&]+/) +
"&SQL=CALL+SAPurgeAllCache();)></iframe>"
);
</script>

OBIEE Update session variables from Dashboard

To update session variables so that they can be used in rpd querys you must call the NQSSetsessionvalue commands this is normally done from the Issue SQL in the administration part of OBIEE. Using the code below this can be done using Prompt Variables and on a dashboard hidden away... From the below example i am updating 3 session variables in the rpd

1. Insert a Text box into Dashboard - Set text box to "Contains HTML"
2. Paste script below with your ammedments and save.

<script language="javascript">
document.write(
"<iframe width=0px height=0px src=" +
document.location.href.match(/^[^?]+/) +
"?IssueRawSQL" +
document.location.href.match(/&_scid=[^&]+/) +
"&SQL=CALL+NQSSetSessionValue('String+sessionv1=@{mydate1};String+sessionv2=@{mydate2};String+sessionv3=@{myvalue1};')></iframe>"
);
</script>

sessionv1 etc is the name of your session variable in RPD.

mydate1 etc is my dashboard prompt variable.

This is for Dashboard to RPD

OBIEE Cursor Cache clear from Dashboard Java Script

OBIEE Cursor cache is normally cleared from the Administration - Manage Sessions Screen...

Here is a way that a piece of java can be embedded into a dashboard and call an xframe so you cant see it being called and clear the OBIEE Cursor cache - Presentation cache in effect.

1. Just insert a text box into Dashboard - Tick the "Contains HTML Markup"
2. Paste the script below into the text box

<script language="javascript">
document.write(
"<iframe width=0px height=0px src=" +
document.location.href.match(/^[^?]+/) +
"?ManageSessions" +
document.location.href.match(/&_scid=[^&]+/) +
"&Action=CloseAllCursors&Done=saw.dll%3fSessions',{ensureFreshUrl:true});return false;')></iframe>"
);
</script>

Voila - when ever you click on the dashboard or refresh it the cursor cache will get cleared.

OBIEE Dates in Descending order from RPD Column

When you have a date column and you want to sort it as Descending when picked in Answers but automatically from rpd.

1. Insert a new column in the BM Layer in Rpd using calculation below

-(1) * CAST ( EVALUATE('TO_CHAR(%1,%2)' AS CHARACTER ( 30 ), yourcolumn, 'YYYYMMDD') AS INTEGER )

yourcolumn is the column that you want to sort.

2. Once created goto the original column yourcolumn and set the sort order to the column you just created above.

Thats Its ...

Direct DB Request and Integer Problem on Column Selected

Sometimes when you do a direct database Request especially from Oracle you have problems when a number to obiee is an integer

Do the below to resolve .. In your sql statement CAST(yourcolumn AS double precision)

To change integer into double precision ---

select CAST(yourcolumn AS double precision)
from Yourtable

OBIEE Pivot Table nulls to Zeros for empty Rows

When you create a pivot table and you see nulls in the measures part , You want to see 0 or 0.00 instead

Just change the data format on the report column in the report itself too

Custom format

#,##0.00;-#,##0.00;0.00

For Nulls if you want zeros 0 instead of null then enter:
#,##0;-#,##0;0

For a Dash
#,##0;-#,##0;-

A custom Message instead of null
#,##0;-#,##0;no data

Prompt for All Choices is Null when Prompt variable is called in Text

To get a value of ALL when using a prompt with all choices selected try the below using Java Code. Neat solution... Insert a Text Box and click As html.... Just pass in a Presentation Variable from dashboard prompt

-----------------Call a Text box as HTML --------------------------
Call a presentation Variable directly into a Text Insert into a Dashboard.
Display as HTML

Wednesday, December 26, 2012

OBIEE 11g Custom HTML And Java Styles