It's a bit more than what Joel Acha posted as simply with presentation variable you will have an error as it isn't a date (and you know it as you wrote it).
So your challenge is not about the filter itself but just how to get a date out from a varchar presentation variable.
There are many ways to do it, you can of course do it by some CASE WHEN parsing the varchar and matching January = 1, February = 2 etc. and the year.
If your higher granularity is the month level (you don't have days in your model) I hope you also have a real date (first day of the month or last of the month is the most common) in your time dimension at the same level of your month column. In that case you add a filter on your varchar month column but use the real date column in your filter: the prompted value filter the varchar value which will fix the value of the real date column for your month.
Another option can be convert your text into a date directly by using TO_DATETIME.
This function acts a bit like TO_DATE in Oracle DB, the syntax is:
So you can try to match the format string to read your own varchar date (you can have issues with languages if you have multiple languages and things like that but you can always give it a try ...).
I don't want to be pessimistic but ... CAST of that kind of varchar to date using "CAST" will give you something like this:
(the formula is CAST('December 2014' as DATE) on SampleApp406)
CAST isn't good for these things, and I'm also scared that "December 2014" can't be matched by To_DateTime neither.
It's probably the worst possible format for a date ("Dec 2014" or "2014.12" work well with To_DateTime for example).
And I'm almost sure that in your time dimension you have a column working find with To_DateTime (you must have something like 2014-12 or 2014.12 or just "month number", and with "year" you can concatenate, to sort your months or April 2014 will be the first instead of January 2014).
Something along the lines of:
should work. As long as the presentation variable is being populated by an actual date column in the dashboard prompt, then you're guaranteed to get a "string" value in the right date format. I have used this approach before and it does seem to work.
Yes, something in that format (numeric-like year, month, day) is easy to convert to a date, it's just the full text version the OP posted ("December 2014") which is a nightmare and must be banner (except if thanks to a proper time dimension next to it there is a nice formatted column with a "normal" date or something close to it).
For both @Gianni Ceresa and @JayZee I would say thank you for sharing idea and solution,,I will try it first..thansk a million guys
Sure @Gianni Ceresa
I would suggest to create hidden prompt with variable prompt and set data type as date and time.
this variable value can be refreshed from time table based on user selection.
refer http://docs.oracle.com/cd/E21764_01/bi.1111/e10544/prompts.htm#BIEUG1171 for more detail on hidden prompt.
to @Gianni Ceresa I did as what you said like "And I'm almost sure that in your time dimension you have a column working find with To_DateTime (you must have something like 2014-12 or 2014.12 or just "month number", and with "year" you can concatenate, to sort your months or April 2014 will be the first instead of January 2014)" and the result was working out that I used a column from time dimension like 2014-12 but in dasboard prompt it seems not what User want actually because User want something like "December 2014" when they want to choose Month. is it possible that in dashbord prompt will display "December 2014" but the value kept in presetation variable is like "2014-12"?
Thanks all for sharing ideas, solutions and advices.with respectfully.