Gurus,
I have a multi-select date prompt with parameter name(Prompt_Date) that allow user to select multiple dates in the prompt. I want to show the prompt result separated by comma in the report. For example: 10/15/2016, 10/29/2016
I insert a text item and use ParamValue('Prompt_Date') as the report expression. However, it only works for single date. When I select more than one date and run it, it shows me ora-01821: date format not recognized error. Please let me know what's the best way to handle this.
TIA..
Quote from: coolite on 04 Jan 2017 02:45:10 PM
Gurus,
I have a multi-select date prompt with parameter name(Prompt_Date) that allow user to select multiple dates in the prompt. I want to show the prompt result separated by comma in the report. For example: 10/15/2016, 10/29/2016
I insert a text item and use ParamValue('Prompt_Date') as the report expression. However, it only works for single date. When I select more than one date and run it, it shows me ora-01821: date format not recognized error. Please let me know what's the best way to handle this.
TIA..
Hi,
Instead of using a text item and a report expression, try using a layout calculation with the expression ParamDisplayValue('Prompt_Date')
I just tried this with the sample GO Sales (query) package, and it works fine for me. As a quick sanity check, have you defined your filter using an 'in' operator?
Cheers!
MF.
MF,
I used the layout calculations and I got the same error message..
The report is built from my own SQL. Below is what I have in the where clause of the SQL:
p_date IN TO_DATE(#PROMPTMANY('Prompt_Date')#,'YYYY-MM-DD')
Thanks,
Bo
Quote from: coolite on 05 Jan 2017 08:49:42 AM
MF,
I used the layout calculations and I got the same error message..
The report is built from my own SQL. Below is what I have in the where clause of the SQL:
p_date IN TO_DATE(#PROMPTMANY('Prompt_Date')#,'YYYY-MM-DD')
Thanks,
Bo
Why have you hard-coded SQL in your report? One of the many drawbacks of doing this is that "normal" things don't work, or work easily. To give you an analogy, what you are doing is like buying a motorcycle and pushing it to get to every destination. Then asking how you can reinforce the soles of your shoes so they don't wear out quickly. My advice is to spend a little time building a robust metadata framework and use this for your reports instead of hand-coding SQL.
MF.
MF,
This is a custom report we tried to build to skip the securities. Most of our reports are built using FM.. This is the rare exception.
After I played with it, I found out the problem is with the promptmany macro. It returned semicolon separated string like '2017-01-08; 2017-03-08'. However, in the sql, I need the string to be '2017-01-08', '2017-03-08'. The single quote is a special character and I'm not able to add it near colon even after I doubled the single quote to escape it. Below is what I have tried and the result in the sql.
#sq(substitute(',', ''', ''', join(',',split(';', PROMPTMANY('Prompt_Date', 'date')))))#
TO_CHAR (F.P_DATE, 'YYYY-MM-DD') IN ('2017-01-08'','' 2017-01-05')
#sq(substitute(';', ''', ''', PROMPTMANY('Prompt_Date', 'date')))#
TO_CHAR (F.P_DATE, 'YYYY-MM-DD') IN ('2017-01-09, 2017-01-02')
(#sq(join('CHR(39),CHR(39)',split(';', PROMPTMANY('Prompt_Date', 'date'))))#)
TO_CHAR (F.P_DATE, 'YYYY-MM-DD') IN ('2017-01-08, 2017-03-08')
#sq(csv(split(';', PROMPTMANY('Prompt_Date', 'date'))))#
TO_CHAR (F.P_DATE, 'YYYY-MM-DD') IN ('''2017-01-08, 2017-01-07''')
Thanks..
I've figured it out by using number instead of date to get around single quote. :)
(#join('',split('-', join(',',split(';', PROMPTMANY('Prompt_Date', 'date')))))#)
to_number(to_char( F.P_DATE, 'YYYYMMDD')) IN (20170110, 20170111)
So much work to quickly bypass the holy Framework... :P