OBIEE

Changing the placement of currency name

2012-12-05T06:15:00.001-08:00

Usually currency symbol is shown before the currency value ($1000) and currency name appears after the value (1200 USD). In OBIEE,currency name/symbol formatting is handled by currency.xml

Have a look at this report.

In my case currency name is INR.

I want INR to be appeared before the AMOUNT_SOLD column value.

Let us go and open the currency.xml and search for the currency name INR.

Edit the highlighted line. Add a tag : format="$ #"

This will look like:

Save the changes. Restart the BI services. Reload the metadata and clear the browser cache.

Now let's look at the result.

Thanks.

Column Format Value Suppression - Change Default Option

2012-12-04T03:00:00.000-08:00

Requirement is to change the default option - Suppress in the Column Format Value Suppression to Repeat.
This is with reference to this OTN POST.

Before modification let us see what we have to achieve.

Take any one presentation column and go to column properties->Column Format
The default value of option "Value Suppression" is set to "Suppress"

We have to change this default option to "Repeat"

Edit the java script file formatcolumn.js which is placed at 2 location:

C:\OBIEE11G\Oracle_BI1\bifoundation\web\app\res\b_mozilla\answers
AND
C:\OBIEE11G\user_projects\domains\bifoundation_domain\servers\AdminServer\tmp\_WL_user\analytics_11.1.1\silp1v\war\res\b_mozilla\answers

Change the default value from

var a=XUIGetAttributeString(this.defaultElement.getDefaultFormatting().selectSingleNode
("saw:formatSpec"),"suppress","default");

TO

var a=XUIGetAttributeString(this.defaultElement.getDefaultFormatting().selectSingleNode
("saw:formatSpec"),"repeat","default");

Restart the BI services. Reload the metadata. Refresh the browser.

You will find the default value being changed to "Repeat"

Here it is-

This is the system wide change so remember to take back-up of the files which you are going to modify.

Thank you.

Measure prompt

2012-11-09T23:46:00.000-08:00

Hello
Taking the reference of this OTN Post I thought of playing around prompts and measure columns.
The exercise is based on Sample Sales Lite SA ( XML Based SA )
Environment- OBIEE 11.1.1.6.0 ( Windows 7 -32 bit /Tested on IE 8 )

SA- Sample Sales Lite
Create an analysis of 3 columns namely
1> "Time"."Per Name Year"
2>"Base Facts"."Revenue"
3>"Base Facts"."Revenue"
I would rename the columns ( no. 2 & 3 ) as shown in the snapshot below.

Column formula of column no 2

Column formula of column no 3 ( You can have complex calculation - In the given example I have used a very simple one )

Save the report. [R1]

Go back to home page and once again navigate to Sample Sales Lite SA.Take 2 to 3 measures for example in the analysis and capture their names from the respective column formulae.We would need this while creating a dashboard prompt. Look at the below snapshot.

"Base Facts"."Revenue"
"Base Facts"."Billed Quantity"
"Base Facts"."Discount Amount"

Now go to dashboard prompt editor.Select the option Variable Prompt.Add the details as shown in the below snapshot.

Save the prompt and pull it on the dashboard with the report R1.

Select the first measure ( Revenue ) and hit apply.You will see the Revenue measure values getting populated in the second column while values of 3rd column would base upon the 2nd column that we select from the prompt.

You may find blogs on changing the column name dynamically.

I have to test this solution against an SA based on a relational database and perform multiple rounds of testing. Try it out - You may find some even more interesting facts. Good luck.

Thanks.

User Specific UI in OBIEE 11g

2012-10-16T11:04:00.000-07:00

Aim - Make the user interface dynamic or user specific. For example user [weblogic] wants to see the background of front end in default blue color while user Mike wants to see it in green color. Let us see how we can achieve this step by step.

Create a folder say CUSTOM_UI. You need to copy the following folders and paste them to CUSTOM_UI.

1) Folder s_blafp from C:\OBIEE11g\Oracle_BI1\bifoundation\web\app\res

2) Folder sk_blafp from C:\OBIEE11g\Oracle_BI1\bifoundation\web\app\res

3) Folder WEB-INF from C:\OBIEE11g\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\analyticsRes

I have kept the folder CUSTOM_UI in OBIEE11g home directory.

Rename the folders s_blafp to s_Mike AND sk_blafp to sk_Mike.

