Saturday, February 27, 2010

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

Wednesday, February 24, 2010

OBIEE Popup box

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

image

This can be done simple from a narrative view:

First start with a basic report:

image

goto the narrative view:

image

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:

image

Till Next Time

Sunday, February 21, 2010

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:

image

 

image

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

image

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

image

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

image

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

image

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

image

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

image

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]

image

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

image

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}

image

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

image

Byte x{1} Length of description

image

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

Thursday, February 18, 2010

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 => image

This causes your nicely formatted script which looked like this:

image

to look like this:

image

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

Monday, February 15, 2010

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:

image

On the demo report we added a static textbox:

image

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

image  (sorry only got the Dutch version installed)

And select the style you want to demo:

image  = Default

image = Red

image = 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

Friday, February 12, 2010

OBIEE Changing unchangeable cells

If you create a pivot view:

image

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

image

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

image

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

image

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

image

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:

image

Check the results:

image

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

Till Next Time

Tuesday, February 9, 2010

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:

image

image

Add a static text box:

image

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:

image

Check the results:
image

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

Saturday, February 6, 2010

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:
image
Make a Pivot:
image
Put it on a Compound Layout:
image
On the basic table add a conditional format on a random column:
image
Open the advanced tab:
image
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 ++)
image
Remove the condition from your bas table:
image
Go back to the advanced tab:
image
Copy and paste all XML into your XML EDITOR:
image
Locate the Pivot View part:
image
Copy you condition into the column part just below the labels between saw:totalValue and saw:displayFormats tags
image
Copy the XML back to OBIEE, don’t forget to press the SET XML tag:
image
There you go:
image
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