OBIEE Presentation Server Always Refresh

If you have a separate development presentation server, you can use the developersprompt: (http://obiee101.blogspot.com/2008/10/obiee-developersprompt.html) for bypassing the BI servercache. If you always want to bypass the Presentation server cache add this to the instanceconfig file of the development presentation server:

<WebConfig>
<ServerInstance>
<ForceRefresh>TRUE</ForceRefresh>
</ServerInstance>
</WebConfig>

Till Next Time

OBIEE Popup box

Customer wanted to see some extra info in a popup box:

This can be done simple from a narrative view:

First start with a basic report:

goto the narrative view:

Switch on the HTML

in the prefix put:

<tr><th>[b]Customer[/b]</th><th>[b]Revenue[/b]</th></tr>

In the narrative put:

<tr>
<td>@1</td>
<td> <input type="text" onclick="alert('Sum for All Customers: @3!')" value="@2" />
</td>
</tr>

Put it all together on the compound view:

Till Next Time

OBIEE ATR files (reports).

The ATR files in the OBIEE repository manage privileges on a item and provide a “readable” text for both the name and description of an object. Dan Malone of Calpoly did some basic research on how these work.

This article describe the build up of the ATR file when used for a report.

WOW: backup the files before you start to hack them, one misplaced byte can really f*&^k up your system!

Let’s get out our trusty “hexplorer” and open an ATR file:

 

Bytes 00.03 {4} => File Standard; Seems to be always the same

Byte 04 {1} => Length of the object name; Name starts 4 bytes from here.

Byte x..03 {3} => Prequel to object name

Byte x..Byte 04 {Byte 04} => Object name as ascii.

Byte x..11 {11} => prequel 06 00 01 + Hex id of the object owner

Byte x {1} number of users / groups which have form of permission.

Byte x..13 {13} => Prequel 00 01 {2} + Hex id of the grantee (group or user) {8} +
permission: (FF FF  00- Full Control ; 0F 00 00 - Change/Modify ;03 00 00 – Read; ◊02 00 00 – Traverse ; 00 00 00- No Access ) {3} [for each grantee]

Byte x{1} length of the object type name:

Byte x .. 3+byte(x-1)+8 => Prequel 00 00 00 {3} + Object type name as ascii + Sequel 02 00 01 00 04 00 00 00 {8}

Byte x..x+3{4} text Desc

Byte x{1} Length of description

Byte x..byte(x-1)+4=> prequel 00 00 00  {3} description in Ascii; sequel 00 00 00 00 {4}

Now in what way is this info useful? If you want to script your reports, you also need to script the ATR file.. I will hopefully soon have time to show you how to script a report.

 

Till Next Time

OBIEE JavaScript and comments

When you write large pieces of custom javascript on a report you might run into the strange problem that it works during development, you can save it without a problem, but when you reopen it in another session it doesn’t work anymore.

Most of the time this is caused by the rendering process of the report XML by the presentation server.

When obiee loads a report for the first time during a session it performs a SET XML. (like pushing the SET XML button in the advanced TAB =>

This causes your nicely formatted script which looked like this:

to look like this:

Basically it has become one long string. You might run into trouble because of comment lines starting with // in your code. This turns the rest of the string into a comment. Always encapsulate your comments as /*..comment..*/ .

An other problem might be missing semi-colon “; “. Always close your process steps with one.

Till Next Time

OBIEE Chancing CSS style on the fly

One of our customers wanted to see some suggested CSS changes on the fly on actual reports. The wanted it on the production machine…

First we copied the Demo file to a new folder on the OC4J webserver:

On the demo report we added a static textbox:

Put in the reference to the alternate stylesheets:

<link rel="stylesheet" type="text/css" title="Standard"
    href="res/s_oracle10/b_mozilla_4/views.css">
<link rel="alternate stylesheet" type="text/css" title="Red"
    href="res/Demo/views2.css">
<link rel="alternate stylesheet" type="text/css" title="Green"
    href="res/Demo/views3.css">
<meta http-equiv="Default-Style" content="Standard">

If you now open the report in FireFox and goto view => page style

  (sorry only got the Dutch version installed)

And select the style you want to demo:

  = Default

= Red

= Green

Yes this trick will only work in FireFox (View | Page Style), Opera (View | Style), Konqueror(View | Use StyleSheet), Workarounds for IExplorer are widely spread on the net. Haven’t found it in Chrome Yet

Till Next Time

OBIEE Changing unchangeable cells

If you create a pivot view:

Most of the properties can be changed from the pivot view editor:

But they forgot to put an edit box on the top left corner cell:

If you want to set the background color system wide goto the views.css file and change the PTIndentCell:

If you want to change it only for on report add a static text:

add some javaScript like this:

<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 != 'PTIndentCell' ){
continue;
}
tds[td].style.backgroundColor="#CCCC00";
// Info on property naming: http://codepunk.hardwar.org.uk/css2js.htm
lCSS.push(tds[td].innerHTML);
}
for(var len =0; len < lCSS.length; len++){
//document.write("Bingo " + "<BR>");
//debug only
}
</script>

