OBI formatting grand totals – without XML!

OBI formatting grand totals – without XML!

Get Social!

Recently I needed to have some conditionally formated grand total rows in OBI. I remember years ago hacking around with the XML which is far from ideal. I then realised it could be done another way, here is the example:

 

Create an analysis with a measure column:

Apply the conditional format on the Actual % column

This should result in the following analysis

This is the special bit, add a new calculated item on the column where you want the total

Select the Function (aggregation Rule) and add in all of the values

Now the totals will be applied to the analysis

You can format this calculated row using the format option on the calculated item window



Javascript to Refresh All OBIEE Dashboard Analysis

Get Social!

oracle-biOBIEE isn’t designed for (near) real time reporting, and as such doesn’t have an out of the box page refresh option.

There are times when I’ve needed a dashboard to be refreshed – when looking at query performance dashboards, for example, and would like the charts and tables to be constantly updated.

After searching on-line I found a few different ways to to refresh a dashboard but they all refreshed the whole page, rather than just the Analysis themselves. It’s not the end of the world, but it results in the whole page flickering as the Browser reloads it. It’s most annoying when you’re half way through the ‘Dashboards’ menu, about to click the navigate away and BAM! The page refreshes and you loose your position on the menu.

The following script is a combination of HTML, CSS and JavaScript code to loop through each Analysis on a single Dashboard page and issue the OBIEE Refresh command. A small box towards the bottom left hdashboard-refreshand side of the page that overlays the Dashboard with some controls and stats for the page refresh. It will show when the page was last refreshed, a countdown in seconds to the next refresh and a button to start and stop the automatic refresh.

chrome_256x256 Works in Chrome 44 internet-explorer_256x256 and Internet Explorer 8 and 10

There are 3 variables that are user changeable:

  • vRefreshDuration controls how many seconds between refreshes. Make sure this is higher than how long each query takes, otherwise an Answer will be asked to refresh before it’s finished refreshing!
  • vMessageLastRefreshed is the message displayed before the date and time of the last refresh.
  • vMessageNextRefresh is the message displayed before the countdown in seconds to the next refresh.

Feel free to change the below code, but please keep the header information. If you make any improvements, please let me know!

To add the Refresh code to a Dashboard, create a Text Dashboard drag-text-dashboard-objectObject and drag it onto any Dashboard that you’d like to automatically refresh. Then paste the below code into the Text box and check the ‘Contains HTML Markup’ checkbox. Click save and view your Dashboard!

<!--
        Title:      OBIEE in page refresh
	Author:     James Coyle (www.jamescoyle.net)
	Date:       2015-08-10
	Description:Self contained code to loop through each analysis on a 
                    Dashboard page and issue a Refresh command. This will 
                    refresh each analysis, going back to the data source, 
                    without refreshing the whole page. 
				 
	Version:    2015-08-10                  First revision. 
				
-->
<style type="text/css">
.toggle {
    margin: 4px;
    background-color: #EFEFEF;
    border-radius: 4px;
    border:1px solid #D0D0D0;
    overflow: auto;
    float: left;
}
.toggle label {
    float: left;
    width: 32px;
}
.toggle label span {
    text-align: center;
    padding: 3px 0px;
    display: block;
    cursor: pointer;
}
.toggle label input {
    position: absolute;
    top: -20px;
}
.toggle .input-not-checked:hover{
    background-color: #AAAAAA;
}
.toggle .input-checked{
    background-color: #404040;
    color: #F7F7F7;
	cursor: default;
}
#left-refresh-wrapper{
	float: left;
	padding: 6px 12px 6px 12px;
}
#refresh-heading-wrapper{
	font-size: 16px;
}
#refresh-button-wrapper{
	width: 90px;
	margin-left: auto;
	margin-right: auto;
}
#refresh-text-wrapper{
	padding: 6px 0px 6px 12px;
	float: left;
	font-size: 12px;
	line-height: 17px;
	border-left: 1px solid grey;
}
#floating-refresh-wrapper{
	border: 1px solid grey;
	width: 290px;
	overflow: hidden;
	border-radius: 8px;
	position: fixed;
	left: 20px;
	bottom: 22px;
	margin: auto; 
	background: rgb(255, 255, 255);
    background: rgba(255, 255, 255, .7);
}


