Unable to apply IS BETWEEN Saved Filter : OBIEE 11.1.1.6

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

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

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

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. :-)