Sunday 6 November 2016

OBIEE Prompt: Month Year prompt values in ASC order by year and month



We have a period name as a combination of Month and Year and using this in dashboard prompt,value gets sorted alphabetically by month name as shown below.



requirement is to sort this basis on year and month ,we can achieve this by using SQL

Edit Dashboard Prompt->go to options ->select SQL results  and enter following SQL

 SELECT UPPER(MONTHNAME("Domain Change"."Initiation Date"))||'-'||SUBSTRING(CAST(YEAR("Domain Change"."Initiation Date") AS CHAR) FROM 3 FOR 2), SUBSTRING(cast(YEAR("Domain Change"."Initiation Date") as char) FROM 3 FOR 2),MONTH("Domain Change"."Initiation Date") FROM "Human Resources - Workforce Profile" FETCH FIRST 65001 ROWS ONLY Order by 2,3

Note:  I used Upper & Substring functions because my Period column format is in MON-YY format.Basis on your format you can modify your SQL.


With this change, values in prompt are displayed in order by year and month as shown below.

 
at

4 comments:

Subscribe to: Post Comments (Atom)

A fatal error occurred while processing the request. The server responded with: FOProcessor cannot run without a valid TMP directory. Please check if TMP directory exist and write enabled.

When attempting to export a Dashboard to Excel the following error message in sawlog is found: A fatal error occurred while processi...