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