OBIEE Conditional format based on other column in charts

When you want to do a conditional format on a chart in OBIEE by default you only have two choices:

Based on a value or based on a presentation variable.

But what if you want to do it based on an other column. Let’s say you want the bar to be completely red if it’s below the line and completely green if it’s above the line:

First add two ‘dummy’ conditions one for red and one for green:

Next goto the advanced tab and located the conditions in the XML:

<saw:conditionFormats>
            <saw:conditionRule>
               <sawx:expr xsi:type="sawx:comparison" op="less">
                  <saw:columnRef columnID="c1"/>
                  <sawx:expr xsi:type="xsd:decimal">0</sawx:expr></sawx:expr>
               <saw:visualFormats>
                  <saw:visualFormat className="barProp" name="normalBar" color="#FF0000"/></saw:visualFormats></saw:conditionRule>
            <saw:conditionRule>
               <sawx:expr xsi:type="sawx:comparison" op="greaterOrEqual">
                  <saw:columnRef columnID="c1"/>
                  <sawx:expr xsi:type="xsd:decimal">0</sawx:expr></sawx:expr>
               <saw:visualFormats>
                  <saw:visualFormat className="barProp" name="normalBar" color="#00FF00"/></saw:visualFormats></saw:conditionRule>

Chance <sawx:expr xsi:type="xsd:decimal">0</sawx:expr> to the column you want use like: <sawx:expr xsi:type="sawx:sqlExpression">"11 Time Series"."1-07  Revenue  (Qago)"</sawx:expr>

Don’t forget to press the set XML button:

hmmmm this is not what we want!

What went wrong? The expression builder for the chart conditional format accepted our tweak:

The problem is the way the Presentation-server tells the Corda Engine to create the graph. Since the Corda engine can’t ‘see’ the data it needs to get it’s conditions HardCoded. This means the Presentation server will evaluate the first value of the SQL expression and sent it to the Corda engine.

Is there a workaround? It’s actually quit simple. Add two more columns to your table RED and GREEN:

Add them to your chart and make it a stacked bar:

Remove the legend:

Add it to your compound layout and add a extra legend:

Till Next Time

OBIEE Opaque data labels on charts

Recently I was asked how to get opaque (see trough) data labels on a chart:

This is control by the pcxml file belonging to the Graph type:

Open the file in an editor and look for the "DataLabels" tag. Edit the last to numbers (hex) of the BGColor subtag to "00"

(sync the file with the one in the OC4J if used). Restart everything.

Till Next Time

OBIEE TimeLine graph Line settings

In 10.3.4.0 Oracle introduced finally the DTA or Time Line graph. In there haste to release it the forgot the chart properties editor. This means we have to edit the PCXML files ourselfs, to get dotted lines, other colors or the plus symbol.

In the timeline.pcxml file look for the SeriesDefinition tag:

Within this tag you can set the following properties:

LineColor='#ff0033'
{the color of the line; possible colors all web RGB colors}

SymbolColor='#3366ff'
{the color of the Symbol; possible colors all web RGB colors}

FillColor='#66ff99'
{the color of the FillColor; possible colors all web RGB colors}

