#prompt as optional

[EArumugam]

I used #prompt()# in native sql.

all prompts are optional.

but when i run the report,prompt  which i used in native sql (#prompt('xxx') become Required.

CODE:

   (((#prompt('FC')#=2) AND DATE
                      BETWEEN TO_DATE(#prompt('from')#,'YYYY-MM-DD HH24:MI:SS')
                      AND TO_DATE(#prompt('to')#,'YYYY-MM-DD HH24:MI:SS'))
                      OR ((#prompt('FC')#=1) AND GT.FISCAL_YEAR IN (#prompt('fiscal')#)))

how to make the #prompt()#  as optional in native sql.

its urgent.

Thanks in advance.

[CognosPaul]

In order to make a prompt macro optional, it must have a default value.

In your example you can try changing #prompt('FC')# to #prompt('FC','integer','1')# to have FC default to 1. You'll need to set defaults for all of the prompts to avoid them becoming required. Instead of #prompt('fiscal')# you can do something like #prompt('fiscal','integer','year(getdate())')#.

Also, it looks like you're trying to filter for more than one year in the fiscal year prompt, this will only work if you change the prompt to promptmany.

[EArumugam]

Thanks Paul.

If i #promptmany function in macro i am getting the below error

QE-DEF-406: unknown function 'promptmany' in macroexpression.

how to avoid this error.

[EArumugam]

hi paul,

I am using the same code for #prompt('fiscal','integer','year(getdate())')#.
but i am getting the below error..

RQP-DEF-0177
      An error occurred while performing operation 'sqlPrepareWithOptions' status='-9'.


UDA-SQL-0107 A general exception has occurred during the operation "prepare". ORA-00904: "YEAR": invalid identifier

[CognosPaul]

What database are you using? year(getdate()) works in SQL Server. For Oracle you might try either trunc(sysdate, 'YEAR') or to_char(sysdate,'YYYY'). For DB2 you can try YEAR (current timestamp).

I don't know what to make if the problem with the promptmany. The following works perfectly for me:

#promptmany('Year','integer','','','[Package].[TimeDim].[Year]')#

Can you post the entire macro?