Go to C:\OBIEE11g\CUSTOM_UI\sk_Mike\b_mozilla_4 and search for "custom.css"

Change the background color of the 2 nodes:

->.HTMLBody{font-family:Tahoma,sans-serif;font-size:11px;background-color:#336600!important;overflow:auto;margin:0;padding-left:5px;}

->.HeaderContainer{background-color:#336600;font:11px Tahoma;position:relative;}

Save the changes.

Now login to weblogic console. Press Lock & Edit. Go to deployment and press Install as shown in the snapshot below.

Select the CUSTOM_UI folder using the Browse button and follow the instructions as shown below.

Now just go to tab Target and verify if the AdminServer has been selected.

Click on Save. Now Click on Activate Changes.

Once it is done - Go to Deployment option and search for our newly deployed application CUSTOM_UI.

Have a look at its state. It will be in "Prepared" state - >

Select the application CUSTOM_UI and press Start [ Servicing All Requests ]

You will now see its status being changed to Active.

Now we are going to create a database table with 3 columns -- Username , UserStyle and UserSkin.

Have a look :

Note that the default style and skin of user weblogic is "blafp" and we have created a new

style and skin named "Mike" for user Mike.

IMPORT THIS table to OBIEE repository.

Create a SESSION INITIALIZATION BLOCK as shown below.

BLOCK SQL : SELECT USER_STYLE, USER_SKIN FROM USER_SKIN_STYLE

WHERE upper(USERNAME)=upper(':USER')

Note : While Creating a variable SKIN - a message will pop up asking if you want to overwrite system variable SKIN. In that case - Say YES.

Save the changes.

Add the below tags to instanceconfig.xml

<URL>

<CustomerResourcePhysicalPath>C:\OBIEE11g\CUSTOM_UI</CustomerResourcePhysicalPath>

<CustomerResourceVirtualPath>/CUSTOM_UI</CustomerResourceVirtualPath>

</URL>

<UI>

</UI>

RESTART THE BI SERVICES.

Once the login page comes up :

1] Login with weblogic

Note that user weblogic can see the blue background ( default skin : blafp )

Now log out .

2] Login with Mike.

Note that user Mike can see green background. ( Custom Skin : Mike )

Note : Changing the background color was just an example. You can actually change the images,texts and so on. So just play around with that CSS stuff.

Thank you and wish you all the best.

OBIEE 11.1.1.6.4 is available

2012-09-27T07:04:00.003-07:00

The Business Intelligence Enterprise Edition 11.1.1.6.4 patchset has been released and is available to download from My Oracle Support.

The Oracle Business Intelligence 11.1.1.6.4 patchset is comprised of the following patches:

patch 14538078 Patch 11.1.1.6.4 (1 of 7) Oracle Business Intelligence Installer
patch 14538128 Patch 11.1.1.6.4 (2 of 7) Oracle Real Time Decisions
patch 14285344 Patch 11.1.1.6.4 (3 of 7) Oracle Business Intelligence Publisher
patch 14538164 Patch 11.1.1.6.4 (4 of 7) Oracle Business Intelligence ADF Components
patch 14415773 Patch 11.1.1.6.4 (5 of 7) Enterprise Performance Management Components Installed from BI Installer 11.1.1.6.x
patch 14405222 Patch 11.1.1.6.4 (6 of 7) Oracle Business Intelligence
patch 14409674 Patch 11.1.1.6.4 (7 of 7) Oracle Business Intelligence Platform Client Installers and MapViewer

Follow the same process as described in the article :
http://obiee1000.blogspot.com/2012/09/upgrade-obiee-111160-to-111162-patchwork.html

BI Composer - Graph Converted to Pivot View

2012-09-25T01:21:00.000-07:00

I am connecting to BI Composer.

Turn ON the accessibility mode. Select the analysis and any of the subject areas. BI Composer Page will open.

Open the Testing Subject Area and Select 2 columns ( 1 dimension column say Year and 1 measure for example Amount Sold )

Go next - I would like to select Graph view and would prefer Bar Recommended view.

Let us preview the results.

Where is the Graph ? Where is it ? It has got converted into Pivot view.
And look at the title - It says "Graph Converted Pivot Table"

How can i see the Bar graph ?

Just open the main page of your analytics that might have been minimized and TURN OFF the accessibility mode and refresh the Page. Remove cache/Cookies.

Now go back to BI Composer page and cancel the request and from the left hand corner - click on Create.
Select the same subject area and create one request.

