OBIEE dynamic columns in the by statement

Did this one on OTN http://forums.oracle.com/forums/thread.jspa?threadID=1090834&tstart=0
First get your column names into a prompt:

sql is bit like :
SELECT case when 1=0 then "D0 Time"."T02 Per Name Month" else  '"D2 Market"."M04  Region"' end FROM "Sample Sales" UNION ALL SELECT case when 1=0 then "D0 Time"."T02 Per Name Month" else  '"D0 Time"."T02 Per Name Month"' end FROM "Sample Sales"
Put the column name between single quotes!
(Yes I know a LOV is better but this is just for demo)
Create a report:

Edit the rank formula to:
Rank("F1 Revenue"."1-01  Revenue  (Sum All)" by @{pColumn}{"D0 Time"."T05 Per Name Year"})
Put it on a dashboard:


Till Next Time

OBIEE Adding Image to Column Header

Or how to make this:

Goto your column header format:

Put a space in both your table heading and Column Heading, check the custom headings box.

Press the format button for the column heading:

Add some custom CSS:

like :

[code]

background-image:url('res/sk_oracle10/answers/abbcompound.gif');background-repeat:no-repeat;height:30px;

[/code]

Check the results:

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 Excel and slashes

Just a tip from my (time costly) experience when using the Excel plug-in. Try to avoid the '/' - forward slash (fi: costs / hr) in column names . Excel has sometimes trouble handling those columns if the column name is used in further (VBA) scripting. Basically it not a good idea to use any mathematical signs (+-/*^%#&) in a column name.

OBIEE empty presentation tables in a repository in a multi-user environment

If you check in a repository in a multi-user environment, you will notice that it doesn't accept empty presentation tables, which you might use for table indentation.

One solution is to using a dummy column:

To make it invisible for the other users you can make a special user HideColumn:


And set the persions only for this user:

This way OBIEE checks in and out without any problems.

OBIEE EVALUATE function and presentation variables

I have this function defined in a report:
evaluate('bi_server.fnc_XXX_l_ind (%1,%2)' as varchar(250),@{MACHINEID}{'1234'}
,@ {PTESTCODE}{'X56GH'})

both %1 and %2 are varchar

When I put the report in a dashboard and try to fill the presentation values the report comes back with:

[nQSError: 27005] Unresolved column: "X56GH". (HY000)

The reason for this error is that is't trying to use the column "X56GH" instead of the string "X56GH".

The solution is to put single quotes around the presention variables:
evaluate('bi_server.fnc_XXX_l_ind (%1,%2)' as varchar(250),'@{MACHINEID}{1234}','@{PTESTCODE}{X56GH}')

But not around the default values!

OBIEE Dummy Column with NULL value

Sometimes you need a dummy column in Union (ALL) query's with a NULL value.
Try this =>
CAST(NULL as double)