OBIEE Hack: Preserve Column Formatting While Editing Direct SQL

Update: I posted a better solution here.

OBIEE's direct SQL reports query databases directly, bypassing its logical data model layer (RPD). This is a quick-and-dirty solution for ad hoc reporting requests. However, there is an annoying issue with direct SQL reports. Every time you modify a report's SQL, you lose its column formatting. Reformatting the same reports again and again could be frustrating if your customers frequently ask for changes to their reports. Here is a hack to preserve column formatting while editing direct SQL reports.

When you modify and validate a direct SQL report's SQL, OBIEE reset all columns to default settings. However, the reset happens only at client side, not on the server. In other words, the original formatting is still saved on the server. It is possible to retrieve the original formatting from the server, and merge it with the modified SQL from your local computer. The bookmarklet below grabs and posts the following information to a web service.

• OBIEE server host name
• Full path of the report on the server
• Modified and validated report in XML format to be saved on the server

	javascript:(function(){
	var host = window.location.host;
	var path = (new URLSearchParams(window.location.search.toUpperCase())).get('PATH');
	if(!path){
	alert('Cannot read report path from URL. Please open the report directly in Edit mode.');
	return false;
	};
	var rpt = XUIPanel.getEditor('idReport').report.outerHTML;
	var data = JSON.stringify({'host':host,'path':path,'rpt':rpt});
	var xhr = new XMLHttpRequest();
	xhr.onreadystatechange = function(){if(xhr.readyState===4 && xhr.status===200){document.body.style.cursor = 'auto';alert(xhr.responseText);}};
	xhr.open('POST', 'http://url.to.my/webservice');
	xhr.setRequestHeader('Content-Type', 'application/json;charset=UTF-8');
	xhr.send(data);
	document.body.style.cursor = 'wait';
	})();

view raw ObieeMerge.js hosted with ❤ by GitHub

The web service is where you do the merge. There are various technologies for implementing web services and manipulating XML. You can mix and match them based on what's already available in you company's technology stack.