OBIEE Hack: Save and Restore Column Formatting While Editing Direct SQL Reports

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. I previously developed a client-server hack to preserve column formatting while editing direct SQL reports. This post is a pure client-side, simpler, and more elegant solution.

Just drag and drop this bookmarklet to your browser's bookmark bar: Direct SQL Wizard.

When you edit a report's SQL, click the bookmarklet. It will add 3 new buttons sandwiching the native "Validate SQL" button as shown in the following screenshot. The new buttons' function is self-evident.

Below is the bookmarklet's JavaScript code.

	javascript:(function(){
	window.showObieeMsg = window.showObieeMsg || function(msg){
	var msgDiv = document.querySelector('td#idStatusIndicator div');
	if(!msgDiv){
	msgDiv = document.querySelector('td#idStatusIndicator');
	msgDiv.innerHTML = '<div class="StatusIndicatorDiv"></div>';
	msgDiv = document.querySelector('td#idStatusIndicator div');
	}
	msgDiv.innerHTML = msg;
	setTimeout(function(){msgDiv.innerHTML=''}, 5000);
	};
	var b = document.querySelector('td.XUIPromptCaption');
	b.insertAdjacentHTML('beforebegin', '<td class="XUIPromptCaption"><span class="minibuttonOn"><a title="Save column format." href="javascript:void(null)">Save Column Format</a></span></td>');
	b.insertAdjacentHTML('afterend', '<td class="XUIPromptCaption"><span class="minibuttonOn"><a title="Restore by Position." href="javascript:void(null)">Restore by Position</a></span></td>');
	b.nextSibling.insertAdjacentHTML('afterend', '<td class="XUIPromptCaption"><span class="minibuttonOn"><a title="Restore by Name." href="javascript:void(null)">Restore by Name</a></span></td>');
	document.querySelector('td.XUIPromptEntry').setAttribute('colspan', 5);
	b.previousSibling.firstElementChild.firstElementChild.onclick = function(){
	var columns = XUIPanel.getEditor('idReport').criteria.selectSingleNode('//saw:columns');
	if(!columns){
	showObieeMsg('No column format available');
	}else{
	window.columnCache = columns.cloneNode(true);
	showObieeMsg('Column format saved');
	}
	};
	b.nextSibling.firstElementChild.firstElementChild.onclick = function(){
	if(!window.columnCache){
	showObieeMsg('Column format not saved');
	return false;
	};
	var cached = window.columnCache.selectNodes('//saw:column');
	var cols = document.querySelectorAll('td.SelectCellC');
	var cnt = Math.min(cached.length, cols.length);
	for(var i = 0; i < cnt; i++) cols[i].tColNode.outerHTML = cached[i].outerHTML;
	XUIPanel.getEditor('idReport').displayHTMLColumns();
	showObieeMsg('Column format restored by position');
	};
	b.nextSibling.nextSibling.firstElementChild.firstElementChild.onclick = function(){
	if(!window.columnCache){
	showObieeMsg('Column format not saved');
	return false;
	};
	var cached = {};
	window.columnCache.selectNodes('//saw:column').forEach(function(c){
	var n = c.selectSingleNode('.//saw:text');
	if(n && n.innerHTML) cached[n.innerHTML] = c.outerHTML;
	});
	document.querySelectorAll('td.SelectCellC').forEach(function(i){
	var m = i.tColNode.selectSingleNode('.//saw:text').innerHTML;
	if(cached[m]) i.tColNode.outerHTML = cached[m];
	});
	XUIPanel.getEditor('idReport').displayHTMLColumns();
	showObieeMsg('Column format restored by name');
	};
	})();

view raw DirectSqlWizard.js hosted with ❤ by GitHub

Browser compatibility: Google Chrome (yes), FireFox (yes), Opera (yes), IE (no).