Now select the Graph view and check if the graph/chart is getting displayed on the BI Composer page.
Play preview.

Yaa !! Graph view is now graphical.

Thank you.

Wish you good luck.

Configuring BIComposer

2012-09-21T03:52:00.002-07:00

This article explains the configuration of Bi Composer.
Environment : OBIEE 11g -Windows 7-32bits -Oracle database 11g.
Here we begin.

Stop the BI Services.

Go to Weblogic Configuration wizard. The path is shown below.

Just follow the steps as shown in the series of snaps below.

Now Start the BI Services and Open the Console application as shown below.

Press Lock & Edit button. Go to bifoundation_domain - Services - Data Sources as shown below.
Follow the instructions as shown in the snaps below.

So we have tested the JDBC connection and its successful.
Note : (Look at the snap "Create a new JDBC Data Source" - In the database name I have given the full name orcl.us.deloitte.com. Earlier I had given orcl only but the JDBC connection failed so you may try giving full name )

Click Finish.
Activate the changes as shown below.

Changes have been activated.

Now login to Enterprise Manager and go to Deploy option as shown in the snapshot below.

Follow the onscreen instructions and steps as shown below.
Select the .ear file in the path shown below.

Click on the pencil-shaped button and select mds-bicomposer and press OK.

Partition name : bicomposer - Next.

Deploy.

We will have BiComposer in Application Deployments. Right click and go to System MBean Browser as shown below.

Search for oracle.adf.share.connections and expand it till you get bi-default.
Edit the username and password as shown below. Apply changes.

Go to ADFConnections - Operations tab - Click on Save button and Invoke.

Add an entry in the instanceconfig file

Now we have to restart the BI services. Clear the browser cache and remove temporary internet files.

Login to OBIEE 11g analytics. Reload the metadata files. Refresh the browser.
Now go to Weblogic-My Account- Make the Accessibility mode ON.

Now go to New - Analysis and select any of the subject areas - for ex - Testing subject area and a new page will open.

This is BI Composer. In the next article we will see how we can make use of this BI Composer to do the analysis.

Note : After you are done with your work with BI Composer and go back to Analytics and turn off the Accessibility mode otherwise next time when you login to analytics you will see limited option being present on the home page ( Administration link would have gone so be careful with that. )

Thank you and good luck.

Upgrade OBIEE 11.1.1.6.0 to 11.1.1.6.2 - Patchwork

2012-09-13T07:44:00.000-07:00

Hi friends
I recently upgraded OBIEE from 11.1.1.6.0 to 11.1.1.6.2. (Windows 7 - 32bits). I will share all my experience throughout this patch process.Here we start -

Download the below patches from the Oracle Support portal.

14223977 Patch 11.1.1.6.2 BP1 (1 of 7) Oracle Business Intelligence Installer
14226980 Patch 11.1.1.6.2 BP1 (2 of 7) Oracle Real Time Decisions
13960955 Patch 11.1.1.6.2 BP1 (3 of 7) Oracle Business Intelligence Publisher
14226993 Patch 11.1.1.6.2 BP1 (4 of 7) Oracle Business Intelligence ADF Components
14228505 Patch 11.1.1.6.2 BP1 (5 of 7) Enterprise Performance Management Components Installed from BI Installer 11.1.1.6.x
13867143 Patch 11.1.1.6.2 BP1 (6 of 7) Oracle Business Intelligence
14142868 Patch 11.1.1.6.2 BP1 (7 of 7) Oracle Business Intelligence Platform Client Installers and MapViewer
13952743 : JDeveloper Patch

Create a folder OBI_Upgrade on the desktop and put all the above patch files,
unzip all the patch files in that folder.You will find each folder being created for every patch file you unzipped.

Now move all the folders (14223977,14226980,13960955,14226993,14228505,13867143,14142868,13952743)
from OBI_Upgrade to C:\OBIEE11g\Oracle_BI1

Go to START->ALL PROGRAMS->ORACLE BUSINESS INTELLIGENCE->STOP BI SERVICES.

Go to C:\OBIEE11g\Oracle_BI1\bifoundation\web\catalogmanager\configuration and Remove the following Catalog Manager cache directories (if they exist):
* org.eclipse.osgi
* org.eclipse.equinox.app

Open the command prompt and set the variables.