</style>
<div id="floating-refresh-wrapper">
	<div id="left-refresh-wrapper">
		<div id="refresh-heading-wrapper">
			Refresh Status
		</div>
		<div id="refresh-button-wrapper">
			<div class="toggle">
				<label id="refresh-on" onclick="event.preventDefault(); toggleButton(this);"><input type="radio" name="toggle"><span class="input-checked">On</span></label>    
			</div>
			<div class="toggle">
				<label id="refresh-off" onclick="event.preventDefault(); toggleButton(this);"><input type="radio" name="toggle"><span class="input-not-checked">Off</span></label>
			</div>
		</div>
	</div>
	<div id="refresh-text-wrapper">
		<div id="last-refresh">Please wait...</div>
		<div id="next-refresh">Please wait...</div>
	</div>
	<div style="clear: both;"></div> 
</div>
<script type="text/javascript">
	// Get report IDs into an array
	function initRefresh(){
		if (typeof vReportIds !== 'undefined'){
			for (key in vReportIds){
				if (key != 'undefined' && key != null){
					vObjectIds.push(vReportIds[key]);
				}
			}
		}
		else{
			vObjectIds = [];
		}
	}

	// Start timer if refreshTimerObject is null
	function startRefreshTimer(){
		if (refreshTimerObject === null || typeof refreshTimerObject === 'undefined'){
			refreshTimerObject = setInterval(function () {timerTick()}, 1000);
			vCountdown = vRefreshDuration;
		}
	}
	
	// Stop timer if refreshTimerObject is not null
	function stopRefreshTimer(){
		if (refreshTimerObject !== null && typeof refreshTimerObject !== 'undefined'){
			clearTimeout(refreshTimerObject);
			refreshTimerObject = null;
			updateNextRefresh('-');
		}
	}

	// Loop known report IDs and send them to the OBIEE refresh function
	function timerTick(){
		if (vCountdown === 1){
			refreshReports();
			vCountdown = vRefreshDuration;
			updateLastRefreshed();
		}
		else{
			vCountdown = vCountdown - 1;
		}
		
		updateNextRefresh(vCountdown);
		
	}
	
	// Update display countdown
	function refreshReports(){
		for (key in vObjectIds){
			HereLink(vObjectIds[key], 'Refresh');
		}
	}
	
	// Update last refresh time on Dashboard
	function updateLastRefreshed(){
		document.getElementById("last-refresh").innerHTML = vMessageLastRefreshed + formatDate(new Date());
	}
	
	function formatDate(dt) {
		var dd = dt.getDate();
		var m = dt.getMonth() + 1;
		var yyyy = dt.getFullYear();
		var hh = dt.getHours();
		var mm = dt.getMinutes();
		var ss = dt.getSeconds();
		return padZero(dd, 2) + '-' + padZero(m, 2) + '-' + yyyy + ' ' + padZero(hh, 2) + ':' + padZero(mm, 2) + ':' + padZero(ss, 2) ;
	}
	
	function padZero(v, l){
		var r = v;
		if (v.toString().length < l){
			for(a = v.toString().length; a < l; a++){
				r = '0' + v;
			}
		}
		return r;
	}
	
	// Update last refresh time on Dashboard
	function updateNextRefresh(i){
		document.getElementById("next-refresh").innerHTML = vMessageNextRefresh + i;
	}
	
	// toggle button and change timer status
	function toggleButton(o){
		if (o.childNodes[1].innerHTML !== vLastSpanClick){
			vLastSpanClick = o.childNodes[1].innerHTML;
			var spans = document.getElementById('refresh-button-wrapper').getElementsByTagName('span');
			for (el in spans){
				spans[el].className = 'input-not-checked';
			}
			
			o.childNodes[1].className = 'input-checked';
			
			if (o.childNodes[1].innerHTML === 'Off'){
				stopRefreshTimer();
			}
			else{
				startRefreshTimer();
			}
		}
	}
	
	var vRefreshDuration = 5;
	var vMessageLastRefreshed = '<strong>Last refreshed :</strong> <br />';
	var vMessageNextRefresh =  '<strong>Next refresh : </strong>';
	
	var vObjectIds = [];
	var refreshTimerObject = null;
	var vCountdown = 0;
	var vLastSpanClick = 'On';
	
	initRefresh();
	updateLastRefreshed((new Date).toLocaleString());
	updateNextRefresh(0);
	startRefreshTimer();
	
