Counting record and sorting with fiscal month in APEX chart

Recently in one of my project that built in Oracle APEX. This is basically a Change Request System (CRS) built in APEX to submit or log any change request by system users for other corporate systems. It was working quite well with the notification and reporting. When user submit a new request I get an email notification and then I access to the CRS to review and action the request. User who originally posted the request get notified every time I do changes in the request log and get update about the progress of the request.

I thought it will be good to have some sort of summary report with chart to see how many calls logged each month and how much change we do in the system. Started to create a new page with chart and scripted my simple query in TOAD first to make sure the script written correctly. Here is my initial script to implement chart –

-- To count records for each month  
SELECT NULL Link, TO_CHAR (create_date, 'MON/YY') AS "Month", COUNT (*) AS "No of records"
    FROM RT_RFC
GROUP BY TO_CHAR (create_date, 'MON/YY')
ORDER BY TO_CHAR (create_date, 'MON/YY')

The query works well in TOAD and I have carried over the script to my APEX page. Here is what I get output from my chart.

Ops! I did not realize that the chart was not rendering months according to fiscal year instead it was rendering alphanumerically. Actually it was doing the same in TOAD but I did not notice in first instance. This is not going to help and I can see why it was sorting alphanumerically because I was converting date to char (string) :(.

I have tried different approach using trunc() and it works fine APEX standard report however I had set the column format to month in the report column heading to ‘Month’ . Here is the truncate statement –

 SELECT NULL link, TRUNC (create_date, 'MONTH') AS "Month", COUNT (*)
    FROM rtt_rfc
GROUP BY TRUNC (create_date, 'MONTH')

When I have implemented this code in Chat control it was displaying ‘1-MAY-2010’ which is what I do not want to display like. I want to display only Month name. Here is what I got with this script –

As you can see I have change the rotation to display the month in 45 degree rotation but unfortunately I could not find any way to format the month field to display only month. I had to think for alternate solution.

Ok, I suddenly thought how about if I use case statement to achieve this. Here is what I have done –

SELECT NULL link,
         CASE
            WHEN TO_CHAR (create_date, 'MM') = 1 THEN 'JAN'
            WHEN TO_CHAR (create_date, 'MM') = 2 THEN 'FEB'
            WHEN TO_CHAR (create_date, 'MM') = 3 THEN 'MAR'
            WHEN TO_CHAR (create_date, 'MM') = 4 THEN 'APR'
            WHEN TO_CHAR (create_date, 'MM') = 5 THEN 'MAY'
            WHEN TO_CHAR (create_date, 'MM') = 6 THEN 'JUN'
            WHEN TO_CHAR (create_date, 'MM') = 7 THEN 'JUL'
            WHEN TO_CHAR (create_date, 'MM') = 8 THEN 'AUG'
            WHEN TO_CHAR (create_date, 'MM') = 9 THEN 'SEP'
            WHEN TO_CHAR (create_date, 'MM') = 10 THEN 'OCT'
            WHEN TO_CHAR (create_date, 'MM') = 11 THEN 'NOV'
            WHEN TO_CHAR (create_date, 'MM') = 12 THEN 'DEC'
         END
            "MONTH",
         COUNT (*) AS "No of Request"
    FROM rtt_rfc
GROUP BY TO_CHAR (create_date, 'MM')
ORDER BY TO_CHAR (create_date, 'MM')

Hmm, it is looking good in TOAD when ran the query and doing exactly what I wanted to do. Now I took the query into the chart report. Here is what I got –

Wow! it is working perfect now. Hope this will help someone to play around with date field.