Hi All,
I have a dimension “Completed Date”, data type Date, its SQL:
to_date(ABC.COMPLDATE,‘j’)
I created a filter for it with the formula, called it MyCode to indicate below: @Select(path\Completed Date) LIKE @Prompt(‘Completed Date’,‘D’,‘path\Completed Date’,Mono,Free,Not_Persistent,{‘ALL’}) OR ‘ALL’ IN @Prompt(‘Completed Date’,‘D’,‘path\Completed Date’,Mono,Free,Not_Persistent,{‘ALL’})
when i validate the formula, I got the error “ORA-00920: invalid relational operator”. I do not configure out the reason why.
Moreover, I would like to enhance the filter with the requirement:“Select ALL by default and prompt user to select between Completion Start Date and Completion End Date from the list of values”. I think I should use BETWEEN … AND. @Select(path\Completed Date) BETWEEN MyCodeForStartDate AND MyCodeForEndDate
Help me please! Any suggestion would be appreciated.
I found that the error come from the default value of @prompt. It should not {‘ALL’} because I was using @prompt with the datatype D (Date). However, the requirement is “Select “ALL” (all dates) by default”, Is there anybody know how to achieve it?
Best option is to create Optional Prompt at WEBI/Report Level so that by default it will run for all date values.
Else calculate Min Date and Max Date of Completed Date and Try something like below (below code may not be 100% accurate but try something along those lines)
Completed Date Between
(case when @Prompt(‘Completed Date’,‘D’,‘path\Completed Date’,Mono,Free,Not_Persistent) = ‘1900-01-01’ then Min Date else @Prompt(‘Completed Date’,‘D’,‘path\Completed Date’,Mono,Free,Not_Persistent) end)
AND
(case when @Prompt(‘Completed Date’,‘D’,‘path\Completed Date’,Mono,Free,Not_Persistent) = ‘1900-01-01’ then Max Date else @Prompt(‘Completed Date’,‘D’,‘path\Completed Date’,Mono,Free,Not_Persistent) end)