OBIEE Pivot table Measure label

One of those things you have to be reminded of how it works.


If you have this request:

You will get a default table view like this:

Let's put it in a pivot table:

Switch the measure label box with the region:

It becomes even more intressting wen we bring the measure label box to row pane:

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 making a column selector in a prompt

On of our customers has a very wide table containing 50+ 'settings' columns and four 'result' columns. For there report they only use one of the setting columns, but they wanted total freedom to select this column (1 of 50+).
A normal prompt for this report would take up a very large portion of the screen and was found very unpractical / unaesthetic.
Here is how we solved this, based on the 'PAINT' example:

First we made a simple report based on the columns Year, Year Ago Dollars and Dollars. For debugging purpose we added the Logical SQL view.
On the year column we added the following filter based on a presentation variable.

No switch to the Advanced tab:
Have a close look at the request XML especially the filter part:


Periods."Year"
FILTER01_LEFT

Now edit the FILTER XML to:

@{FILTER01_LEFT}{'1'}
@{FILTER01_RIGHT}{'1'}
Don't forget the single quotes (').

Press the set XML button: and switch back to your criteria view:

Save the report!
If you run the report and have a look at the logical SQL:


you will see that the filter have evaluated into 1=1, which is always TRUE. It's a good practice not to let it evaluate to NULL = NULL since not every database will give back the same result.
Now create a new prompt called FILTER01_LEFT as a dropdown list based on SQL:

In the SQL statement you put:
SELECT case when 0=1 then Markets.Region else '1' end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else 'Markets."Total US"' end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else 'Markets.Region' end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else 'Markets.District' end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else 'Markets.Market' end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else 'Products."Total Product"' end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else 'Products.Type' end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else 'Products.Brand' end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else 'Products.UPC' end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else 'Products.Color' end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else 'Products.Finish' end FROM Paint
union all
SELECT case when 0=1 then Markets.Region else 'Products."Size X"' end FROM Paint
Test the result:

Add the second prompt FILTER01_RIGHT as edit box prompt:

Test the result:

Save the prompt and open a new dashboard page, put the prompt and the report on the dashboard page:

Test the result

Till Next Time

John Minkjan is a senior BI-consultant at Ciber in the Netherlands, the English text of this article is also published on his compagny blog http://knowledge.ciber.nl/weblog/

OBIEE 10.1.3.3.3 documentation

The first documentation is out :

http://download.oracle.com/docs/cd/E10415_01/doc/bi.1013/e10416/toc.htm

Till Next Time

OBIEE 10.1.3.3.3 it's out

OBIEE 10.1.3.3.3 is out, you can find at OTN. They haven't updated there documentation yet....

Till Next Time

---
Of course I was beaten by Venkatakrishnan : http://oraclebizint.wordpress.com/2008/05/10/oracle-bi-ee-101333-released-and-out-for-download/

Most 'improvements' seem to be in the BI publisher tool. Stil no 'datetime' aware graphs.