BusinessObjects Board

Issue with pre-defined filter at Universe

building semantic layer
#1

Hi,

I am unable to work out the below code. I am trying to prompt the user with a list of date values and passing them to decode and if user selects ‘’ and passing '=’*’ in the where clause of the oracle so that it would retrieve all the records. But its throwing ORA-00907; missing right parenthesis error. Can someone please help and suggest alternative solution ?

(case when decode(@Prompt(‘Select timeframe for which to run the report:’, ‘A’, {‘Current Month’, ‘Last Month’,’’}, MONO, CONSTRAINED,persistent,{’’},User:0)
, ‘’, '’
, ‘Current Month’, to_char((select sysdate from sys.dual@catsprd), ‘YYYYMM’) || ‘01’
, ‘Last Month’, to_char(add_months((select sysdate from sys.dual@catsprd), -1), ‘YYYYMM’) || ‘01’

       ) = '*'
  then '''*''=''*'''

else
(
to_char(INCIDENT_DETAILS.INCIDENT_DATE, ‘yyyymmdd’) >= decode(@Prompt(‘Select timeframe for which to run the report:’, ‘A’, {‘Current Month’, ‘Last Month’,’’}, MONO, CONSTRAINED,persistent,{’’},User:0)
, ‘’, '’
, ‘Current Month’, to_char((select sysdate from sys.dual@catsprd), ‘YYYYMM’) || ‘01’
, ‘Last Month’, to_char(add_months((select sysdate from sys.dual@catsprd), -1), ‘YYYYMM’) || ‘01’
)
AND
to_char(INCIDENT_DETAILS.INCIDENT_DATE, ‘yyyymmdd’) < decode(@Prompt(‘Select timeframe for which to run the report:’, ‘A’, {‘ALL’,‘Current Month’, ‘Last Month’, ‘’}, MONO, CONSTRAINED,persistent,{’’})
, ‘’, '’
, ‘Current Month’, to_char(add_months((select sysdate from sys.dual@catsprd), 1), ‘YYYYMM’) || 01
, ‘Last Month’, to_char((select sysdate from sys.dual@catsprd), ‘YYYYMM’) || ‘01’
)
)
end
)

vahmedk (BOB member since 2014-10-24)

#2

Welcome to B:bob:B!

Why the result of the WHEN branch of the CASE statement is

 '''*''=''*'''

That does not make any sense to me. Can you explain?

The result should be a value (string, number, date) not an expression (something equals something).

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#3

This is just I am passing a TRUE condition to the where clause so that it can retrieve all the records like below

select * from table
where —

and ‘’=’’

that way, I can bypass the complete prompt making it as a optional prompt.

vahmedk (BOB member since 2014-10-24)

#4

The escape char is a single quote. If you want single quote in your output then you need to enclose it with single quote and then escape it with single quote for each single quote.

‘’’’’=’’’’’

gives you in the output

‘=’’

vahmedk (BOB member since 2014-10-24)

#5

Hi,

Don’t make it overcomplicated.

Evaluate what @prompt() returns and based on that add the relevant condition. Something like this:

(
   @Prompt('Select timeframe for which to run the report:', 'A', {'Current Month', 'Last Month', '*'}, MONO, CONSTRAINED, persistent,{'*'}, User:0) = 'Current Month'
   AND
   INCIDENT_DETAILS.INCIDENT_DATE BETWEEN TRUNC(sysdate, 'MM')
                                      AND ADD_MONTHS(TRUNC(sysdate, 'MM'), 1) - 1/86400
)
OR
(
   @Prompt('Select timeframe for which to run the report:', 'A', {'Current Month', 'Last Month', '*'}, MONO, CONSTRAINED, persistent,{'*'}, User:0) = 'Last Month'
   AND
   INCIDENT_DETAILS.INCIDENT_DATE BETWEEN ADD_MONTHS(TRUNC(sysdate, 'MM'), -1)
                                      AND TRUNC(sysdate, 'MM') - 1/86400
)
OR
@Prompt('Select timeframe for which to run the report:', 'A', {'Current Month', 'Last Month', '*'}, MONO, CONSTRAINED, persistent,{'*'}, User:0) = '*'

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#6

Thanks Marek, Your code worked. my filter has a long list of values

‘Last 7 Days’
‘Last 30 Days’
‘Last 90 Days’
‘Last 180 Days’
‘Last 3 Months’
‘Current Year’
‘Last 12 Months to Date’
‘Last 24 Months to Date’

If you can give me the code for this that would be great. Cheers

vahmedk (BOB member since 2014-10-24)

#7

This post should help:-

https://bobj-board.org/t/152613

Mak 1 :uk: (BOB member since 2005-01-06)

#8

I provided you with a logic/framework how it could be done so it works.

Please don’t ask for spoonfeeding. Try working out the full syntax yourself. You will learn by doing it.

Marek Chladny :slovakia: (BOB member since 2003-11-27)

#9

Thanks Marek. I really appreciate . Cheers

vahmedk (BOB member since 2014-10-24)