(Yep it’s based on a idea coming from Joe Betram)

Check the contians HTML Markup box:

Check the results:

The naming of CSS properties in javaScript varys slightly from normal CSS: http://codepunk.hardwar.org.uk/css2js.htm

Till Next Time

OBIEE Grabbing the logical SQL

Sometimes you want to “grab” the logical from a dashboard or report without showing it to the user or having to switch on logging.

Step 1: Create you report with an sql view:

Add a static text box:

Add this code from javascript master Joe Betram:

<script type="text/javascript">

// Original code from Joe Betram

// See :http://forums.oracle.com/forums/thread.jspa?messageID=4026864&#4026864
var tds = document.getElementsByTagName('td');
var lSQL = new Array();
for(var td=0;td<tds.length;td++){
if( tds[td].className != 'SqlViewCell' ){
continue;
}
tds[td].style.display = "NONE";
lSQL.push(tds[td].innerHTML);
}
for(var len =0; len < lSQL.length; len++){
document.write("Stored logical SQL in slot " + len + " is: " + lSQL[len] + "<BR>");
}
</script>

Don’t forget to check the contains HTML Markup box:

Check the results:

With a little bit of tweaking you can use this script to for instance to call a web service or channel the SQL to another logging program.

Till Next Time

OBIEE Pivot Conditional Format Grand Total Part 2

In this OTN forum discussion (http://forums.oracle.com/forums/thread.jspa?messageID=4025463&#4025463) JavaSript master Joe and OBIEE Crack Raghu show us two separate ways of making a conditional format for the grand total on a table.
Raghu’s way editing XML:
Let’s start with a basic table:

Make a Pivot:

Put it on a Compound Layout:

On the basic table add a conditional format on a random column:

Open the advanced tab:

locate the saw:condition part :

<saw:condition>
    <sawx:expr xsi:type="sawx:comparison" op="greaterOrEqual">
        <saw:columnRef columnID="c1"/>
        <sawx:expr xsi:type="xsd:decimal">1000000</sawx:expr>
    </sawx:expr>
        <saw:displayFormat backgroundColor="#FF0000" wrapText="true"/>
</saw:condition>

Copy and paste this into an good! XML Editor (fi Notepad ++)

Remove the condition from your bas table:

Go back to the advanced tab:

Copy and paste all XML into your XML EDITOR:

Locate the Pivot View part:

Copy you condition into the column part just below the labels between saw:totalValue and saw:displayFormats tags

Copy the XML back to OBIEE, don’t forget to press the SET XML tag:

There you go:

Although this method of Raghu is very elegant I personally prefer Joe method discussed in part 1. With XML Editing you run into the risk of loosing your stuff when make a change in your report easily, also most user won’t have access to the Advanced TAB.
Till Next Time