Oracle Business Intelligence

Products Banner

How to add date parameter in MON-YY format in dashboard in oracle fusion?

Accepted answer
56
Views
3
Comments

How to add date parameter in MON-YY format in dashboard in oracle fusion? We are taking date from the below SQL query which is having 'mm/dd/yyyy' format

'SELECT
"General Ledger - Period Status Real Time"."Period Statuses"."Start Date"
FROM "General Ledger - Period Status Real Time"'.

Best Answer

  • Gurujothi D
    Gurujothi D ✭✭✭
    Answer ✓

    Hello,

    Below is the working example which will return 04/01/2024 as APR-24 and 12th period as MAR-25. Update this as per your configuration.

    SELECT concat(case when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=01 then 'APR'
    when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=02 then 'MAY'
    when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=03 then 'JUN'
    when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=04 then 'JUL'
    when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=05 then 'AUG'
    when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=06 then 'SEP'
    when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=07 then 'OCT'
    when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=08 then 'NOV'
    when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=09 then 'DEC'
    when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=10 then 'JAN'
    when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=11 then 'FEB'
    when CAST(Monthname("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as char(2))=12 then 'MAR'

    			end,
    

    concat('-',SUBSTRING(CAST(Year("General Ledger - Period Status Real Time"."Period Statuses"."Start Date") as CHAR(4)) FROM 3 FOR 2))) FROM "General Ledger - Period Status Real Time"

    I hope this is helpful.

    Regards,
    Guru

Answers

  • Hello PD001,

    Tried doing it slightly differently. Hope this is also helpful.

    VS

  • Hello I am not able to add the image.. so I will try to explain in writing.

    1. Create a column prompt.
    2. Prompt for column - change the Fx to have following formula

    concat(CAST(Year("Orchestration Orders Details"."Creation Date") as CHAR(4)),
    concat('-',CAST(Monthname("Orchestration Orders Details"."Creation Date") as char(2))))

    Check your results in the choice list.

    VSS