set ORACLE_HOME=C:\OBIEE11g\Oracle_BI1
set PATH=%ORACLE_HOME%\bin;%PATH%
set JAVA_HOME=%ORACLE_HOME%\jdk
set PATH=%JAVA_HOME%\bin;%PATH%
set PATH=%ORACLE_HOME%\OPatch;%PATH%

Now one by one I have to apply the patches (Except 13952743)
(14223977,14226980,13960955,14226993,14228505,13867143,14142868)

Let us see the process for one patch 14223977. Path would be till the folder 14223977

The patch process would begin and when prompted you just have to type Y. Once the patch process completes it would display a message'Opatch Succeeded'

After that you have to go one folder back and enter into the new patch folder and execute the apply patch command as shown below.

You will have to repeat this process until you complete 13960955,14226993,14228505,13867143,14142868 (Except 13952743)

(Note: One of patches shown here is not required - I don't remember here but once you come across that one - the screen will display a proper message so there is no problem as such. You just have to finish applying all the patches.)

Once you done with all those patches,one important patch you will have to apply and that is JDeveloper 13952743 patch. For that you again have to set the variables. This time ORACLE_HOME would be different. Have a look at the variable path and you will easily understand the difference. Just before that copy the BIPublisherDesktop32.exe and BIPublisherDesktop64.exe files from the
C:/OBIEE11g/clients/bipublisher/repository/Tools directory
to the
C:/OBIEE11g/ /user_projects/domains/bifoundation_domain/config/bipublisher/repository/Tools directory.

Let us focus on Jdeveloper 13952743 patch now.

On the command prompt set the variables.

set ORACLE_HOME=C:\OBIEE11g\oracle_common
set PATH=%ORACLE_HOME%\bin;%PATH%
set JAVA_HOME=%ORACLE_HOME%\jdk
set PATH=%JAVA_HOME%\bin;%PATH%
set PATH=%ORACLE_HOME%\OPatch;%PATH%

and go to the folder 13952743 and apply the patch command

As usual when prompted enter Y.
Once the process completes you just have to start BI services.
When the patch process completed I started the BI services but I encountered few errors.
[ PLEASE NOTE THAT YOU MAY NOT RECEIVE SUCH ERRORS. THIS WAS HAPPED IN MY CASE SO IT DOES NOT MEAN THAT BELOW STEP IS A PART OF THIS UPGRADE PROCESS]

The error lines have words like Java.class...MBean ....Exception....
I went to
C:\OBI11g\user_projects\domains\bifoundation_domain\config\fmwconfig\servers\
AdminServer\dms_config.xmlAnd

Changed this entry:

to:

And started the BI services - Errors were gone and the login page could open. I reloaded the metadata files and removed the browser cache and temporary files. The Admin page then showed the latest version.

Thank you .
Good Luck.

Remove System Wide Default Change in OBIEE 11g

2012-08-30T00:22:00.000-07:00

Hello! Today we are gonna see if we can remove the system wide default change in OBIEE 11g.I am using OBI 11.1.1.6 on Windows 7 - 32 bits with IE8.

Have a look at the Testing Subject Area and the column CUST_ID.
Go to Column Properties and hide it. Save it as system wise default as shown in snap below.
Look at the column before/after modification.

Go to
C:\OBIEE11g\instances\instance1\bifoundation\OracleBIPresentationServicesComponent\coreapplication_obips1\catalog\SampleAppLite\root\system\metadata

You will find 2 files being created (.txt /.atr) with the subject area name Testing+Subject+Area

Just remember - System wide default file is subject area specific. It means if we do the system wide changes for columns belonging to Testing Subject Area then all those changes will be incorporated in the same file testing+subject+area. Similarly if we do the same for the columns in Sample Sales Lite subject area then those changes would be found in text file of sample+sales+lite as shown in the snap above.

Let's make change in one more column of Testing Subject Area system wide.
This time it is CUST_EMAIL. I have changed its column heading to Customer Email.

So if I open the text file testing+subject+area , I am sure that i would find those 2 changes. Let's see.

Take the backup of this file.

Now we are going to remove those 2 files - testing+subject+area (.txt and .atr) and restart the BI services.

Clear the browser cache,login to the system and reload the metadata files.

You will find that both the changes will have been restored back. See the snap.

I actually wanted to restore only 1 change ( visibility of column CUST_ID ) but it seems its not possible - You have to remove the entire file and so all the changes of that particular subject area would get restored. This is the limitation but no issues - I can now save the system wise default setting for the column CUST_EMAIL only.

Thanks - This was just an experiment. You may find some more and interesting results if you try to play around with this option. Good luck.

Change header background color - OBIEE 11g

2012-08-13T01:10:00.000-07:00

Change header background in OBIEE 11.1.1.6
Solution tested in Windows environment - IE8,Mozilla,Chrome.

Change the value of 2 nodes in common.css

->.HTMLBody{font-family:Tahoma,sans-serif;font-size:11px;background-color:#9f0b34!important;overflow:auto;margin:0;padding-left:5px;}
->.HeaderContainer{background-color:#9f0b34;font:11px Tahoma;position:relative;}

Common.css has to be updated from both the locations at shown below.

C:\OBIEE11g\user_projects\domains\bifoundation_domain\servers\AdminServer\tmp\_WL_user\analytics_11.1.1\silp1v\war\res\sk_blafp\b_mozilla_4\common.css
AND
C:\OBIEE11g\Oracle_BI1\bifoundation\web\app\res\sk_blafp\b_mozilla_4\common.css

You can download the modified version from
https://docs.google.com/open?id=0B_6YDYCkQgcfYjRmUFpfbVlQXzQ

Restart the BI services. Clear the browser cache.

Thank you.

Display report subtotal ONLY if multiple values are selected from the dashboard prompt

2012-08-10T01:11:00.002-07:00

Hi all - Good afternoon

Of late I attended one technical interview and was asked to solve one scenario. The scenario was :
" I have a dashboard prompt on one column say Country. I want to show the subtotal if i select more than one country in the prompt otherwise the report should not have subtotal " - I took my own time but could not answer the question :-(

Finally I decided to figure out this issue practically and found a solution. Enjoy.. I know you have started guessing the solution and might have found some pointers.

Create a simple report - Label it as NoSubtotal.

Create one report - Subtotal - Here we are subtotal option has been enabled in the table view.

Create one intermediate report - Prod Category Prompt report

Now we have to create a prompt on Product Category and give the reference of presentation variable A.

Now let us go to the dashboard editor and make use of section condition and intermediate report.

Save the changes and test the dashboard.

Thank you.
It might help some of you if by chance you come across with such type of scenario question in an interview process. :-) Good Luck.

Reduce time to open Login Page - OBIEE 11g

2012-08-06T01:38:00.000-07:00

Tried below option and 11g login page got opened little early than it usually takes.
( My environment : OBIEE 11.1.1.6 on Windows 7 - 32 bits)

Stop BI services.

Take back up (if necessary) of most recent log and keep them on local drive

DELETE LOG FILES from each of the sub folder inside :
C:\OBIEE11g\instances\instance1\diagnostics\logs

DELETE all the Log files in :
C:\OBIEE11g\user_projects\domains\bifoundation_domain\servers\AdminServer\logs

Start BI Services

Customize the title of the page : OBIEE 11g

2012-07-19T22:43:00.002-07:00

I am on Windows 7 -32 bits -OBIEE 11.1.1.6.0 and IE8.

Let us do the modification in common.css file. 2 Nodes ->

.HeaderTitleBarCaption{background-color:#FDF8F8;color:#F50F0F;font-size: 120%;
white-space:nowrap;max-width:600px;background-image:url(../l1_seltab_bg.gif);font-weight:bold;height:16px;padding:6px 1px;margin-bottom:-3px;overflow:hidden;text-overflow:ellipsis;max-width:400px;}

.HeaderTitleBarLeft img,.HeaderTitleBarRight img{vertical-align:top;display:none;}

Please find the modified version of common.css at :
https://docs.google.com/open?id=0B_6YDYCkQgcfaUVBaUo1WkZfZWM

As usual we have to keep this at 2 places.

C:\OBIEE11g\Oracle_BI1\bifoundation\web\app\res\sk_blafp\b_mozilla_4

AND

C:\OBIEE11g\user_projects\domains\bifoundation_domain\servers\AdminServer\tmp\_WL_user\analytics_11.1.1\silp1v\war\res\sk_blafp
\b_mozilla_4

Restart the OBI services. Clear the browser cache.

Few snapshots :

Thank you.

Selection of Reports through dashboard prompt - OBI 10g

2012-07-17T09:04:00.000-07:00

Hi All

The requirement "Selection of Reports through dashboard prompt" is nothing new and has been covered in many blog posts.
I happened to work upon the same in the recent past and documented my findings. I am gonna share with you.
Please download it from :

https://docs.google.com/document/d/1hvsjtlvsYk9ccZHLF0TVgleM-y_ZDBQ13TBtD-8DIUs/edit

Hope it may help some of you.

Best wishes.

Remove Reset Button - OBIEE 11.1.1.6.0

2012-07-15T05:50:00.000-07:00

Got a chance to do R&D after looking one question posted on OTN and it was - Whether we can remove Reset button ( reference dashboard prompt ). So let us see if we can do so. I am using OBIEE 11.1.1.6.0 on Windows 7 -32 bits. I will perform a test on IE8.

First of all have a look at the dashboard prompt and report. Look at the Reset button.

Now basically we are going to delete some code from the existing java script promptsruntime.js
This file is located at 2 places. Hence change should be made at both the places.

C:\OBIEE11g\Oracle_BI1\bifoundation\web\app\res\b_mozilla\prompts
AND
C:\OBIEE11g\user_projects\domains\bifoundation_domain\servers\AdminServer\tmp\_WL_user\analytics_11.1.1\silp1v\war\res\b_mozilla\prompts

I have provided the link from which you can download the modified promptsruntime.js
https://docs.google.com/open?id=0B_6YDYCkQgcfU21QY2lmNVdwSjA

After you put the modified file at 2 places shown above, restart all the services.Delete cookies,History,cache from the IE browser. Reload the server metadata and files once you logged in to the front end.

And look at the same report- Reset is gone. It is actually gone from all the dashboard prompts.

-- Updating-->

We also have to remove the "Show Reset Button" check box which you can see in the dashboard prompt properties (Edit Page Settings).

For that we have to modify one existing javascript named promptseditor.js Below link will gives you the modified javascript.
https://docs.google.com/open?id=0B_6YDYCkQgcfbnVKMTdOXzRpTTQ

Put this file at 2 places.

C:\OBIEE11g\Oracle_BI1\bifoundation\web\app\res\b_mozilla\prompts
AND
C:\OBIEE11g\user_projects\domains\bifoundation_domain\servers\AdminServer\tmp\_WL_user\analytics_11.1.1\silp1v\war\res\b_mozilla\prompts

Restart the services. Clear Browser cache. Reload metadata. Let us see the dashboard prompt editor and you will find the "Show Reset Button" checkbox would have gone.

All the best

Evaluate : Call Function

2012-07-05T03:54:00.002-07:00

Good afternoon ..
Have a look at the column JUNK.

It has spaces,special characters.
I would not want to see them - I want the plain text.
Let us write one function in oracle.

CREATE OR REPLACE FUNCTION RMV ( A VARCHAR2 )
RETURN VARCHAR2 is
B VARCHAR2(50);
BEGIN
SELECT regexp_replace((regexp_replace(A,'([[:punct:]])')),'[[:blank:]]') INTO B
FROM DUAL;
RETURN(B);
END;
/

Now I will call this function in the front end via EVALUATE function.

Let us keep both the columns side by side and we will see the difference.

The same effect can be achieved by nesting the evaluate function
( This is nothing but the expression we used in the function above )
Since we are using evaluate - we need to be very careful about spaces,single quotes and its syntax.

Have a look at -

Have a nice day.

Unable to apply IS BETWEEN Saved Filter : OBIEE 11.1.1.6

2012-06-23T07:36:00.001-07:00

Thanks to Mark.T who raised one doubt on OTN and made me to reproduce the issue.
I am on OBIEE 11.1.1.6 - Windows 7 - 32 bit - AND using IE8.

Look at the report - It is very simple - It has a filter - IS BETWEEN on a measure column.

Let us save this filter. Name it as BETWEEN filter.

Now REMOVE THIS SAVED FILTER and try to add the same from where you have saved it.

You will not able to see the SAVED filter "BETWEEN Filter" getting added to report neither you get any kind of response from the presentation server.

Ok - So let us find out out the workaround. Go to the Home page of OBIEE 11g and edit the Filter.

Simply select the check box option "Convert this filter to SQL"

SAVE the changes. Go back to the same report.
AND now try adding the same filter ...

Husshh..I can now add my saved filter.

(Note : I encountered this issue on IS BETWEEN filter and on IE browser - You may or may not find it with some other kinds of filters Or browsers -I do not know - Other kinds of filters behaved in a normal manner.)

Thank you for taking your time to read this article.

Good bye.

Grand Total Sorting in Descending order in Pivot view- OBIEE 11g

2012-06-21T07:33:00.001-07:00

NOTE - Do not forget the second part of this article as you will find the solution and not the workaround. Thanks to Renu Prasad Gurudev.

Hello friends - Good evening.
I came upon one requirement where Grand Total was to sort in descending order in pivot view.

Let us create a simple report.

Look at the last column - The Grand Total column ( Pivot view - Columns section - Total - After )
Grand Total is not in order. Neither in ascending nor in descending order.
There is no option to sort - Sort arrows are missing.

Let us try some other workarounds.

Create a report as shown below. In the criteria - 2 extra columns
1) Grand Total By Product Catagory
2)Rank on Grand Total By Product Catagory

Now I am placing the Rank column the ROWS section of the Pivot view.

From the pivot view I am going to remove AMOUNT_SOLD_TOTAL column from the measures.

And I am selecting the Grand Total- After option from Column Section of the view.

No luck - The moment you bring Amount_Sold_Rank column in the ROWS section - it losses its order and you DO NOT find the arrows either. So this workaround is not working.

Let us try another workaround.
Ok.
In my case the Product Categories are less in number and hence I can manually give a rank to them.
Have a look at report below.

From the AMOUNT_SOLD_TOTAL shown in the reports above I came to know which Product Category has the highest Total and which one has the lowest - I have noted it down and accordingly I will be ranking them.

Go to Pivot view. Place the PRODUCT_CATEGORY_RANK in the ROWS section and HIDE it.

In my case AMOUNT_SOLD_TOTAL does not change overnight for a particular product category. Definitely I will have to monitor the AMOUNT_SOLD_TOTAL on a weekly basis and may have to modify the hard coded rank column AMOUNT_SOLD_RANK.

This is something more of a manual work but at the moment it has solved my purpose.

-----------------------------------------------------------------------------------------------------------------------

Part II : The logic what I have provided in the post shown above is a workaround and it has been corrected by RENU PRASAD GURUDEV and as per the steps he has provided to me - I am correcting my logic. Thanks to him. This would be useful for future reference.

Create the report as shown above -

In the report we have manually created a grand total column AMOUNT_SOLD_TOTAL and do not forget to select the check box "Treat as an attribute column"

Open the report in PIVOT view - and place the column AMOUNT_SOLD_TOTAL IN THE ROWS SECTION. SORT THIS COLUMN IN DESCENDING ORDER. AFTER THAT HIDE THAT COLUMN. IN THE COLUMN SECTION WE HAVE PLACED YEAR COLUMN AND APPLIED GRAND TOTAL - AFTER OPTION.

Thank you.

XML Search And Replace : Useful option

2012-06-13T03:25:00.001-07:00

HI ALL - I am sure that most of you must have already used the feature XML Search and Replace. I happened to use it recently and found very useful.

I had a subject area "SH1" and many reports were built around it as you can see below.

I was asked to change the name of the subject area SH1 to "Testing Subject Area" and so I went to RPD and renamed the SH1 folder.

And I opened the front end ( Reloaded metadata) and found the subject area name being changed to Testing Subject Area in the Analysis section.

Now I tried to edit the reports which were built on Testing Subject Area ( - The new name of SH1 )

Have a look at the warning : Either you do not have permission to use the subject area SH1 within Answers, or the subject area does not exist

I opened one of the reports shown above and went to Advance tab. Let us see the XML code.

So to resolve this issue - I went to Catalog Manager in Offline Mode and selected the option
"XML Search/Replace" from the Tools.
I replaced
subjectArea=""SH1""

BY
subjectArea=""Testing Subject Area""

Say OK.
It replaced and showed what all the items were changed.

Say OK.
Close the catalog manager. Stop all the BI services and start them again.
Clear the RPD cache, Browser cache. Reload the metadata in the front end.

Now try to click the Edit button of any of those reports from the Home Page and you will get to see the renamed subject area name.

Done !!!

Thank you.

OBIEE 11g : coreapplication_obisch1 is down

2012-06-12T21:34:00.001-07:00

I have been working on OBIEE 11.1.1.6 since quite a long time and all of a sudden some day something went wrong and I could not log in to OBIEE front end. Front end said "Invalid User Name or Password"

I just looked at the status of the services and found that scheduler (coreapplication_obisch1) was down.

Looked at the Scheduler and server logs.

Tried to connect to database : First with schema SH user and then with System user.

Oracle was not available. So used the startup command.

Restarted the BI services and found scheduler was up.

And I could log in to front end.

Note : There could be many other reasons that could bring down the scheduler - In my case I found that Oracle was not available from the log files and hence the 'startup database' solved the issue.

Thank for taking your time to read this post. I hope it may be useful when your scheduler gets down and your oracle is not available. :-)

