Home » Fusion Middleware & Colab Suite » Business Intelligence » Case Statement Month Rank
icon7.gif  Case Statement Month Rank [message #639318] Mon, 06 July 2015 03:28 Go to next message
chungiemo
Messages: 1
Registered: July 2015
Location: Scotland
Junior Member
Hi folks I am a newbie in OBIEE,and seeking help from the experts.

I was looking to rank my months e.g. Apr 2014 - Mar 2015, and display this in a cross tab by its Rank in my report.

In my mind I would do a case statement on a Column Rank, and then exclude the rank in my results.

But having problems with the the syntax or am doing this completely wrong......

I had proceeded to dupilcate the Start Date, and called it "Rank" with the formula case statement:-

CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "april" THEN "Attendance"."Rank" = 1
CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "may" THEN "Attendance"."Rank" = 2
CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "june" THEN "Attendance"."Rank" = 3
CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "july" THEN "Attendance"."Rank" = 4
CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "august" THEN "Attendance"."Rank" = 5
CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "september" THEN "Attendance"."Rank" = 6
CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "october" THEN "Attendance"."Rank" = 7
CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "novermber" THEN "Attendance"."Rank" = 8
CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "december" THEN "Attendance"."Rank" = 9
CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "january" THEN "Attendance"."Rank" = 10
CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "febuary" THEN "Attendance"."Rank" = 11
CASE WHEN MONTHNAME ("- Attendance"."Start Date") = "march" THEN "Attendance"."Rank" = 12

It displays "Invalid Alias Format : Table_name.column required".

Any assistance would be greatly appreciated.

Regards

Man
Re: Case Statement Month Rank [message #639320 is a reply to message #639318] Mon, 06 July 2015 04:10 Go to previous messageGo to next message
Littlefoot
Messages: 21263
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
I don't use OBIEE, but - generally speaking - you are looking for a correct ORDER BY clause. Here's an example, see if you can adjust it accordingly to your needs.

I'm selecting employees from Scott's EMP table, displaying different HIREDATE column values, as returned by different TO_CHAR function parameters (i.e. format masks).
- HIRE_MM - two characters, but numeric representation of a month
- HIRE_MONTH_CRO - month name in my default language (Croatian)
- HIRE_MONTH_ENG - month name in English

I prefer using numbers than characters because NLS settings might change, and query - which was working just fine - suddenly returns incorrect results. That's why I used 'MM' format mask in my ORDER BY clause.

As I said: have a look, pick an option you find the most suitable (if any).

SQL>   SELECT ename,
  2           TO_CHAR (hiredate, 'dd.mm.yyyy') hire_date,
  3           TO_CHAR (hiredate, 'mm') hire_mm,
  4           TO_CHAR (hiredate, 'month') hire_month_cro,
  5           TO_CHAR (hiredate, 'month', 'nls_date_language = english')
  6              hire_month_eng
  7      FROM emp
  8  ORDER BY CASE
  9              WHEN TO_CHAR (hiredate, 'mm') = '04' THEN 1
 10              WHEN TO_CHAR (hiredate, 'mm') = '05' THEN 2
 11              WHEN TO_CHAR (hiredate, 'mm') = '06' THEN 3
 12              WHEN TO_CHAR (hiredate, 'mm') = '07' THEN 4
 13              WHEN TO_CHAR (hiredate, 'mm') = '08' THEN 5
 14              WHEN TO_CHAR (hiredate, 'mm') = '09' THEN 6
 15              WHEN TO_CHAR (hiredate, 'mm') = '10' THEN 7
 16              WHEN TO_CHAR (hiredate, 'mm') = '11' THEN 8
 17              WHEN TO_CHAR (hiredate, 'mm') = '12' THEN 9
 18              WHEN TO_CHAR (hiredate, 'mm') = '01' THEN 10
 19              WHEN TO_CHAR (hiredate, 'mm') = '02' THEN 11
 20              WHEN TO_CHAR (hiredate, 'mm') = '03' THEN 12
 21           END;

ENAME      HIRE_DATE  HI HIRE_MON HIRE_MONT
---------- ---------- -- -------- ---------
JONES      02.04.1981 04 travanj  april
BLAKE      01.05.1981 05 svibanj  may
CLARK      09.06.1981 06 lipanj   june
TURNER     08.09.1981 09 rujan    september
MARTIN     28.09.1981 09 rujan    september
KING       17.11.1981 11 studeni  november
MILLER     23.12.1982 12 prosinac december
SCOTT      09.12.1982 12 prosinac december
JAMES      03.12.1981 12 prosinac december
FORD       03.12.1981 12 prosinac december
SMITH      17.12.1980 12 prosinac december
ADAMS      12.01.1983 01 siječanj january
WARD       22.02.1981 02 veljača  february
ALLEN      20.02.1981 02 veljača  february

14 rows selected.

SQL>

[Updated on: Mon, 06 July 2015 04:11]

Report message to a moderator

Re: Case Statement Month Rank [message #639323 is a reply to message #639318] Mon, 06 July 2015 04:54 Go to previous message
Lalit Kumar B
Messages: 3123
Registered: May 2013
Location: World Wide on the Web
Senior Member
chungiemo wrote on Mon, 06 July 2015 13:58

I was looking to rank my months e.g. Apr 2014 - Mar 2015, and display this in a cross tab by its Rank in my report.

In my mind I would do a case statement on a Column Rank, and then exclude the rank in my results.


You need to use the "edit formula" and add the CASE function. The are two types available:

- CASE(switch)
- CASE(if)

Edit your analysis, go to formula, click on Fx, select CASE. Have a look at http://docs.oracle.com/cd/E12096_01/books/admintool/admintool_SQL8.html

By the way, if you want the months to be always associated with a rank, then you could do it in the BMM(Business model and mapping) using a logical column. Read http://docs.oracle.com/cd/E23943_01/bi.1111/e10540/busmodlayer.htm#BIEMG625
Previous Topic: Oracle Data Integrator, Customised Delete Operation
Next Topic: XML Publisher Excel Template design
Goto Forum:
  


Current Time: Mon Dec 11 01:04:55 CST 2017

Total time taken to generate the page: 0.09398 seconds