So today i am here to discuss the scenarios of CAST function in OBIEE11g or 12c both. We always see that we always run behind using CAST function for conversion to Char/Int/Date etc. But issue appears when we have to get a diff format in Date functions.
For example, i had a scenario where user wanted to see report in pivot form, with Prompt Date range as 2017 / 01 to 2017 / 04.
I tried getting above values in Prompt by using Convert Calendar date to Char using Cast function. But when i have to pass this prompt value to my report, it was not working as expected. If i select Date range from 2017 / 01 to 2017 / 04, it use to show only data in report for 2017 / 01 , 2017 / 02, 2017 / 03. It use to omit 2017 / 04.
To resolve this issue, we used a simple logic in Report Prompt, as below. This gives us the output in little different format , but still no CAST is used,so no conversion to Char and then having issues passing value.
Using below idea, we are now able to get output in proper format and it passes correctly to report and filters it.
Output shows below:
Follow for more 🙂