Invalid format for prompt

[chinnucognos]

Hi everyone,

I have been stuck in one of the issue related to value prompts.
column/use value in value prompt:TO_CHAR([TRANSACTION_DATE],'YYYY - Month')
ater running the report... in prompt page i am gettting values as expected but after selecting the values i.e 2014-Feb
getiing an error:

QFS-ERR-0139

      The request has multiple errors.
RQP-DEF-0354 The query contains one or more unresolved prompts.QE-DEF-0381 Invalid format for prompt 'Transaction Month'. Expected format for date: CCYY-MM-DD

Can you please help what is cause for this....

[Francis aka khayman]

is your data (i.e. values in Time Dimension) of the same format? looks like your prompt is changed format

[chinnucognos]

its a data type of "date time"

[Francis aka khayman]

i mean time format...

if your prompt is YYYY-Month... of course your value should be of the same format....

from the information you provided it seems your prompt is YYYY-Month while your data is CCYY-MM-DD

[chinnucognos]

Thanks Khayam...
What you said is correct...now  i have removed to_char 'YYYY-month' and went to properties tab, here i changed data format as per required but when i changed this.... values are coming like this "2014-Feb,2014-Feb,2014-Feb...2013-December,2013-December..
this is becaz of 2014-Feb has three date values i.e 2014-Feb-21,2014-Feb-28 and vice versa ...etc...
But actual valus should be 2014-March,
                                      2014-February,
                                        2014-January,
                                        2013December
                                              .
                                              .
Pls let me know how to go further

[Francis aka khayman]

the problem you stated in this thread is the error which is now solved.

you never stated what you are trying to do so i cannot tell you how to proceed.

[Lynn]

So you want to prompt the user to select a month and then filter your data for any date within the month, right?

As you discovered you can't use a filter that directly compares a character presentation of YYYY-Feb with a date/time data type. I would suggest you revise your prompt query to return the distinct list of months, and then alter your filter to translate the selected month to the first and last day of the month for filter purposes. That will be easier to do if the "use" value of your prompt uses month numbers such as 201402 rather than name 2014-Feb, but you can still have the "display" value as the month name if you like.

You can parse out the elements of your "use" value to month and year and use the _make_timestamp function to get the first day of the month in date/time format. Then use that first day of month expression within the _last_of_month function. Your transaction date should fall between those two elements. You may need to tweak for the time component depending on your time values. The time component of _make_timestamp defaults to 00:00:00.000 which may not be appropriate if you have actual times recorded.

I'm not sure building a prompt query off your transaction table is the best idea from a performance standpoint. If you have a calendar dimension to use as an alternative that might be a better approach.