Multiple column values in a single column

2012-05-31T02:53:00.001-07:00

This exercise has been carried out on OBIEE 11.1.1.6

Let us start with the SH subject area. We would be combining Calendar_Year,Product_category and Customer_City_Id in a single column with a fact column Amount Sold.

Create an analysis with 2 columns Calendar Year and Amount Sold as shown below.

Go to Advanced tab and copy the SQL.

Do not save the report. Open/Create a new analysis with 2 columns.

Go to the Advanced tab and click on the button 'New Analysis'

Now simply paste the SQL from the earlier analysis.

First of all modify this sql as shown below. Look carefully the above and below snap and the sql.

And press OK.

Observe that Year and Product category values in the same column.
Now again do not save the report, go to Advanced tab and copy the SQL.

Create a new analysis with 2 columns Customer City and Amount Sold.

Go to Advanced tab and click on the button New analysis.

Now paste the sql from the earlier analysis.

Modify the SQL - Observe 2 snaps ( above one and the below )

Press OK.

Now here you can rename the first column.

Done !!!

Thank you.

Add Custom Calculated Item - Useful option

2012-05-23T01:23:00.000-07:00

This is one more useful option in OBIEE 11g

Look at the Years.

Look at the Years and Amount Sold.

There is no Amount for the Year 2002 but client wants zero to be displayed for that year.
Now this is where the option Add custom Calculated Item comes handy.
This enables us to display zero and that too at front end level.