LineWidth='1.0'
{the width of the line in pixels; possible values 'Off','1.0','2.0','3.0','4.0','5.0','6.'0'}

LineStyle='Dashed'
{the style of the line; possible values 'Plain','Dashed', 'Dotted', 'DashDot'}

SymbolType='Circle'
{the symbol at the datapoint; possible values 'Off', 'Dot', 'SmallSquare', 'Square','SquareOutline', 'Circle', 'CircleOutline', 'Triangle', 'TriangleOutline', 'Plus', 'PlusOutline', 'Diamond', 'DiamondOutline'}

ShowArea='True'
{show the area below the line; possible values 'True', 'False'}

Till next time

OBIEE 10.3.4.0 missing popchart service

On a clean install of OBIEE10.3.4.0 op windows 2003 somehow the popchart service doesn't get installed causing you to see no charts. This is easily solved by running two scripts:
installCOMEmbedder.bat (not required but handy)
installService.bat
both can be found in ..\OracleBI\corda50\bin


Now you have to start the service manualy or reset the whole server.


Till Next Time

OBIEE Bottom scale on the time line graph

By default the bottom scale on a timelin graph is set to automatic. The OBIEE interface doesn't allow you to influence it. Of course we found a workaround. You can do this by altering the PCXML file. The scale is controlled by the tags in the ValueScale Position='Bottom' part. Try playing around with ManualTicksMajor='Quarter' and ManualTicksMinor='Month' . Possible values are 'Year', 'Quarter', 'Month', 'Week', 'Day', 'Hour', 'Minute'.

Till next time

OBIEE customising your PCXML

Altough NOT supported by Oracle you can do a lot of customizing of your PCXML files. These file control the appearance of your Charts and craphs in OBIEE.

Some documentation can be found on your OBIEE server: ..\OracleBI\corda50\docs\graph_reference\graph_reference.pdf

Till Next Time

OBIEE "Private" output type for a chart

Sometimes you want the OBIEE chart in a higher graphical definition. This can be done by altering the instanceconfig.xml. Problem with this is that this will change the setting for the whole presentation server.


At one of our customers we had the need only to change this for the scatter chart and leave the rest on default (Flash)


This is how we did it:

Warning: Remember PNG and JPEG graphical types are not down drillable!

Step 1: Altering imagetypes.cxml

First locate your Chart support directory, usually found in …OracleBI\web\app\res\s_oracle10\chartsupport

Make a copy of “imagetypes.cxml” in the same directory, give it a name which refers to the chart type you want to use (eg: “imagetypesScatter.cxml” )

Open the renamed file (e.g. with Notepad):


Changed preferred to the image type you want (e.g.: “png”)






! Save the file

Step 2: Altering charttype.cxml

Now locate the charts directory usually found ….\OracleBI\web\app\res\s_oracle10\charts


Open the .CXML file for the chart type (e.g.: “scatter.cxml”)

Alter the
{?xml:namespace prefix = xi /}{xi:include parse="xml" href="fmap://chartSupport/imagetypes.cxml"}{/xi:include}{?xml:namespace prefix = xi /}{xi:include parse="xml" href="fmap://chartSupport/imagetypes.cxml"}{/xi:include}

(of course use the correct tag sign)

Till Next Time

OBIEE Changing the line color in a scatter graph with line

In OBIEE they forgot to put in a editor to change the line color for a Scatter with lines graph. It's a known issue on Metalink3. A simple workaround is to edit the PCXML file for this graph type.

You can find it in:

...\OracleBI\web\app\res\s_oracle10\popbin



Open the file scatter.pcxml

after the {/legend} tag and before {/graph} tag add the following:

{SeriesDefinition Number='1' Color='#2175BB'/} {SeriesDefinition
Number='2' Color='#F4B44D'/} {SeriesDefinition Number='3'
Color='#A4C000'/} {SeriesDefinition Number='4'
Color='#F9F400'/} {SeriesDefinition Number='5'
Color='#82AFDD'/} {SeriesDefinition Number='6'
Color='#000000'/} {SeriesDefinition Number='7'
Color='#C8C8C8'/} {SeriesDefinition Number='8'
Color='#CC6600'/} {SeriesDefinition Number='9'
Color='#CC99CC'/} {SeriesDefinition Number='10'
Color='#EE00EE'/} {SeriesDefinition
Number='11'/} {SeriesDefinition
Number='12'/} {SeriesDefinition
Number='13'/} {SeriesDefinition
Number='14'/} {SeriesDefinition
Number='15'/} {SeriesDefinition
Number='16'/} {SeriesDefinition
Number='17'/} {SeriesDefinition
Number='18'/} {SeriesDefinition
Number='19'/} {SeriesDefinition Number='20'/}

(Note replace the { and } with correct tag symbols)

Till Next Time

OBIEE Graphs Gradient Effect

The default graph type are rather plain a simple 'professional' effect can be created by playing around with the gradient effect in the PCXML file:

<-Chart Version='4.30.00' BorderType='None' Width='491' Height='295' BGGradientColor='#ccccff' GradientType='BottomTop' >

possible types are:

• BottomTop
• TopBottom
• LeftRight
• RightLeft

Till Next Time

OBIEE adding a customized graphtype

One of our customers had the need for some extra customized graphtypes in answers. It took me a while to figure out how OBIEE connects but here is how we did it.

1. In the map {OracleBI}\web\app\res\s_oracle10\popbin make a copy of the PCXML for the graphtype you want customize.


2. rename the file [FI:bubble2.pcxml]


3. Open the file and make the custyomistions you want


4. In the map {OracleBI}\web\app\res\s_oracle10\charts make a copy of the CXML for the graptype you want to customize.


5. rename the file [FI:bubble2.cxml]


6. open the file and edit in sawc:template => displayNameRef="kmsgChartCustomBubble"


7. edit in sawc:seriesRules => displayNameRef="kmsgBubbleCustomSeries"
8. edit in sawc:engineSpecific file="popbin/bubble2.pcxml"


9. Save and close the file.


10. Next we have edit the messages !you have to this for every language you use! (remember to back it up, else you loose it after every (re-)install/update of OBIEE) [EDIT: or make a custommessage folder for each language] go to {OracleBI}\web\msgdb\l_en\messages


11. open "chartmessages.xml" and add the entry <-WebMessage name="kmsgBubbleCustomSeries"><-TEXT>Bubble - Custom


12. Save and close the file


13. open "charviewmessages.xml" and add the entry <-WebMessage name="kmsgChartCustomBubble"><-TEXT>Bubble - Custom


14. Save and close the file


15. restart the Java-host and the prestationserver.

note: replace "<-" with "<"

Till Next Time

OBIEE X,Y and data in mouse over label

One of our custumers wants not only the data value in the mouse over label but als the X and Y axis value.

You can achieve this by editing the ".PCXML" for the graph type.

%_BUBBLE_VALUE=> The value for the bubble item. [bubble]
%_CATEGORY_NAME => The name of the category that the data item belongs to. [All, except, X-Y and Time Plot]
%_CATEGORY_TOTAL => The sum of all data values in the category to which the data item belongs. [Area, Bar, Line, Pareto, Pie, Radar]
%_CLOSE_VALUE => The close value for a high-low data item. [Stock]
%_GRAPH_TOTAL => The sum of all data values in a bar, line, pie, or radar graph. [Area, Bar, Line, Pareto, Pie, Radar]
%_HIGH_VALUE => The high value for a high-low data item. [Stock]
%_LOW_VALUE => The low value for a high-low data item. [Stock]
%_OPEN_VALUE => The open value for a high-low data item. [Stock]
%_PERCENT_OF_CATEGORY => The data value represented as a percentage of the sum of all data values in its category. [Area, Bar, Line, Pareto, Pie, Radar]
%_PERCENT_OF_TOTAL => The data value represented as a percentage of the
sum of all data values in the graph. [Area, Bar, Line, Pareto, Pie, Radar]
%_SERIES_NAME => The name of the data series that the data item belongs to. [All]
%_TIME_VALUE => The time value for a Time Plot data item. [Time Plot]
%_VALUE => The value of the data item. [Area, Bar, Line, Pareto, Pie, Radar]
%_XVALUE => The x value for an X-Y data item. [X-Y]
%_YVALUE => The y value for an X-Y or Time Plot data item. [X-Y, Time Plot]

You can find the ".PCXML" files here: {OracleBI}\web\app\res\s_oracle10\popbin or {OracleBI}\web\app\res\s_Siebel77\popbin

look for
<- DataLabels Font='Size:9;Style:Bold;' TransparentBackground='False' BGColor='#FFFFFFcd' BorderColor='#EEEEEE' FormatString='%_BUBBLE_VALUE'/>
After editing you have to restart the java host and the presentation service.

Till Next Time

OBIEE Corda Popchart Example

A customer asked me to demonstrate the default "CORDA" graph types availible in OBIEE to insert into a dashboard. After a lot of ctrlC and ctrlV if made this HTML page for future reference. (You have to run it on your OBIEE / CORDA server)

graph_type_samples.zip

Till Next Time

OBIEE Corda Popchart

If you want to use the examples for the Corda Popchart Engine in OBIEE you first have to register a DLL .
The script is called "installCOMEmbedder.bat" can be found here:
?:\OracleBI\corda50\bin

Till Next Time