Top N analysis

I had a requirement to display the number of orders each state is making in a specific time period. This report should show only Top 5 states plus one record for all orders from rest of states. 

Though there are several ways of doing this, but I did using combined with similar request.

1) Create a request which is having states and orders count. Apply filter on "Orders count" where rank(orders) is greater than 5.  Save the request under shared folders in some name.

2) Create another request with necessary columns needed and apply filter on "Orders count" such that it will display only the Top 5 states. Add one dummy column to the report and hard code value ‘1’.  I will use this dummy column for sorting purpose later

3) Now select combined with similar request option, create the request like as earlier mentioned and apply filter on state, Using Advanced -> "filter based on result of another request" and point to the first request (item 1) using the Browse. Add one blank column to the report and hard code value ‘2’ for sorting purpose and edit column formula of reporting state as follows;

case when CUSTOMER.STATE = 'X' then 'Y' Else 'OTH' end 

(Make sure you really don't have a valid state_cde as 'X' now or in future, otherwise results may go wrong)

4) Sort the report using the dummy column

5) Finally hide the dummy column using column properties -> Column format -> Hide

 

Avoid Null in OBIEE dashboard prompts

It is always possible that NULL or white spaces appear in the dimensional attributes.  If we create a prompt with control type "multi-select" for that dimension, we will see blanks appearing in the list of values.  My customer said for any prompt they don't want to see NULL appearing, instead they want to see some text, something like, say "NULL VALUE" .

How to do this?

Add the below in Show "SQL Results" of the prompt

SELECT CASE WHEN TRIM(TABLE."COL NAME") IS NULL THEN 'NULL VALUE' ELSE TABLE."COL NAME" END FROM SUBJECT AREA

TRIM function in the above SQL will convert white spaces to NULL which is then decoded to 'NULL VALUE' using the CASE statement