Right click on Year 2001 and follow the steps as shown below - Click on Add Custom Calculated Item

Label is 2002 and value is 0.

Table and graph -

Create Compound Layout - Useful option

2012-05-22T01:39:00.000-07:00

The exercise is based on an option which is nothing very new to OBIEE 11g but found very useful while working on one kind of requirement.

On the answers page of one specific subject area - we actually wanted to have many views with different columns - we managed to show them as a group on the dashboard.

The option Create Compound Layout is very useful.

I created 3 views and renamed them. All the 3 views have different columns of the same subject area.

If you put this report on the dashboard - only one view will be visible -which is the obvious behavior.

So from the Home page of OBI - go to the Edit tab of this report and copy the URL.
Now on the dashboard properties - Add the Embedded Object and paste this URL - Increase the frame size so that this will look like -

Thanks.

Content Delivery of Agent - Fuzzy behavior of pivot / table view

2012-05-11T07:22:00.001-07:00

I am using
Platform:OBIEE 11.1.1.6 on windows 7- 32 bit
Microsoft Outlook : 2010
Browser:IE8

I was trying to deliver a simple report ( Pivot view + Gauge view ) directly to my outlook mail account without selecting any kind of attachment option.

Now just have a look at the Front end report first.

Now look at the outlook mail snapshot. Observe the formatting of pivot view. It is an incomplete image.

(In gmail - Pivot/table view gets converted to plain text view
In yahoomail - Pivot/table view is no more present)

After playing around with the tool - I found one workaround.
I went to pivot table view properties and changed the border type to "ALL" - as shown below -

Save the changes and run the agent again.
Now look at the Outlook mail.Pivot view is complete.

Pivot view formatting is now fine.

Thanks.

NOTE : The Pivot/Table view display behavior is not normal in gmail, yahoomail and outlook and a bug has been registered.
<Bug : 14078840><TABLE/PIVOT VIEW AGENT NOT PROPERLY SHOWN UP IN OUTLOOK MAIL>

Display city names horizontally

2012-05-10T06:39:00.000-07:00

Looks like I wasted a big time in displaying the city names (horizontally) in a single cell separated by space in my earlier post. The following way is bit easier.

Take a column city in your workbook and open the narrative view.
Select HTML - Use this code for ex: <a>&nbsp@1&nbsp</a>

Note : In the Rows to display option I have selected 100 - But in order to show all the city names, you have to increase the parameter value until you see all the names in the output below.

Let us rename the narrative view ( new feature in OBIEE 11.1.1.6) and change the font color.

Remove extra views from the answer. Look at the result.

Thanks.