Wednesday, December 19, 2007

According to the documentation you can set a scale marker based on a column value.

[quote]Options for these values include a static value, a variable, a valuesourced from a column included in the request, or one derived from a SQLquery.Advanced options enables you to dynamically set values based onresults of acolumn, query, or presentation variable which is evaluatedwhile rendering thechart as follows.[/quote]

It seems that if a query is very complex (5 union all and some dashboard prompts) you loose the column selector (undocumented feature?).

==>> After futher research I discovered that this feature seems tobe turned on or off based on the availble columntypes. If you make the column an aggragate (SUM,MIN,MAX etc) you can use it directly.... <<==

You can add the scale marker manually by first adding a static marker and then editing the XML directly by replacing the linePosition part:

(saw:linePosition>(saw:columnRef columnID="c59"/> (/saw:linePosition>

( Replace the ( with < )where "c59" is the column you want to use as a scale marker.

Tuesday, December 11, 2007

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.

This function is very useful if you want to use the build in statistics functions from the ORACLE database like:

CORR

COVAR_POP

STDDEV_POP

The following example calculates the coefficient of correlation between the list prices and minimum prices of products by weight class in the sample view oe.products:SELECT weight_class, EVALUATE_AGGR('CORR(%1,%2)' as double, product_information.list_price, product_information.min_price)FROM product_information;

If you get a "[38083] The Attribute defines a measure using an obsolete method." error, check whether you set the Aggregation Rule of the logical column correctly.

As often in practice the real world is more complex:Good practice is to tell the EVALUATE function which type of return value it can expect:EVALUATE('DB_Function(%1)' as returntype, {Comma separated Expression}). If you omit this it might sometimes complain that it can't concatenated values from a different type.

Another trap is when you use columns from several tables for the input variables, be sure that they have a direct join. Else OBIEE will try to join them underwater which can lead to unwanted Cartesian products.

One of my customers wanted some data presented in a pivot table with a maximum of 5 columns. If more data is available then a sort of carriage return should be given and continue on next line. (A sort of DTP)

Wednesday, December 5, 2007

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 varcharWhen 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!

Even I had to go with the flow and create my own blog.
Let me introduce myself...

My name is John Minkjan and I'm a senior BI consultant voor CIBER in the Netherlands.
I use this blog to store all the things I run into when using OBIEE. Mostly to use it as a reminder (how did we to that?). Feel free to send me any comments!

My Site's

Subscribe To

Followers

Disclaimer

Opinions expressed are entirely my own and do not reflect the position of my employer, Oracle or any other corporation. I'm NOT responsible for any damages in whatever form caused by the usage of the content of this blog. And Yes I'm human, so I tend to make a misstake every now and then