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

PD001

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"'.

Gurujothi D

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

VipinSharma

Hello PD001,

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

VS

VipinSharma

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