OBIEE Magic Trick !!!

Anybody ever face the typical enhancement requirements on existing dashboard reports about enabling the column sorting for all Tabular reports ? I’m sure ,definitely somebody have faced this . However ,I have faced this kind of new requirements from one of my recent client .

Dashboard Column Sorting

Think of , you have 500 reports already in place and it is very tedious job to hover across all reports individually and put a tick against the “Enable column sorting in dashboards” option .Though this is very simple and straightforward  however too irritating and hectic for developers as he/she has to modify each report and for doing this he/she need to click 7-8 times from editing till saving the request .So altogether you are saving 4000 click and overall a substantial minutes 🙂 Don’t go for a Rapid Fire round !

So how it will be , if  we think of a tweak which will make developers free from this boring and hectic activities of monotonous job . Yes I know , some of you are thinking smartly to make a change on Stylesheet /msgdb XML templates or some tweak on instanceconfig.xml .

Fortunately I came to know that ,it could be achievable  from ” Global Sorting of Dashboard by changing XML ” i.e modifying answerstemplate.XML file from location \\OracleBI\Web\msgdb\messages .

Dashboard Column Sorting2

Only thing you have to do is ,search below strings from answerstemplate.XML file (before change)

<WebMessage name=”kuiCriteriaDefaultViewElements” translate=”no”>
Dashboard Column Sorting3 And replace with below (after change) .

<WebMessage name=”kuiCriteriaDefaultViewElements” translate=”no”><HTML><view signature=”tableView” sortable=”true” /></HTML></WebMessage>

Dashboard Column Sorting4 This has been well documented by Oracle .See this link . After doing it and restarting your services your reports should be automatically sorted and if you edit the request it would by default check the Sorting enable option .

Unfortunately this is not the end of story and that is why it is :

—————— xxxxxx A MAGIC THREAD  xxxxxx ——————-

I have implemented the above solution in my OBIEE version and unfortunately  it doesn’t work good . So I have raised an ORACLE SR and it reveals that ,it is a product bug and hence I presume a Documentation bug .An Enhancement Request (ER) 6619910 has been currently with Oracle Support (P3/P4 Bug 6619910) to be considered for review by Development team for future bug fix release .

Alas ! what will be then about my 500 reports sorting in a moment ?? Yes ,there is way and don’t put your finger cross 🙂

Open your Shared folders using Catalog Manager and do “XML Search and Replace ”  as below in offline mode :

Replace  name=”tableView!1″ rptViewVers=”200510010″ with
name=”tableView!1″ rptViewVers=”200510010″ sortable=”true” . Just restart Presentation Services .

Dashboard Column Sorting5

Voila …. It’s all magic see below ! Yes, all reports have been Sorted dynamically in dashboard within couple of seconds . Note that the above method is not documented and hence do it at your own risk .Suggestion is to keep the backup of catalog before performing the steps .

Dashboard Column Sorting6

Now , just think of how much effort you have saved to enable sort for your dashboard reports … Like a MAGIC  and less tricky so far!   🙂 🙂 🙂

Pivot table – Conditional Coloring and Tooltips

Once I was working for a giant UK based client where we had a requirement to replace the existing .Net system . As everybody know that OBIEE is not a programming language rather a tool and there must have certain limitations as this could not be customized like a language .This is pretty hard to make a realization to them as they mostly focus on business needs rather thinking from technical perspective . Which is true indeed ! So sometime we say this is not possible very upfront and outright and sometime we need to further dig down on any alternative solution or workarounds . Sometime it is successful and sometime not . So this is all about the background of this thread and how myself succeed to help there business achievement .

The requirement was something like there would be multiple measure which need to be displayed against dimensions in pivot table . “Measure 1”  is a target achievement measure which need to be shown either Green or Red status based on target value set (target value is not hard-coded and its dynamic and driven by a backend threshold value ). For other measures at the end month there might be some order which is confirmed , some unconfirmed and some yet to be build . Now once user hover mouse to the colored cell (of those status represented in Legend) it should show the information in tooltips .A valid information in this scenario could be specifying  the dynamically changed date time window and mention the type of the period i.e building , confirmed or unconfirmed .

Lets have a look on the pivot report :

Conditional Coloring and Tooltip

Nice huh ?? I have actually googling around to get a readymade solution and unfortunately it doesn’t help . Finally after building this I even get surprised about nothing is technically impossible !

Nows lets see the how the measure and its status entirely driven dynamically in RPD  .Only change done in webcat is treating the data format as HTML .

 Conditional Coloring and Tooltip2

Conditional Coloring and Tooltip3



However you can put same calculation in Catalog itself in column formula to achieve this . But for this let BI server understand that you want “Server Complex Aggregate” to represent the conditional column measure .

Conditional Coloring and Tooltip4






The commentary is another interesting stuff where based on status of each month order Admin user want to add/update there comments which navigated to a target where Writeback has been implemented . Commentary code is like below :


Conditional Coloring and Tooltip5

Customising Graph OBIEE – A Charismatic Gradient Effect

Sometimes we are so bogged down with our business requirements fullfilling so that its pretty easy to forgot about any enhancement from look and feel perspective which sometimes client demands.”Showmanship” is something should not be avoided and thats why there is common terminology called “Good Presentation” . 

Anyway I am bit fond of colors . If you don’t like colors that means somethings definitely will be colorless in your life 🙂 Thats my opinion …

Keeping in my mind , the professional look and feel sometimes I feel crazy to play with colors to see how a graph representation could be made attractive , verbose and charismatic obviously considering the business fulfillment.Most of them given below adopted the customised gradient effect …

So here you go ….

 Style Graph-2


Style Graph-3


Style Graph-4


Style Graph-5


Style Graph-6


The above all is the output after tweaking the gradient effect in the PCXML file .You need to edit the PCXML files typically resides at : ..\OracleBI\web\app\res\s_oracle10\popbin) and (..\OracleBI\oc4j_bi\j2ee\home\applications\analytics\analytics\res\s_oracle10\popbin). Considering s_oracle is your default Style for dashboard .Then restart the BI Presentation server, BI Javahost service and OC4J .Sometimes you need to clear your “browser cache” also .

Here I modified below files for the special effect:

bar.pcxml file (for Horizontal Bar Chart) as:

<Chart Version=’4.30.00′ BorderType=’None’ Width=’491′ Height=’295′ BGGradientColor=’#ccccff’ GradientType=’RightLeft’>

column.pcxml (for Vertical Chart) like:

<Chart Version=’4.30.00′ BorderType=’None’ Width=’491′ Height=’295′

BGGradientColor=’#C1FFC1′ GradientType=’BottomTop’ >

Line.pcxml (for Line chart) like:

<Chart Version=’4.30.00′ BorderType=’None’ Width=’491′ Height=’295′

BGGradientColor=’#FFE1FF’ GradientType=’TopBottom’ >

Linecolumn.pcxml (for Line Bar Combo chart) like:

<Chart Version=’4.30.00′ BorderType=’None’ Width=’491′ Height=’295′

BGGradientColor=’#CAFF70′ GradientType=’BottomTop’ >

Pie.pcxml (for Pie Chart) like below:

<Chart Version=’4.30.00′ BorderType=’None’ Width=’362′ Height=’318′

BGGradientColor=’#FFC1C1′ GradientType=’BottomTop’ >

As mentioned possible Gradient types are:BottomTop,TopBottom,LeftRight and RightLeft .