</script>

 

 

 

 


Downgrade an OBIEE RPD Version

Category : How-to

Get Social!

oracle-biAn RPD file saved by one version of the OBIEE Administration Tool cannot be opened by an older version of the OBIEE Administration Tool or be applied to an older version of an Oracle BI Server. For  this example we’re looking at an RPD created with version 349 of the Administration Tool (that’s the first release of OBIEE 11.1.1.9) and we’re trying to apply it to a server expecting version 326 (that’s the latest patched release of OBIEE 11.1.1.7.150120)

You can check the version of the tool you’re currently using by going to Help > About in the menu and look at the Repository Version line.

About-Oracle-BI-Administration-Tool

You’ll get an nQSError 36010 from the Administration tool if the tool is not of a high enough version:

nQSError: 36010] Server version 326 cannot read the newer version 349 of the repository c:\oracle\middleware\fmw\instances\instance1\bifoundation\OracleBIServerComponent\coreapplication_obis1\repository\Dev_Copy.rpd

Luckily there is a way round it! For certain versions you’ll be able to downgrade your RPD file so that you’re able to open it in the older version of the Admin tool. This isn’t true for all version, but from my test the below major versions worked for the downgrade process:

  • 11.1.1.6 > 11.1.1.5 (318 > 308)
  • 11.1.1.9 > 11.1.1.7 (349 > 326)

To downgrade the RPD file you’ll need to use an Admin Tool version that can open the file. Carrying on our example, we’ll need to use the 11.1.1.9 Admin Tool. You will need the nqgenoldverrpd utility which is part of the Administration Tool installation and can be found in the below path:

C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client Tools\oraclebi\orahome\bifoundation\server\bin

The utility has a few switches which you’ll need to enter to tell it what it needs to know to downgrade the RPD file.

  • -P – is the RPD password of the source RPD you wish to downgrade.
  • -I –  is the source RPD you wish to downgrade.
  • -O – is the target name and path for the downgraded RPD
  • -V – is the RPD version to downgrade to.

There is also a -F which will force the downgrade process to clear any expressions that cannot be downgraded.

The following example will downgrade an RPD on your desktop with password Admin123 and save it to a new RPD file on your desktop. The downgraded version will be 326 which is OBIEE 11.1.1.7.

First of all, open a CMD prompt in Windows and navigate to the above path, or the path where your Administration Tool is installed.

C:\Program Files\Oracle Business Intelligence Enterprise Edition Plus Client Tools\oraclebi\orahome\bifoundation\server\bin

And execute the nqgenoldverrpd command and substitute your values.

nqgenoldverrpd -P Admin123 -I C:\Users\James\newversion.rpd -O C:\Users\James\oldversion.rpd -V 326

You’ll see the below message once it’s complete:

Reading newversion.rpd...
Preprocessing...
Generating oldversion.rpd of version 326...Done!!!

nqgenoldverrpd File Paths

You can find the nqgenoldverrpd utility installed as part of the OBIEE Admin Tool or under an OBIEE installation on Windows.

  • On Client under : [CLIENT_FOLDER]\oraclebi\orahome\bifoundation\server\bin\nqgenoldverrpd.exe
  • On Server under : $MW_HOME\bifoundation\server\bin\nqgenoldverrpd

Visit our advertisers

Quick Poll

What type of VPN protocol do you use?

Visit our advertisers