OBI formatting grand totals – without XML!

  • 0

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



  • 8

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!

 

 

 

 


  • 13

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:

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:

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.

And execute the nqgenoldverrpd command and substitute your values.

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

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