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.
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.
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.
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
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?