Monday, June 28, 2010

OBIEE dynamic pivot calculations

In http://obiee101.blogspot.com/2009/01/obiee-rowbased-calculations-in-pivot.html I showed you how can make make your own calculations in a pivot table. But what if you want to make it more dynamic? Consider this: The forecast is .4 * q1  + .6 * q3.
If we do it the “hardcoded” way we get:
image
This works fine:
image
But if we switch to 2008:
image
we loose the forecast.
you can make it dynamic by chancing the “hard-coded” row or column names to dynamic ones using the dollar sign $:
image
Now you always have a forecast:
image and image

Andreas made a smart remark:


Just for completion I would add the fact that $1 stands for the first element of the request. And as a warning (Wow) that the sorting there is determined by the request and not by the constellation in the pivot-table. Because this can cause some people to search around quite long, for example if you change the sorting in the request OBIEE will add up different quarters than before etc.


Till Next Time
PS @eejimkos it works fine in al my browsers:
Firefox:
image
IExplorer:
image
Chrome:
image

6 comments:

eejimkos said...

Here,we have to mention that this only works in Internet Explorer and not in Firefox.I was really confused in the past..
Keep the good work.

John Minkjan said...

@eejimkos it seems to work fine in al my browsers, can you eloborate a bit? What am I missing?

eejimkos said...

Hello,
Probably,i have not explained it 100%.It works fine in all browsers.

The problem is in the development.

I just tried to make a "New Calculated Item" in Firefox.It does not allowed it.It shows,"The semantics of the formula entered are not valid".But in Explorer works just fine.As you mentioned ,the results are working in all browsers.
I use the version,10.1.3.4 and firefox 3.6.6(and in older versions).

John Minkjan said...

@eejimkos

I do all my development in firefox (Mozilla/5.0 (Windows; U; Windows NT 5.1; nl; rv:1.9.2.6) Gecko/20100625 Firefox/3.6.6 ( .NET CLR 3.5.30729)) it works fin here... Do you have a specific language version?

regards

John

eejimkos said...

Hello,again.
I faced this issue about a year ago.
I was searching it for about 1-2 days,until i used explorer.
I use the Greek version.
I've just search it more....
Now,it passes me the message i mentioned above when i try to "commit" for first time my calculation,but with a second try,it accepts it.
The same, it happens if i want to modify it.
It seems to work.(even with a second try)
Thanks for your quick reply.
I put this comment,because at that time,this "calculation" was a nightmare for me and i wanted to help.

**
Will you in the future,make a comparison between Cognos 8.x and OBIEE?

Or the new tool PowerPivot <-> TM1?

What will be the response from Oracle?
**
Tnks

Palani said...

I use this functionality quite often, Only problem is we cant use this column in prompt to filter the set.
- Palani